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]

You need to loop through the fields:

        For i = 1 To .Fields.Count
            ActiveSheet.Cells(1, i) = rst.Fields(i - 1).Name
        Next i

 

[IndyTF]

Back again on this same subject. And in fact I am getting the same error again - although for different reasons. Oddly enough, I have been using this code for some time in multiple settings and haven't had any problems.

Here is the issue at present: I am getting System Error &H80040E37 (-2147217865) when I try to run this SQL generator twice in the same workbook without closing the workbook. I've closed all connections so this isn't making much sense to me. Below is the code that I have been running. Here is an example of me calling the code (Executive Data is a named Excel Range):

Call GetData("SELECT [Vendor Name],[Invoice Number],[Line Item Number],[Invoice Date],[Line Description],[Amount] FROM ExecutiveData WHERE [Invoice Selected] = 'x' ORDER BY [Vendor Name], [Invoice Number], [Distribution Line Number];", outCell)

Main SQL Code:

Sub GetData(SQL As String, outRange As Range, Optional Source As String, Optional RecCountOutRange As Range)

'Return a recordset from a workbook using an SQL Statement
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim strConn As String
Dim varData As Variant

Dim row As Integer
Dim col As Integer

Dim outCell As Range

'Define the print-out location
Set outCell = outRange.Range("a1")

'Open the connection and feed details
Set cn = New ADODB.Connection

With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
         "Extended Properties=""Excel 8.0;HDR=Yes;"""
    .Open
End With

'Open new recordset within the connection
Set rs = New ADODB.Recordset

'Populate RS with data using SQL parameter
rs.Open SQL, cn, adOpenStatic, adLockReadOnly, adCmdText

'Print Recordseet to Workbook
outCell.CopyFromRecordset rs

'Allow for a print-out of a Record Count
If RecCountOutRange Is Nothing Then
    Else
    RecCountOutRange.Value = rs.RecordCount
End If

'Source parameter allows you to change this code based upon where the request is coming from.
'Use or don't use - it's just there.

'Close all recordsets and connections
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

The weird thing is that the Code works perfectly the first time you run it in the workbook. Sorts, Order By, Where statements - all working great. Odder still: This used to work fine no matter how many times you ran it. What changed? I am not sure. IE8 got installed accidentally and that messed up a bunch of things, but I thought I cleared it all out and got everything working again.

Any ideas? I've attached a image of my Reference Libraries just in case. I am running Excel 2002 at the moment, but it has worked before on 2003,2007 as well.

Thanks in advance! Sorry for the long post. Just wanted to get as much info out as possible to save on the back-and-forth posting.

 

[RoryA]

There is in fact a memory leak if you use ADO on the current workbook, which might be the issue. If you run the code against a closed workbook, does it work?

 

[IndyTF]

Rorya,

That seems to work just fine. And it is incredibly fast. (It seems faster than querying the current workbook even)

I swear I wasn't having these memory leaks a while ago though. I know I was able to run the sub several times - even making changes to the "queried table" (just a named excel range in my case) it would use the right data and work fine.

Any ideas? Is there a way to prevent the memory leak? When IE8 installed I was having problems with read-only files being opened (but not visible) and would appear once the sub was done. But I reinstalled some libraries and uninstalled IE8 and everything worked fine.

I truly appreciate your help though. Not that many people know much about this topic.

 

[RoryA]

There is no fix that i know of. It is a bug and MS is aware.

 

[combatvolcano]

Here's my latest method for doing this in Excel 2007.

Requires that you create an ODBC data connection first with an SQL command type (or change it to SQL).

You then use the following VBA to change the connection string and SQL. Easily bound to a button or workbookchange event.

Using VBA to do it means the workbook doesn't have to be in any particular directory.

Dim WorkbookPath As String
WorkbookPath = ThisWorkbook.FullName
Dim WorkbookDir As String
WorkbookDir = ThisWorkbook.Path
With ActiveWorkbook.Connections("connection_name").ODBCConnection
    .Connection = Array( _
Array("ODBC;DBQ=" & WorkbookPath & ";DefaultDir=" & WorkbookDir & ";"), _
Array("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize="), _
Array("2048;MaxScanRows=8;PageTimeout=5;ReadOnly=0;SafeTransactions=0;Threads=3;UserCommitSync=Yess;"))
    .CommandText = "" & _
        "SELECT DISTINCT `Table_Column_Name`, `Table_Column_Name_Etc`, " & _
        "FROM `WORKSHEET_NAME` " & _
        "WHERE (`Table_Column_Name`=?) AND (`Table_Column_Name_Etc` Is Null)"
    .Refresh
End With