Import Data into Excel from Access Table, Code to Open Table and Specific Columns

Question

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 Sup\Production 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 Sup\Production 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

JNevill JNevill · Accepted Answer · 2014-09-25T15:45:51

Editted: You have a lot of problems here:

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

When you open your recordset you should specify the SQL statement, not just a table. That is meaningless to ADO. Furthmore, your SQL statement is bad. There should be no comma after the last field in your SELECT clause and no comma after your table in your FROM clause and your table name should not have double quotes around it and... a few other things about encapsulating your strings properly in the embedded SQL statement itself. Try instead:

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


End With
TargetRange.CopyFromRecordset rs

Import Data into Excel from Access Table, Code to Open Table and Specific Columns

1 Answers