Problem with VBA/ADO Insert Into

[JohnM3]

Goal: Insert WS data into MS Access Table
using ADO and SQL

Error:Invalid Argument

SQL Used:
INSERT INTO MyNewTable (C1,C2,C3) SELECT * FROM
[Excel 12.0;HDR=YES;C:\Development\Excel_Sandy_NaturalFactors\A_Programs\NatFactors.xlsm].[BY_X$]

Connection:
Valid Connection

Table Structure:
C1 - Text
C2 - Text
C3 - Text

Table Name:
MyNewTable

WS NAME:
BY_X

Code:
The code is below but now useful
in that it calls related properties.

Please Login or Register  to view this content.

[MarvinP]

Hi John,

Why not just import Excel data into Access?

http://conferences.aicpa.org/tech08/...%20Fleenor.pdf

[JohnM3]

Hi MarvinP

That is a great article and I appreciate it and have saved it.

However, I am very interested in nailing down the correct syntax to simply use
ADO/SQL to load a worksheet to an Access table and hope someone here might
already be using it and might run across my question. I am googled-out and out
of ideas but suspect my syntax is close to the correct syntax.

regards
John

[JohnM3]

Goal: Insert an Excel WS to an Access table (in one shot) using ADO and SQL.

Good SQL: The following SQL works (Excel 2007 -> Access 2007)

INSERT INTO MyNewTable SELECT * FROM [BY_X$]
IN 'C:\Development\Excel_Sandy_NaturalFactors\A_Programs\NatFactors.xlsm' 'Excel 12.0 Macro;HDR=YES;'

Note:
There has to be as space between .... xlsm' 'Excel (2nd line of SQL)

This code uses internal properties that are not defined below.

Please Login or Register  to view this content.

[MarvinP]

Great Job!!

Now make the Forum Master happy and edit your first post and mark the Prefix to the Title as Solved!!

I'm so glad you got this answer as I didn't feel I was any help.

AND - Your code above might help others with the same question!!

[romperstomper]

FYI, you were very close with what you had originally:

INSERT INTO MyNewTable (C1,C2,C3) SELECT * FROM
[Excel 12.0;HDR=YES;Database=C:\Development\Excel_Sandy_NaturalFactors\A_Programs\NatFactors.xlsm].[BY_X$]

[JohnM3]

Hi RS

_THANKS_ for posting that. I like your solution better.

INSERT INTO MyNewTable (C1,C2,C3) SELECT * FROM
[Excel 12.0;HDR=YES;Database=C:\Development\Excel_Sandy_NaturalFactors\A_Programs\NatFactors.xlsm].[BY_X$]


regards
John

[sridevi kumaravel]

Hi I am trying to import data to Mssql 2008, can anyone help me

For the below query, equivalent query in SQL

INSERT INTO MyNewTable SELECT * FROM [BY_X$]
IN 'C:\Development\Excel_Sandy_NaturalFactors\A_Programs\NatFactors.xlsm' 'Excel 12.0 Macro;HDR=YES;'

When i try the same query in sql it says 'incorrect syntax near IN'

Even i tried the option suggested by RS,
Like this
INSERT INTO MyNewTable (C1,C2,C3) SELECT * FROM
[Excel 12.0;HDR=YES;Database=C:\Development\Excel_Sandy_NaturalFactors\A_Programs\NatFactors.xlsm].[BY_X$]

I am getting invalid object name.please help me.

Regards,
Sridevi

[JosephP]

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.