Aol (1 Viewer)

Dear All,
I get run time error 3450 "Syntax Error in Query, Incomplete Query clause" when running the code below at the highlighted line.

Actually, I am trying to reference table names on a table on the local Db, then open those tables on a remote Db. I guess I get it wrong some where. I need help please .

Dim DBsr As Database
Dim rstReg As Recordset
Dim rstLTL As Recordset
Dim boMails As Boolean
Dim strTable As String
Dim RegFile As String
Dim strReg As String

'Firstly get the list of tables to be updated
Set Dbs = CurrentDb()

Set rstLTL = Dbs.OpenRecordset("SELECT *FROM tblUpdate ") 'get list of tables to be updated
While (Not (rstLTL.EOF))
'get registrars list
Set rstReg = Dbs.OpenRecordset("SELECT *FROM tblRegStatus ")
While (Not (rstReg.EOF))
strReg = rstReg("Reg")
RegFile = "C:\Apps\Retreat\Program Manager V2.0\Users\Public\Documents\PRA\Regclient" & strReg & "\Data.accdb"

Set DBsr = OpenDatabase(RegFile)

With rstLTL

strTable = rstLTL("Table_Name")
DBsr.Execute "DELETE * FROM '" & strTable & "' "


End With
rstReg.MoveNext
Wend

rstReg.Close
DBsr.Close
rstLTL.MoveNext

Wend
rstLTL.Close

Did you consider just linking to the tables in your second database?

If you are new to database, I recommend you don't DELETE anything without a proper backup.

Can you describe the business rationale for what you are trying to do? There may be options.

Good luck.

1. You seem to have single quotes around the table name in the delete query. Rather than executing a string, save the string to a variable and then execute the variable. This allows you to print out the actual SQL string which helps immensely in finding syntax errors.
2. ALWAYS disambiguate references to DAO and ADO objects. For a short time, MS switched gears and made ADO the default library rather than DAO and if you copied code from someone else, you might end up using ADO code when most of the code is DAO and vice versa. For DAO, always use DAO.Database, DAO.Recordset, DAO.TableDef, DAO.Querydef, etc. For ADO, I think you need ADODB.Database, etc. The disambiguation keeps you safe in the event that you or someone else adds objects from the other library.

Well, your first problem is you titled the thread "AOL"... that will keep a LOT of people from ever looking at it because of the bad reputation America On Line has....



I will second Pat's recommendation. Until you are SURE what you are trying to do in SQL we can't help fix it. I would also double check what value is going into RegFile to make sure your opening the database before trying to access any files.