syntax for SQL query from same workbook

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:
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
The below code works find for me, where I have 2 fields on the "order data" tab, Name and Site (both are text).
I have the code dumping the results into a tab named DataDest.

Code:
Option Explicit

Dim adConn As ADODB.Connection
Dim rst As ADODB.Recordset

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

Sub GetDeliveryData2()
Dim strQuery As String

connectionOpen

strQuery = "SELECT * FROM [order data$] WHERE Site = '20';"
Set rst = New ADODB.Recordset
rst.Open strQuery, adConn, adOpenStatic, adLockReadOnly, adCmdText

MsgBox rst.RecordCount
Worksheets("DataDest").Range("a1").CopyFromRecordset rst

connectionClose

End Sub


Sub GetDeliveryData()
Dim strQuery As String

connectionOpen

strQuery = "SELECT * FROM [order data$];"
Set rst = New ADODB.Recordset
rst.Open strQuery, adConn, adOpenStatic, adLockReadOnly, adCmdText

MsgBox rst.RecordCount
Worksheets("DataDest").Range("a1").CopyFromRecordset rst

connectionClose

End Sub


Is that something you can work with?
 
Upvote 0
Thanks Ron

I tried that approach but was getting data mismatch errors. I had the exact code you've shown, but it failed because only the data was formatted as text, whereas it seems to require the entire column as text. I've gotten it to work now using text only

I can possibly work with this, but my formulas etc. are all configured to use numbers not text, so I'd like to know if I can use these. I also need to tackle numbers formatted as dates next too, which definitely won't work so well as text. I can do it easily from Access but obviously the syntax has changed when querying from Excel
 
Upvote 0
Scrap that. My old code is now working even though I'm adamant it wasn't before. Perhaps I had the header row formatted differently or something. Anyway I believe the problem is fixed. Thanks for the help
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top