ADODB Connection: Running SQL Queries on Data within the Same Workbook

[IndyTF]

Hello All,

I am relatively new to the boards, although I have been lurking around gleaning insights for some time. I have searched for this topic quit a bit and found a few threads on it. Those healped in designing my current workbook, but I've hit a specific snag that is confusing me.

Let me first explain my purpose:

I am simply trying to have some code that would allow me to run relatively simple SQL Queries on Excel Data. This data however will be in a table format within Excel and I will only be needing to query on table at a time; meaning that I don't need relational database features or SQL Joins at all. At most, the SQL Statements will involve WHERE and SORT statements. So, my method is relatively simple:

1. Select an Excel Range (The table being Queried)
2. Provide an SQL Statement
3. Provide an Output range to print the results

After my recent research, I concluded that ODBC/ADODB was the best route. I am very new to ODBC Connections though. Basically, I just set up an ODBC Connection and DataSource using the Control Panel/Administrative Tools. After that, following some templates of others I constructed the following code:

Sub getData(SQL As String, outRange As Range)

Dim conn As Variant
Dim rs As Variant
Dim cs As String
Dim outCell As Range
Set outCell = outRange.Range("a1")
Dim row As Integer
Dim col As Integer

Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")

cs = "DRIVER=Microsoft Excel Driver (*.xls);UID = admin;UserCommitSync = Yes;Threads = 3;SafeTransactions = 0;ReadOnly = 1;PageTimeout = 5;MaxScanRows = 8;MaxBufferSize = 2048;FIL=excel 8.0;DriverId = 790;DefaultDir=F:\2008\Cell Phone\Call Details;DBQ=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name

conn.Open cs
rs.Open SQL, conn

row = 0
For col = 0 To rs.Fields.Count - 1
outCell.Offset(row, col).Value = rs.Fields(col).Name
Next
row = row + 1

Do Until rs.EOF
For col = 0 To rs.Fields.Count - 1
outCell.Offset(row, col).Value = rs.Fields(col).Value
Next
row = row + 1
rs.MoveNext
Loop
rs.Close

Set rs = Nothing
conn.Close

Set conn = Nothing
End Sub

The Connection String I got from the DataSource when I set up the ODBC Connection through the control panel.

Now, I got this working on my Laptop (running Excel 2007, however the workbook is an Excel 2002 Document). It was working incredibly beautiful. Exactly as I hoped for.

However, when I moved the Workbook over to my Work PC, it freaked out. I am getting this error: System Error &H8000FFFF (-2147418113). Catastrophic Failure. Now of course, I reset the Connection String and made a new ODBC Data Source file for the Work PC. But I must have messed something up somewhere. It should also be noted that the workbook is located on a drive which I access through a network connection, not sure if that matters.

My thoughts are that I either do not have the right Drivers, or my Connection wasn't set up right, or My Connection String in the code is in error.

Any thoughts? Please let me know if I can provide anything to make my question clearer.

 

[RoryA]

What version of Excel does your work PC use?

 

[IndyTF]

Work PC uses Excel 2002.

 

[RoryA]

I use this code in 2002/2003. It uses OLEDB and does not require any ODBC set up, but the provider info needs to be changed if you want to use it with Office2007:

Sub GetData()
   ' Sample demonstrating how to return a recordset from a workbook
   Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String
   Dim varData As Variant
   Set cn = New ADODB.Connection
'   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";" & _
'         "Extended Properties=""Excel 8.0;HDR=Yes;"""
   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
         "Extended Properties=""Excel 8.0;HDR=Yes;"""
      .Open
   End With
   strQuery = "SELECT * FROM [Sheet1$];"
   Set rst = New ADODB.Recordset
   rst.Open strQuery, strConn, adOpenStatic, adLockReadOnly, adCmdText
   varData = rst.GetRows
   rst.Close
   Set rst = Nothing
'   cn.Close
   Set cn = Nothing
End Sub

 

[IndyTF]

Ok. First of all, thank you for your quick responses!

I tried what you gave me and got a more specific error in VB:

Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC Driver Manager] Data souce name not found and no default driver specified.

It references this line in your code:

rst.Open strQuery, strConn, adOpenStatic, adLockReadOnly, adCmdText

Any thoughts? Thanks agin for your time!

 

[RoryA]

My thoughts are that I'm a fat fingered muppet and forgot to alter part of my code! Try this:

Sub GetData()
' Sample demonstrating how to return a recordset from a workbook
Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String
Dim varData As Variant
Set cn = New ADODB.Connection
' strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";" & _
' "Extended Properties=""Excel 8.0;HDR=Yes;"""
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""
.Open
End With
strQuery = "SELECT * FROM [Sheet1$];"
Set rst = New ADODB.Recordset
rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
varData = rst.GetRows
rst.Close
Set rst = Nothing
' cn.Close
Set cn = Nothing
End Sub

 

[IndyTF]

Okay. I haven't tried altering your code at all except for insert the [Sheet] name. And to be safe, I tried simply running the code in VB first. Good news, no error. But how can I see that varData, which I assume is my query result? Can I print this to somewhere?

 

[RoryA]

If you just want to see the data in a sheet, then you can use copyfromrecordset instead. Replace this:

vardata = getrows

with:

Activesheet.Range("A2").copyfromrecordset rst

for example.

 

[IndyTF]

Okay,

I know what the problem I have been having is. I have everything working great now.

EXCEPT: The WHERE statement is what was causing my problem. As soon as I took that out, everything started working. But that is a really important part of what I am trying to do.

Here is the SQL statement I am trying to run:

"SELECT [Number],[Employee Name],[Work Force ID],[Department],[AVG MIN],[AVG COUNT],[SUMofQualifications] FROM DataSummary WHERE [SUMofQualifications] = 01;"

What am I doing wrong with the WHERE statement? This was the same one I was using in Laptop version with the ODBC connection and it was working.

 

[IndyTF]

Nevermind! I got it. I needed to add ' ':

WHERE [SUMofQualifications] = '01';

Working great now.

One other question:

How would I print the Field Names at the top of the Recordset as well?