Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Posts
    5

    Unanswered: Query Against Recordset

    I guess it isn't possible to SQL query against a recordset, but I need to be able to:

    1) Retrieve a "Raw Data" recordset with 2 fields: "Timestamp" (a Date/Time) and "Value" (value corresponding to "Timestamp"). I can do this.

    2) Insert the records from this recordset into a temporary (virtual? Object?) Data Table without opening MS Access.

    3) Build a temporary table of all the possible "Timestamps" for the period I'm interested in, without opening MS Access.

    4) Query against both tables to obtain a recordset with two fields, "Timestamp" and "Value", such that any Timestamp missing from the "Raw Data" query would be in this recordset with a null (or blank) value.

    How could this be done in Excel VBA?

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi robnjay

    how about something like this

    Code:
    Sub TestADO()
        Dim con As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim rst As ADODB.Recordset
        
        Dim tbl As Table
        Dim cat As ADOX.Catalog
        
        Dim strConnection As String
        Dim strSQL As String
        Dim dDate As Date
        Dim dDateAdd As Date
        Dim i As Integer
        Dim n As Integer
        
        'create a connection string for the database
        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\filepath\filename.mdb;"
    
        'set up the catalog
        Set cat = New ADOX.Catalog
        cat.ActiveConnection = strConnection
        
        
        'loop through the tables and if it exists delete it
        For Each tbl In cat.Tables
            If tbl.Name = "tblDates" Then
                cat.Tables.Delete tbl.Name
            End If
        Next tbl
        
        'define and append the new table
        Set tbl = New Table
        tbl.Name = "tblDates"
        tbl.Columns.Append "dDates", adDate
        cat.Tables.Append tbl
        
        'set up the connection to the DB
        Set con = New ADODB.Connection
        con.ConnectionString = strConnection
        con.Open
        
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = con
        
        'set up value of dDate
        If Range("A1").Value < Range("A2").Value Then
            dDate = Range("A1").Value
        Else
            dDate = Range("A2").Value
        End If
        
        For i = 1 To Abs(DateDiff("d", Range("A1").Value, Range("A2").Value)) + 1
            'get right date
            dDateAdd = DateAdd("d", i - 1, dDate)
            'add string to add dates into new table
            strSQL = "INSERT INTO tblDates (dDates)" & _
                    "SELECT #" & Format(dDateAdd, "mm/dd/yyyy") & "#;"
            cmd.CommandText = strSQL
            cmd.Execute
        Next i
        
        'fill a recordset as required
        strSQL = "SELECT tblDates.dDates, tblStock.stockGathered " & _
                    "FROM tblDates LEFT OUTER JOIN tblStock " & _
                    "ON tblDates.dDates = tblStock.ChangeDate;"
        
        Set rst = New Recordset
        cmd.CommandText = strSQL
        Set rst = cmd.Execute
        
        'write the recordset to worksheet2
        rst.MoveFirst
        n = 1
        With Worksheets(2)
            Do While Not rst.EOF
                For i = 0 To rst.Fields.Count - 1
                    .Cells(n, i + 1).Value = rst.Fields(i)
                    .Cells(n, i + 1).NumberFormat = "dd/mm/yyyy"
                Next i
                rst.MoveNext
                n = n + 1
            Loop
        End With
        
        
        rst.Close
        Set rst = Nothing
        'delete the temp table
        cat.Tables.Delete tbl.Name
        
        'close and empty object variables
        con.Close
        Set con = Nothing
        Set cmd = Nothing
        
        Set cat = Nothing
        Set tbl = Nothing
    End Sub
    changing the relevant info to your own
    ive used the references

    Microsoft ActiveX Data Objects 2.7 Library
    Microsoft ADO ext. 2.7 for DDL and Security

    All the best
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •