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.