SQL Query using linked Excel table

[stevendt]

Hi,

following on from my question about trying to update a table from Excel . .
(Tried to add the link, but I have <10 posts, but it is (delete the spaces)

access-programmers.co.uk/forums/showthread.php?t=290594

I have created a linked table to point to the Excel file, now I am trying to write a query to update the target table.

Leaving out most of the irrelevant code . . .
I have Dim'ed :-
MyTable as Recordset (a linked "normal" table in another database)
MyTableLinked as Recordset (the linked table for the Excel file)

I have found that I need to open the linked table as Dynaset :-
Set MyTable = MyDB.OpenRecordset("ARM", dbOpenTable)
Set MyTableLinked = MyDB1.OpenRecordset("ARM_Link_Table", dbOpenDynaset)

My SQL Command looks like this . . .

SQLCommand = "INSERT INTO MyTable (Name, Module_Name, Attribute)" & _
" SELECT Name, Module_Name, Attribute FROM MyTableLinked "
'MsgBox (SQLCommand)
MyDB.Execute SQLCommand

Access tells me that it can't find the MyTableLinked table

Can someone tell me where I am going wrong please?

regards
Dave

 

[Ranman256]

no , you dont open the table in code, the table is attached to the db,
so just execute your append query.