Error in Runsql when space in tablename

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"
 
Add square brackets around the table name:
Sql = "Delete from [" & RecordsetNaam & "] IN '" & BackendNaam & "'"
 
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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ReMeE said:
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"
Click to expand...
Click to expand...
 
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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ReMeE said:
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"
Click to expand...
Click to expand...
 
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ReMeE said:
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

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

ReMeE said:
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"
Click to expand...
Click to expand...
Click to expand...
 
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?

Allen Browne said:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ReMeE said:
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

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

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"
Click to expand...
Click to expand...
Click to expand...
 
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



Allen Browne said:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ReMeE said:
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

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

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"
Click to expand...
Click to expand...
Click to expand...
 
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


Allen Browne said:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ReMeE said:
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

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

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"
Click to expand...
Click to expand...
Click to expand...
 
Back
Top