using ADO on an excel spreadsheet

[Irishmaninusa]

Hello,

I am using ADO to query the contents of an excel spreadsheet, and I have
worksheet called WS_1 and there is a number of columns in the spreadsheet. I
want to be able to sort on the product column and I am getting an error
message saying

Too few parameters, Expected 1.

This is how I am connecting to the spreadsheet

'Set the connection string
strConn = "DRIVER=Microsoft Excel Driver
(*.xls);DriverId=790;ReadOnly=True;" & "DBQ=" & strFile
'Open the recordset
.Open strSql, strConn

And this is the select statement I am passing into the spreadsheet
SELECT * FROM [WS_1$] ORDER BY Product

If I use SELECT * FROM [WS_1$] then it doesn't give any errors, but as soon
as I add the order by clause then it gives me errors.

I am using Excel 2000 SP3.

Any help would be appreciated. Thanks.

JD

 

[keepITcool]

I believe you must indicate that the driver should use headings,
else the column/field names are assigned as [F1]...[F256]

note:
You are using ADO with the old ODBC drivers.
I suggest you use the Jet engine.

For the correct connections strings see:
http://www.able-consulting.com/ADO_Conn.htm

Note: be aware of ADO bug: should not be used to query OPEN workbooks.
http://support.microsoft.com/default.aspx?scid=kb;en-us;319998

hth


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


"Irishmaninusa"

 

[Irishmaninusa]

I have tried it both ways and still no luck.

I have tried it with HDR turned off or on.

I have tried using JET engine and still no luck.

Sorry if I sounded frustrated, but I know at one point I did this a few
months ago and now that I need it to work it doesn't, so if you have an
sample code I would really appreciate it. Thanks.

I believe you must indicate that the driver should use headings,
else the column/field names are assigned as [F1]...[F256]

note:
You are using ADO with the old ODBC drivers.
I suggest you use the Jet engine.

For the correct connections strings see:
http://www.able-consulting.com/ADO_Conn.htm

Note: be aware of ADO bug: should not be used to query OPEN workbooks.
http://support.microsoft.com/default.aspx?scid=kb;en-us;319998

hth


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


"Irishmaninusa"

Hello,

I am using ADO to query the contents of an excel spreadsheet, and I
have worksheet called WS_1 and there is a number of columns in the
spreadsheet. I want to be able to sort on the product column and I am
getting an error message saying

Too few parameters, Expected 1.

This is how I am connecting to the spreadsheet

'Set the connection string
strConn = "DRIVER=Microsoft Excel Driver
(*.xls);DriverId=790;ReadOnly=True;" & "DBQ=" & strFile
'Open the recordset
.Open strSql, strConn

And this is the select statement I am passing into the spreadsheet
SELECT * FROM [WS_1$] ORDER BY Product

If I use SELECT * FROM [WS_1$] then it doesn't give any errors, but as
soon as I add the order by clause then it gives me errors.

I am using Excel 2000 SP3.

Any help would be appreciated. Thanks.

JD

 

[keepITcool]

"Irishmaninusa"

And this is the select statement I am passing into the spreadsheet
SELECT * FROM [WS_1$] ORDER BY Product

If I use SELECT * FROM [WS_1$] then it doesn't give any errors, but
as soon as I add the order by clause then it gives me errors.

What happens if you try:
"SELECT Product FROM [ws_1$]"

are you sure the field name "Product" exists?


else try: (with dim rst as adodb.recordset)

with cnn.execute("Select * from [ws_1$]")
for i=0 to .fields.count-1
debug.print .fields(i)
next
end with

else..
zip the workbook with the code and the workbook with the data.
mail to address below.. and I'll have a look.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Jamie Collins]

I believe you must indicate that the driver should use headings,
else the column/field names are assigned as [F1]...[F256]

The implicit default value is

HDR=Yes

so column headings are assumed, as long as they are valid. 'Product'
isn't an a reserved word (ODBC nor Jet) and is valid.

I suggest you use the Jet engine.
For the correct connections strings see:
http://www.able-consulting.com/ADO_Conn.htm

You imply that using the ODBC provider is incorrect. I know of at
least one bug with the Jet provider which means for one of my apps I
legitimately choose to use the ODBC driver e.g.

http://support.microsoft.com/default.aspx?scid=kb;en-us;300948

Jamie.

--