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