"augustus" wrote ...

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
strConn = strConn & "Data Source=" & Application.ActiveWorkbook.FullName & ";"
strConn = strConn & "Extended Properties='Excel 8.0;HDR=NO'"

strSQL = "SELECT 'Apple' FROM [SHEET1$A1:A4] WHERE [SHEET1$B1:B4] = 'TOM &
Co'"

I get :
No value given for one or more required parameters

I use A1:B4 (much larger in the real sheet) as a temp dump area, therefore,
I really don't want to use hearderrow, since other record also go here.

When HDR=NO, Jet assigns column names as F1, F2, F3 etc. I think the
SQL you require is

SELECT F1 AS [Apple]
FROM [SHEET1$A1:B4]
WHERE F2 = 'TOM & Co'

I read somewhere that I must use HeaderRow (HDR=YES) inorder to use other
SQL function, or else, I only get to use select.

This is incorrect. With HDR=NO, the following SQL DML works for me:

UPDATE [Sheet1$] SET F3 = 'Test' WHERE F4 < 3;

INSERT INTO [Sheet1$] (F1, F2, F3, F4)
VALUES (55, 'FiveFive', 'Test5', 5);

SELECT F1 AS MyTestCol INTO MyTestTable FROM [Sheet1$];

INSERT INTO MyTestTable (F1) SELECT F4 AS F1 FROM [Sheet1$];

SQL DDL also works:

CREATE TABLE MyTestTable2 (MyTestCol2 FLOAT NULL);

Jamie.

--