Error in Runsql when space in tablename

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ReMeE - 16 Nov 2006 11:58 GMT

I get an error when running the following :

Sql = "Delete * from " & RecordsetNaam & " IN '" & BackendNaam & "'"
DoCmd.RunSQL Sql

where RecordsetNaam is a table name with a space 'gemaakte offertes' wich I
think causes the error. How do I fix this? I tried several things but this
doesn't seem to work.

I get a error message when the Runsql starts like 'Error 3078: jet database
engine can't find the table or query Gemaakte....' So it doesn't see the
complete name but just the part before the space.

I tried:
RecordSetNaam = "[gemaakte offertes]"
RecordSetNaam = "gemaakte_offertes"

Reply to this Message

Allen Browne - 16 Nov 2006 12:16 GMT

Add square brackets around the table name:
Sql = "Delete from [" & RecordsetNaam & "] IN '" & BackendNaam & "'"

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I get an error when running the following :
>
[quoted text clipped - 14 lines]
> RecordSetNaam = "[gemaakte offertes]"
> RecordSetNaam = "gemaakte_offertes"

Reply to this Message

ReMeE - 16 Nov 2006 12:52 GMT

I did exactly what you wrote but I keep getting the error message when the
Runsql starts like 'Error 3078: jet database engine can't find the table or
query gemaakte....' So it still doesn't see the complete name but just the
part before the space.

> Add square brackets around the table name:
> Sql = "Delete from [" & RecordsetNaam & "] IN '" & BackendNaam & "'"
[quoted text clipped - 17 lines]
> > RecordSetNaam = "[gemaakte offertes]"
> > RecordSetNaam = "gemaakte_offertes"

Reply to this Message

ReMeE - 16 Nov 2006 12:59 GMT

I did what you wrote (i added a * although) bud keep getting the message:

Sql = "Delete * from [" & RecordsetNaam & "] IN '" & BackendNaam & "'"

'Error 3078: jet database engine can't find the table or query Gemaakte....'
So it still doesn't see the complete name but just the part before the space.

What do I do wrong

Reply to this Message

Allen Browne - 16 Nov 2006 15:03 GMT

After ths line where you assign the string, add this line:
Debug.Print Sql

When it fails, press Ctrl+G to open the Immediate window.
Look at the string, and see if you can see what's wrong.

You should see something like this:
Delete from [gemaakte offertes] in 'C:\MyPath\MyFile.mdb';

Note that the path and mdb extension are both important.
Access does add the *, though it's technically not correct.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I did what you wrote (i added a * although) bud keep getting the message:
>
[quoted text clipped - 31 lines]
>> > RecordSetNaam = "[gemaakte offertes]"
>> > RecordSetNaam = "gemaakte_offertes"

Reply to this Message

ReMeE - 16 Nov 2006 19:37 GMT

this is what it says:

Delete * from [Gekalkuleerde Werken] IN 'E:\Documenten van
Rene\ReCaLc\Backup\16-11-2006\OffertesBackend.mdb'

what is wrong here?

> After ths line where you assign the string, add this line:
> Debug.Print Sql
[quoted text clipped - 43 lines]
> >> > RecordSetNaam = "[gemaakte offertes]"
> >> > RecordSetNaam = "gemaakte_offertes"

Reply to this Message

ReMeE - 16 Nov 2006 19:55 GMT

I finaly found the error in the next line which was also in the routine:

Sql = "INSERT INTO [" & RecordsetNaam & "] IN '" & BackendNaam & "' " &
"SELECT * FROM [" & RecordsetNaam & "]"

first it whas like this:

Sql = "INSERT INTO [" & RecordsetNaam & "] IN '" & BackendNaam & "' " &
"SELECT * FROM " & RecordsetNaam

There where no brackets added to the second 'RecordsetNaam'.

Thanks for helping me out allen

> After ths line where you assign the string, add this line:
> Debug.Print Sql
[quoted text clipped - 43 lines]
> >> > RecordSetNaam = "[gemaakte offertes]"
> >> > RecordSetNaam = "gemaakte_offertes"

Reply to this Message

ReMeE - 16 Nov 2006 20:34 GMT

As I managed to make it work here is my whole routine for other people. Is
ther an other way of Runsql on a password protected database as the way I did?

Sub MaakDeBackup(BackendNaam, RecordsetNaam, PasWoord)
Dim Db As Database, Rs As Recordset, Sql as string

If IsNull(PasWoord) Or PasWoord = "" Then
Set Db = DBEngine.Workspaces(0).OpenDatabase(BackendNaam, False,
False)
Else
Set Db = DBEngine.Workspaces(0).OpenDatabase(BackendNaam, False,
False, ";PWD=" & PasWoord)
End If

Set Rs = Db.OpenRecordset(RecordsetNaam)

'Maak eerst het backupbestand leeg indien deze dat nog niet is
Sql = "Delete from [" & RecordsetNaam & "] IN '" & BackendNaam & "'"
DoCmd.RunSQL Sql

'Vul het backup bestand met de nieuwe data
Sql = "INSERT INTO [" & RecordsetNaam & "] IN '" & BackendNaam & "' " &
"SELECT * FROM [" & RecordsetNaam & "]"
DoCmd.RunSQL Sql

Rs.Close
Db.Close

End Sub

> After ths line where you assign the string, add this line:
> Debug.Print Sql
[quoted text clipped - 43 lines]
> >> > RecordSetNaam = "[gemaakte offertes]"
> >> > RecordSetNaam = "gemaakte_offertes"

Reply to this Message