Delete SQL table contents via VBA 1

[jmcg]

I have a local access database that I send up to an SQL db using:

db.Execute "INSERT INTO [ODBC;DRIVER=SQL Server;SERVER=MyServer;DATABASE=MYDatabase].MyTable( Field1, Field2 ) SELECT lclFld1, lclFld2 FROM lclTable

and it works fine, however before uploading the new data I need to clear down the old data.
I have tried:

db.Execute "DELETE FROM [ODBC;DRIVER=SQL Server;SERVER=MyServer;DATABASE=MYDatabase].MyTable

and get an error 3622:
You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.
I tried removing the IDENTITY col and got an error saying could not delete from specified tables.
Any help, much appreciated.

 

[RobertT687]

The syntax should be something like:

CurrentDb.Execute("YourDeleteQueryStatement",dbSeeChanges)

You need to have a primary key defined for the table (probably your Identity column) in order to delete from Access.

 

[jmcg]

Robert
Thanks a lot worked perfectly.
PS: The syntax was without the brackets :-O