baitmaster
Well-known Member
- Joined
- Mar 12, 2009
- Messages
- 2,042
Good evening all
I have an ADODB connection to a worksheet within the same spreadsheet - its a dataset that I wish to query using SQL in order to run reports. I'm doing fine except some gaps in my knowledge of syntax for this type of query
Subs to open and close the connection:
this code works fine:
this code doesn't:
If I try Site = '20' then no records are being found although I know there are some. If I try Site = 20 then I get a data mismatch. I've tried formatting my data as both general (i.e. number, no decimals) and as text with no difference in outcome. Can someone please confirm the syntax I need to know for this to work
thanks
I have an ADODB connection to a worksheet within the same spreadsheet - its a dataset that I wish to query using SQL in order to run reports. I'm doing fine except some gaps in my knowledge of syntax for this type of query
Subs to open and close the connection:
Code:
Sub connectionOpen()
Set adConn = New ADODB.Connection
With adConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;"""
.Open
End With
End Sub
Sub connectionClose()
rst.Close
Set rst = Nothing
adConn.Close
Set adConn = Nothing
End Sub
this code works fine:
Code:
Sub GetDeliveryData()
connectionOpen
Dim strQuery As String: strQuery = "SELECT * FROM [order data$];"
Set rst = New ADODB.Recordset
rst.Open strQuery, adConn, adOpenStatic, adLockReadOnly, adCmdText
connectionClose
End Sub
this code doesn't:
Code:
Sub GetDeliveryData()
connectionOpen
Dim strQuery As String: strQuery = "SELECT * FROM [order data$] WHERE Site =20;"
Set rst = New ADODB.Recordset
rst.Open strQuery, adConn, adOpenStatic, adLockReadOnly, adCmdText
connectionClose
End Sub
If I try Site = '20' then no records are being found although I know there are some. If I try Site = 20 then I get a data mismatch. I've tried formatting my data as both general (i.e. number, no decimals) and as text with no difference in outcome. Can someone please confirm the syntax I need to know for this to work
thanks