Need Help Getting Access 2003 connection to Excel Named Range

[Perico]

I'm trying to import into Access 2003, using an Access 2003 form and import
button, data from an Excel named range. Sheet is named "ResDataSht" and the
range is named "ResData". The Access table is tmpNewInvoice.

Here is my syntax with each command all on the same line:

Dim cn As New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\PGE\DTS-ES\ADO_TEST.xls;Extended Properties=""Excel 8.0;"""

cn.Execute "INSERT INTO tmpNewInvoice (ELEC_SA_ID_NUMBER, ELEC_METER_NUMBER,
SITE, GAS_SA_ID_NUMBER, GAS_METER_NUMBER) SELECT ELEC_SA_ID_NUMBER,
ELEC_METER_NUMBER, SITE, GAS_SA_ID_NUMBER, GAS_METER_NUMBER FROM [EXCEL
8.0;IMEX=1;HDR=Yes;DATABASE=D:\PGE\DTS-ES\ADO_TEST.XLS].[ResDataSht$ResData]"

This cn.Execute code yields an error msg, "cannot find
[ResDataSht$ResData]". When I change the code to
"...ADO_TEST.XLS].[ResData]", thereby just using the named range appended to
the Excel reference, I get an error "cannot find tmpNewInvoice", which is my
Access table.

What am I doing wrong here? Thanks in advance.

 

[Guest]

You have created a connection to XLS.

With a connection to XLS, you don't need to specify XLS
database again in query. Instead, you need to specify MDB
database in query.

If you create a connection to MDB, then you must specify
XLS database in query, but would not need to specify MDB
database in query.

If you connect to dbC:
Insert into [dbA].[tblA] (c,d) from [dbB].[tblB]

If you connect to dbA
Insert into [tblA] (c,d) from [dbB].[tblB]

If you connect to dbB
Insert into [dbA].[tblA] (c,d) from [tblB]

(david)