Hi,

This is my code:

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'"
With Conn
.CursorLocation = adUseClient 'default assumed not client
.ConnectionString = strConn
.Open
End With

Rs.Open strSQL, Conn 'Stopped here
.........

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

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

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.

Thanks

Hi augustus

'Either paste the follwing or
'make changes as noted by add
'Add some numbers or strings in [sheet?]a1 -- a20 and test
'the procedeure
'msgbox should report 12 items

'then you can test your SQL

Private Sub CommandButton1_Click()

Dim strConn As String 'add

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

Dim strSQL As String 'add
strSQL = "Select * from [Sheet2$B9:B20]" 'add for test

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

Dim conn As ADODB.Connection 'add
Set conn = New Connection 'add

With conn
.CursorLocation = adUseClient 'default assumed not client
.ConnectionString = strConn
.Open
End With

Dim Rs As ADODB.Recordset 'add
Set Rs = New Recordset 'add

Rs.Open strSQL, conn 'Stopped here "WORKS AS TESTED"

MsgBox "Total Records = " & Rs.RecordCount, , "Good Luck TK" 'add

End Sub

Good Luck
TK


"augustus" wrote:

Hi,

This is my code:

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'"
With Conn
.CursorLocation = adUseClient 'default assumed not client
.ConnectionString = strConn
.Open
End With

Rs.Open strSQL, Conn 'Stopped here
........

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

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

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.

Thanks

An SQL like

Select 'Apple' from .....

would return the string Apple as a variable autonamed by the SQL engine: if
you want to use your own name, try

Select 'Apple' as MyName from ...

If you specify HDR=No, the Excel SQL parser IGNORES the first row of your
data: this is a known bug.

I do not believe that you can use a WHERE constraint based on a cell value.
Only the names (or implicit SQL names) found on the first row of your range
may be used in the WHERE statement.


"augustus" wrote:

Hi,

This is my code:

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'"
With Conn
.CursorLocation = adUseClient 'default assumed not client
.ConnectionString = strConn
.Open
End With

Rs.Open strSQL, Conn 'Stopped here
........

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

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

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.

Thanks

You might want to experiment with is code:

Sub AA()
Dim Cnn As String, Sql As String
Cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Application.ActiveWorkbook.FullName & _
";Extended Properties='Excel 8.0;HDR=NO'"
Sql = "SELECT 'Apple' FROM [SHEET1$A1:B6] WHERE F2= 'Tom & Co'"
Set ADORS = CreateObject("ADODB.Recordset")
ADORS.Open Sql, Cnn
' In the Immediate window, type ADORS.GetString to see what has been
extracted.
' Note: The FROM clause specifies 2 columns
' HDR is set to NO, so field names are autonamed F1, F2 etc
' This picks up an arbitrary string Apple
End Sub


"augustus" wrote:

Hi,

This is my code:

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'"
With Conn
.CursorLocation = adUseClient 'default assumed not client
.ConnectionString = strConn
.Open
End With

Rs.Open strSQL, Conn 'Stopped here
........

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

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

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.

Thanks

"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.

--

Thanks for all the help.

I practically tried all of the suggestion, but none of them seem to work, so
I went to MSDN and look for "Select" & "Where", and combined with you guys
suggestion, I got the following:

SELECT F1 FROM [SHEET1$A1:B5] WHERE F1 = 'Level 1' AND F2 = 'Ramp';

Tried the above, and it work.

"Jamie Collins" wrote:

"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.

--

"AA2e72E" wrote ...

If you specify HDR=No, the Excel SQL parser IGNORES the first row of your
data: this is a known bug.

This bug is not known to me. Excel doesn't even have a SQL parser.
Both you and the OP are using the Jet 4.0 OLEDB provider with an Excel
8.0 datasource so a test is straightforward. Try the following
connection string:

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Application.ActiveWorkbook.Path & "\New.xls;" & _
"Extended Properties='Excel 8.0;HDR=NO'"

(the code posted uses the active workbook, however because of the
memory leak bug it is not a good idea to query an open workbook, even
in a test environment).

Then create a new table with data by executing each of the following:

CREATE TABLE MyTestTable (MyTestCol VARCHAR(255) NULL);
INSERT INTO MyTestTable (F1) VALUES ('One');
INSERT INTO MyTestTable (F1) VALUES ('Two');

This will create a header row and two data rows.

In context, your statement 'ignores the first row of data' is
ambiguous because HDR=NO means the header row is seen as a data row.
However, the following:

SELECT COUNT(*) FROM MyTestTable;

returns 3, meaning no rows are ignored and proving your statement to
be false.

Jamie.

--

"Jamie Collins" wrote:

This bug is not known to me. Excel doesn't even have a SQL parser.
Both you and the OP are using the Jet 4.0 OLEDB provider with an Excel
8.0 datasource so a test is straightforward. Try the following
connection string:



Probally refering to the ODBC driver bug

http://support.microsoft.com/kb/288343/EN-US/


TK