Writing SQL Queries Against Virtual Tables in Excel VBA

[robnjay]

Hello All:

About 3 - 4 years ago, I wrote an XL add-in which customized and added some functionality to a product called @AGlance IT, which retreives plant process data to a spreadsheet. Since then a _large_ number of spreadsheet applications have been created in my facility using my add-in and @Aglance. We are now transitioning to a new SQL-based process control DB. I am now writing a new add-in which will have functions and subroutines with the same names (and expecting the same parameters) as the old add-in, but which "hides" SQL in these functions/subroutines. In this way I can merely replace the add-in and the existing spreadsheet apps will work.

I do not want to use Microsoft Access, or carry around a dummy .mdb file for this. Excel is the only Office application which will be open when the data acquisition is running.

In order to accomplish this, I am looking at using ADO recordsets and SQL in VBA. I've been experimenting with something called "disconnected recordsets", which is explained in a MSDN article:

http://msdn.microsoft.com/library/de...ORecordset.asp

Using this info, I've managed to create a recordset from a spreadsheet range (column of dates):

Set tblDates = CreateObject("ADODB.Recordset")
With tblDates
    .ActiveConnection = Nothing
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
        With .Fields
        .Append "TimeStamp", adDate
        End With
    .Open
        For Each c In rDates.cells
        .AddNew "TimeStamp", c.Value
        Next
   .MoveFirst
End With

I've also populated another recordset from plant data:

Set cn = CreateObject("ADODB.Connection")
cn.Open ("DRIVER={AspenTech SQLplus};HOST=" & IP21_SERVER & ";PORT=10014")
cn.CursorLocation = adUseClient
sSQLQuery = "SELECT " & sIP21TimesTbl & " AS TheTimeStamp, " & sIP21ValuesTbl & " As Value" & _
            " FROM " & Chr(34) & UCase(sTagName) & Chr(34) & " WHERE (" & sIP21TimesTbl & "<='" & StartTime & "' AND " & _
             sIP21TimesTbl & ">='" & Format(EndTime, "dd-mmm-yy hh:mm:ss") & "');"
Set tmptbl = cn.Execute(sSQLQuery, , adCmdText)

At this point, I have 2 recordsets: "tmptbl" - 2 fields, "TheTimeStamp" and "Value", and "tblDates" - 1 field, "TimeStamp". What is important here is that "tmptbl" has timestamps (field "TheTimeStamp") and values (field "Value") retrieved from the process DB, and "tblDates" containst the timestamps (field "TimeStamp") that the user has requested values for, and that these are not guaranteed to be the same... Now I would like to write a SQL join against these recordsets to populate a third recordset (again with 2 fields "TimeStamp" and "Value") such that for any requested timestamp ("tblDates") which is not present in the retrieved data ("tmptbl"), there is a record with the requested timestamp and an error string (like "UNDEF").

I am not able to write SQL to populate any new recordset from the existing ones. I've tried a very simple one as a test:

sSQL = "SELECT TheTimestamp AS TimeStamp, Value FROM tmptbl"
Set tblReconcile = CreateObject("ADODB.Recordset")
With tblReconcile
    .ActiveConnection = Nothing
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
    .Source = sSQL
    'With .Fields
        '  .Append "TimeStamp", adDate
        '  .Append "Value", adDouble
    'End With
    .Open
End With

Note that I've commented out the field appends. I've tried with and without commenting these out. I always get the following error (at the .open statement):

"Run-Time Error '3709':

The Connection cannot be used to perform this operation. It is either closed or invalid in this context."

Can anyone offer some expertise here? I've seen other requests in forums where people want to be able to write SQL in VBA against spreadsheet ranges or variant arrays; this is nearly the same thing I would like to do. I haven't seen an answer to any of these yet.

[NateO]

I don't think you can pass sql like that on a disconected recordset from what I'm reading. Why not connect to the DB your passing a driver to?

Here's an example of querying a spreadsheet via sql, and it is connected:
http://www.mrexcel.com/board2/viewto...87744&start=14

[jamescol]

I am fairly certain you cannot do what you want with a disconnected record set in VBA. Visual Studio .Net 2003 was the first development platform to permit this type of action I believe.

Just to be sure, though, what version of MDAC are you using? And what version of the Object Library are you referencing?

There is a known problem with certain versions I can check on for you.

Thanks,
James

[robnjay]

Was using ADO 2.5, now will try 2.7

Using Microsoft Jet 4.0 (4.0.2521.8)

I'm not sure if I've really answered your question.....

[NateO]

What are you trying to query, a recordset object data type? Don't even bother, loop through it or go back to the source.

It's one thing to manually stack a disconnected recordset, it's quite another to query a recordset as such, you need to connect to the db, and I can't picture how this works.

What you can do is pass your R.S. to a worksheet, connect to the book (db) and query your table (ws). But, it might be more straightforward, and more of a pure play to simply loop.

It might even be easier to quick-stack or stack an array. Then you can use functions against it, e.g., Match().

[robnjay]

Nate:

I am an old-timer with VB, although very rusty now. In fact, I was very flattered to find my name mentioned in Mr. McKinney's book you reference above. I've written some very involved VBA in Excel, several add-ins, but I am really lost with regard to ADO & database queries, etc.

My problem is this: I want to be able to write a query (join) to two tables: One with "TimeStamps" and "Values" from a process database ("DRIVER={AspenTech SQLplus} and one with "RequestedTimeStamps" from either a range of cells in the ActiveWorkbook, or from a Variant Array created in VB. The results of the join (2 fields - "TimeStamp" and "Value") will be put in a column on the same workbook (Value field only).

I don't care what db driver I use for the table of "Requested Dates" or the resulting table ("ReconciledTimeStamps&Values"). I just don't want to have to carry around an mdb file or write to a temporary range in the open workbook.

I thought if I could create recordsets with (1) process values and (2) requested timestamps, I could then write a query against these recordsets to create the third - "reconciled..". I don't know enough about this whole business to understand whether this is even possible.

BTW, I am able to create the (1) and (2) recordsets...

Thanks for your help
Rob

[jamescol]

Rob,
Is it possible to just create the Join in the intitial SQL query and obtain that as your RS? Or are the 2 RSs you get now from different DBs?

If they are from different DBs, then you will need to use VBA code or temp worksheets to join the data.

I did confirm that you can't use SQL statements to manipulate the RSs while disconnected. Have to move to VS.Net for that feature

James

[NateO]

Nate:

I am an old-timer with VB, although very rusty now. In fact, I was very flattered to find my name mentioned in Mr. McKinney's book you reference above. I've written some very involved VBA in Excel, several add-ins, but I am really lost with regard to ADO & database queries, etc.

I had to pull my pointer reference as my response was a little confusing, but a great read!

I just don't want to have to carry around an mdb file or write to a temporary range in the open workbook.

I think you have to pass the first recordset to a worksheet, preferably the one which holds your second RS table. Now you have one table which you can query against. Using screen updating, you can do this behind the scenes. This gives you your DB which you can connect to. I can't picture one connecting to a RS object burned in memory.

[robnjay]

JamesCol:

There are 3 separate entities involved: (1)The user's spreadsheet. The user is retrieving data to this spreadsheet using entity (2), my add-in, which is installed in his Excel. Entity (3) is the only real database, the process control database. The recordset from it is connected. The second recordset (or "table") involved needs to be derived from one of 2 sources: a column of dates or a variant array of Date/TimeStamps. A join on these two tables would produce a set of "reconciled" values which would be put into a column on the user's spreadsheet.

One interesting possibiity is that the process db has the functionality to create temporary tables. However, I believe this requires a stored procedure in the db, and I don't know how the overall structure of a solution involving a stored procedure would work....

Nate:

Whenever I have created Add-ins that need to "scratch memory" (or "scratch worksheet ranges"), I have made it a rule that this resides in the add-in... Is this possible?

I notice in your "query a worksheet" example on your website, you say to "make sure the worksheet is saved". Why is this? My database structure dictates that I retrieve (in some cases) one column at a time. Does this imply that I would have to save the WS I am querying over and over again?

[NateO]

Nate:

Whenever I have created Add-ins that need to "scratch memory" (or "scratch worksheet ranges"), I have made it a rule that this resides in the add-in... Is this possible?

Sure, pass the first recordset and the 2nd range in question to a sheet in the xla file.

I notice in your "query a worksheet" example on your website, you say to "make sure the worksheet is saved". Why is this? My database structure dictates that I retrieve (in some cases) one column at a time. Does this imply that I would have to save the WS I am querying over and over again?

Nope, it just needs to have a path and name for OLE DB to connect to the DB. So this will naturally occur after an initial save, whether someone is opening it or detaching it etc... The trick is the pointer in OLE DB, Thisworkbook.Fullname. If you pass that, ADO will connect to your add-in. If the xla is not saved, your connection string fails, it doesn't know where to look.

You can see this here:

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
     ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"

[Zack Barresse]

Hi Rob,

Did you ever find a solution on this topic? If so, which one?

[robnjay]

No I can't make Nate's stuff work either. I'm trying this:

Sub QueryMyself()
Dim cn As Object, rs As Object
Dim clcMde As Long, ws As Worksheet
Dim rData As Range
clcMde = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rData = ws.Range("Query_from_MS_Access_Database")
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & _
    ThisWorkbook.FullName & "';Extended Properties=Excel 8.0;"
Set rs = CreateObject("ADODB.Recordset")
With rs
    Set .ActiveConnection = cn
    .Source = "Select [TimeStamp], [Value], [Last6MinAvg]  From [" & ThisWorkbook.Names("Query_from_MS_Access_Database").Name & "] " & _
    "Where [Last6MinAvg] >= 25 Order By [Timestamp] DESC"
    .Open , , 3, 3
    ws.[F14].CopyFromRecordset rs
    .Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing
Sheets(1).Rows(1).Delete
Application.Calculation = clcMde
Application.ScreenUpdating = True
End Sub

I get the following error on the .open statement:

"'Sheet1!Query_from_MS_Access_Database' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long."

I got a few remarks about this error:
(1) M$ Excel (Office 2k) CREATED this name when it executed a query.
(2) I let Excel build the name from ThisWorkbook.Names()
(3) Jet 4.0 is installed
(4) The workbook is saved
(5) I've tried it with:
"From [ Sheet1!" & rData.Address & "] ...
which errors out in the same place, with
"Sheet1!$A$1:$C$1340 is not a valid name. (... rest is same as above)

"From [ Sheet1" & rData.Address & "] ...
which errors out in the same place, with
"Microsoft Jet Database Engine could not find the object Sheet1$A$1:$C$1340. Make sure the object exists and that you spell its name and the path name correctly."

[NateO]

Hello, your reference format is invalid.

You don't need to reference the workbook in the table as you're connected to it in your OLE DB connection string pass.

See the following for table referencing conventions:

http://support.microsoft.com:80/supp.../q257/8/19.asp

It discusses both named ranges and sheet/address references. Here's the relevent info:

Select Excel Data with Code

Your Excel data may be contained in your workbook in one of the following:

• An entire worksheet.
• A named range of cells on a worksheet.
• An unnamed range of cells on a worksheet.

Specify a Worksheet

To specify a worksheet as your recordsource, use the worksheet name followed by a dollar sign and surrounded by square brackets. For example: strQuery = "SELECT * FROM [Sheet1$]"
You can also delimit the worksheet name with the slanted single quote character (`) found on the keyboard under the tilde (~). For example: strQuery = "SELECT * FROM `Sheet1$`"
Microsoft prefers the square brackets, which are the standing convention for problematic database object names.

If you omit both the dollar sign and the brackets, or just the dollar sign, you receive the following error message:
... the Jet database engine could not find the specified object
If you use the dollar sign but omit the brackets, you will see the following error message:
Syntax error in FROM clause.
If you try to use ordinary single quotes, you receive the following error message:
Syntax error in query. Incomplete query clause.
Specify a Named Range

To specify a named range of cells as your recordsource, simply use the defined name. For example: strQuery = "SELECT * FROM MyRange"
Specify an Unnamed Range

To specify an unnamed range of cells as your recordsource, append standard Excel row/column notation to the end of the sheet name in the square brackets. For example: strQuery = "SELECT * FROM [Sheet1$A1:B10]"
A caution about specifying worksheets: The provider assumes that your table of data begins with the upper-most, left-most, non-blank cell on the specified worksheet. In other words, your table of data can begin in Row 3, Column C without a problem. However, you cannot, for example, type a worksheeet title above and to the left of the data in cell A1.

A caution about specifying ranges: When you specify a worksheet as your recordsource, the provider adds new records below existing records in the worksheet as space allows. When you specify a range (named or unnamed), Jet also adds new records below the existing records in the range as space allows. However, if you requery on the original range, the resulting recordset does not include the newly added records outside the range.

With MDAC versions prior to 2.5, when you specify a named range, you cannot add new records beyond the defined limits of the range, or you receive the following error message:
Cannot expand named range.

A nice overall reference.


Sorry if I'm not able to describe this in complete layman's terms, it's fairly technical stuff.

[robnjay]

OK. I got it to work:

Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rData = ws.Range("Query_from_MS_Access_Database")  
Set rs = CreateObject("ADODB.Recordset")
With rs
    Set .ActiveConnection = cn
    .Source = "Select [TimeStamp], [Value], [Last6MinAvg]  From [Sheet1$" & rData.Address(False, False) & "] " & _
    "Where [Last6MinAvg] >= 10 Order By [Timestamp] DESC"
    .Open , , 3, 3
    ws.[D14].CopyFromRecordset rs
    .Close
    .Source = "Select [TimeStamp], [Value], [Last6MinAvg]  From [Sheet1$" & rData.Address(False, False) & "] " & _
    "Where [Last6MinAvg] >= 25 Order By [Timestamp] DESC"
    .Open , , 3, 3
    ws.[H14].CopyFromRecordset rs
    .Close
End With

This is the _only_ way I can get the reference to work.... Even though there is REALLY a name in the workbook named "Query_from_MS_Access_Database", "From Query_from_MS_Access_Database" won't work.

PLEASE NOTE: the "secret" here was to retrieve the address of the range as RELATIVE (rData.Address(False, False)) and append it to the Worksheet name.

So "From [Sheet1$A1:C1340] ...." works!

[NateO]

PLEASE NOTE: the "secret" here was to retrieve the address of the range as RELATIVE (rData.Address(False, False)) and append it to the Worksheet name.

So "From [Sheet1$A1:C1340] ...." works!

Right, because $ signs in ADO mean something else.

[robnjay]

OK Nate,

'Splain why:
From Post Above:
This is the _only_ way I can get the reference to work.... Even though there is REALLY a name in the workbook named "Query_from_MS_Access_Database", "From Query_from_MS_Access_Database" won't work.

[NateO]

It should work if:

1) Your name is valid.
2) You are connecting to the correct workbook. The named range must be in the workbook that the code lies in if you're going to connect to ThisWorkbook.

The following, like a fox in the hen house, has chicken for supper every time:

Sub testeroo()
Dim cn As Object, rs As Object
Dim clcMde As Long
Let clcMde = Application.Calculation
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & _
ThisWorkbook.FullName & "';Extended Properties=Excel 8.0;"
Set rs = CreateObject("ADODB.Recordset")
With rs
    Set .ActiveConnection = cn
    .Source = "Select TimeStamp, Value, Last6MinAvg  From " & _
    "Query_from_MS_Access_Database Where Last6MinAvg >= 10"
    .Open , , 3, 3
    Sheets(2).Cells.ClearContents
    Sheets(2).[a1].CopyFromRecordset rs
    .Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing
With Application
    .ScreenUpdating = True
    .Calculation = clcMde
End With
End Sub

I have attached an example. So, I'm not quite sure what you have done at this point.