|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
|
|
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)
"Perico" <Perico[ at ]discussions.microsoft.com> wrote in message
news:DF162B03-C3E2-470C-A0B7-9D99EEEB739A[ at ]microsoft.com...
[Quoted Text] > 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.
|
|
|