Import Data into Excel from Access Table

I am trying to import data into Excel from an Access table. I am getting a syntax error This error comes up when I run the line: Select [Time], [Tank], FROM "UnitOneRouting", WHERE [Date] = " & RpDate & ORDER BY Tank, Time", cn, adOpenStatic, adLockOptimistic, adCmdTable In the Access Table there are four columns (in order) Date, Time, Tank, Comments. I only want to import two columns, Time and Tank but in the order Tank, Time; and I want to import them based on a date that is given in the Excel sheet. Thanks

How can I rearrange the following section to open the table, select the columns (based on date) and import the data.

With rs
            ' open the recordset
            .Open "UnitOneRouting", cn, adOpenStatic, adLockOptimistic, adCmdTable
            ' filter rows based on date
            .Select [Time], [Tank],  FROM "UnitOneRouting", WHERE [Date] = " & RpDate & ORDER BY Tank, Time", cn, adOpenStatic, adLockOptimistic, adCmdTable
            rs.Open , TargetRange
        End With

CODE STARTS HERE

    Sub ADOImportFromAccessTable()
Dim DBFullName As String
Dim TableName As String
Dim TargetRange As Range
Dim RpDate As Range
 
DBFullName = "U:Night SupProduction Report 2003 New Ver 5-28-10_KA.mdb"
TableName = "UnitOneRouting"
Set TargetRange = Range("C5")
Set RpDate = Range("B2").Cells


Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    ' open the database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
        "U:Night SupProduction Report 2003 New Ver 5-28-10_KA.mdb" & ";"
    Set rs = New ADODB.Recordset
    With rs
        ' open the recordset
        .Open "UnitOneRouting", cn, adOpenStatic, adLockOptimistic, adCmdTable
        ' filter rows based on date
        .Select [Time], [Tank],  FROM "UnitOneRouting", WHERE [Date] = " & RpDate & ORDER BY Tank, Time", cn, adOpenStatic, adLockOptimistic, adCmdTable
        rs.Open , TargetRange
    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Answer

The issue is the second and third .Open command. adjust your with statement to:

With rs
    .Open "SELECT Time, Tank  FROM " & TableName & " WHERE [Date] = " & RpDate & _
"ORDER BY Tank, Time", cn, adOpenStatic, adLockOptimistic, adCmdTable
    ' all records
    ' cn.CursorLocation = adUseClient
    ' filter rows based on date

End With

Advertisement

Answer