syntax for SQL query from same workbook

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,031
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
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,301
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?
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,031
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
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,031
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
 

Forum statistics

Threads
1,081,680
Messages
5,360,512
Members
400,589
Latest member
Mikealphatangoc

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top