Send SQL to Access from Excel using ADO

[Guest]

I am using Windows XP with Office 2003; ADO 2.7.

In the past, I have always only need to return a recordset from Access so I
know how to do that. But, now I need to send a SQL command to Access using
ADO.

Can someone please post example ADO to do that? I use an ODBC-type
connection string as follows:

ConnectionString = "DRIVER={MICROSOFT ACCESS DRIVER (*.MDB)};DBQ=" &
argFullName & ";USER ID=;PASSWORD=;"

Thanks much in advance.

 

[Guest]

I assume you are trying to create a QUERY or VIEW in ACCESS; if so, try this:

Cnn="DRIVER={MICROSOFT ACCESS DRIVER
(*.MDB)};DBQ=C:\OURFILES\AJAY\HIER\HIER.MDB;"
Sql="CREATE VIEW AJAY AS SELECT * FROM FILESYSTEM WHERE AMOUNT<1000;"
Set ADOC = CreateObject("'ADODB.Connection")
ADOC.Open Cnn
ADOC.Execute Sql
ADOC.Close
Set ADOC = Nothing

**Change the SQLand DBQ as required**

 

[Guest]

Hi AA,

Thanks for your post. Actually, I am trying to send a make table query. Your
code seems to work, but I get an error with my SQL: "External table not in
expected format"

I think this has something to do with the [EXCEL 5.0;] portion of my string
as folows:

strSQL = "SELECT [SYMBOLS$].* INTO [SYMBOLS_ALL] FROM [SYMBOLS$] IN '" &
strFullName & "'[EXCEL 5.0;];"

Any ideas on how to fix this?

Thanks again.

 

[Jamie Collins]

I think this has something to do with the [EXCEL 5.0;]
portion of my string

as folows:

strSQL = "SELECT [SYMBOLS$].* INTO [SYMBOLS_ALL] FROM [SYMBOLS$] IN '" &
strFullName & "'[EXCEL 5.0;];"

I prefer Jet's 'brackets' syntax to the IN syntax you are
trying to use. With an alias for the Excel worksheet
(table), try something like this:

SELECT XL.*
INTO SYMBOLS_ALL
FROM [Excel 5.0;Database=C:\MyFolder\MyWorkbook.xls;].
[SYMBOLS$] AS XL;

BTW Excel 5.0 is for Excel95 (and some below) format. For
Excel97 and above formats, use Excel 8.0 in your
connection string.

Also, as you are using ADO, ensure your source workbook
(strFullName) is not an open workbook, otherwise you'll
fall foul of the dreaded ADO memory leak bug (or something
very much like it).

Jamie.

--