import data from Excel

[Mike]

I have a database with a linked Excel table. I use an add
query to import new records from the linked Excel table to
an Access table.
I experience the following problem: if the linked Excel
file is deleted and changed to another one (completely the
same but with other data) while the database is open the
add query would not run any more.
If the file is changed while the database is closed -
everything is OK.
What should I do in such a situation? I still need to
change Excel file while the database is open.

 

[John Nurick]

Hi Mike,

Try cutting out the linked table by specifying the Excel filename with
an IN clause in the SQL of your append query. Something like this:

INSERT Field1, Field2 INTO MyTable
SELECT Field1, Field2
FROM [NameOfSheet$]
IN "D:\Folder\Folder\File.xls" "EXCEL 5.0;"
;

The help topic can be hard to find, but if you go to Help Contents and
look for Microsoft Jet SQL Reference, then Data Manipulation Language,
then INSERT ... INTO