Insert Into from a remote database - Need help with syntax

[khwaja]

I have the following code which I wish to use to empty an existing table 'main' first and then update it with the data from another database. I am not too sure if I am using the right syntax for the insert into code as far as a remote database is concerned. Could someone help me please?

Sub CopyEAData()
Dim dbs As Database, tdf As TableDef, strSQL As String
Set dbs = CurrentDb


DoCmd.SetWarnings False

DoCmd.RunSQL "Delete *from [Main];"

dbs.Execute " INSERT INTO Main SELECT * FROM \\nhomadgffs002\Share\EA variation\Data Files\EAVariationDatabaseData.mdb [Main];"

MsgBox "Update of Design Variation Approvals was also successfull."
Set dbs = Nothing


Cheers

AK

Note: Using Access 97 - still.

 

[Edski]

Yeah, both work for me too but it doesn't seem to work for poor old khwaja!