none
Excel Import Date from MS Access Based on ID in Table

    Question

  • In Excel, I'm trying to import data from MS Access.  Here's my code:

    Sub Access_to_Excel()
        Dim cn As Object, rs As Object
        Dim intColIndex As Integer
        Dim DBFullName As String
        Dim TargetRange As Range
        
        DBFullName = "C:\Users\Ryan\Desktop\Nwind_Sample.mdb"
        
        'On Error GoTo Whoa
        
        Application.ScreenUpdating = False
        
        Set TargetRange = Sheets("Sheet1").Range("A1")
        
        Set cn = CreateObject("ADODB.Connection")
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
        
        Set rs = CreateObject("ADODB.Recordset")
        rs.Open "SELECT * FROM Employees WHERE [EmployeeID] = '4', cn, , , adCmdText"
        
        ' Write the field names
        For intColIndex = 0 To rs.Fields.Count - 1
        TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
        Next
        
        ' Write recordset
        TargetRange.Offset(1, 0).CopyFromRecordset rs
        
    'use:
        Application.ScreenUpdating = True
        On Error Resume Next
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
        On Error GoTo 0
        Exit Sub
        
        'MsgBox "Error Description :" & Err.Description & vbCrLf & _
        '"Error at line     :" & Erl & vbCrLf & _
        '"Error Number      :" & Err.Number
        'Resume LetsContinue
    End Sub

    It's failing here:

    rs.Open "SELECT * FROM Employees WHERE [EmployeeID] = '4', cn, , , adCmdText"

    I got the idea from this site:

    http://stackoverflow.com/questions/17180513/importing-data-from-access-into-an-open-excel-spreadsheet-excel-vba

    Any idea what is wrong?  This must be a simple thing.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, June 22, 2013 6:34 PM

Answers

  • Is [EmployeeID] a text field?  Putting it in single quotes makes it a text field, not  numeric.  Usually, ID fields are numbers.  Also, the quote is at the end of the string which is incorrect.

    rs.Open "SELECT * FROM Employees WHERE [EmployeeID] = '4', cn, , , adCmdText"

    It should be something more like this:

    rs.Open "SELECT * FROM Employees WHERE [EmployeeID] = 4", cn, , , adCmdText


    Leo Scott

    • Proposed as answer by LEScott Sunday, June 23, 2013 3:33 PM
    • Marked as answer by ryguy72 Sunday, June 23, 2013 8:57 PM
    Sunday, June 23, 2013 3:33 PM

All replies

  • Is [EmployeeID] a text field?  Putting it in single quotes makes it a text field, not  numeric.  Usually, ID fields are numbers.  Also, the quote is at the end of the string which is incorrect.

    rs.Open "SELECT * FROM Employees WHERE [EmployeeID] = '4', cn, , , adCmdText"

    It should be something more like this:

    rs.Open "SELECT * FROM Employees WHERE [EmployeeID] = 4", cn, , , adCmdText


    Leo Scott

    • Proposed as answer by LEScott Sunday, June 23, 2013 3:33 PM
    • Marked as answer by ryguy72 Sunday, June 23, 2013 8:57 PM
    Sunday, June 23, 2013 3:33 PM
  • Yeap.  That's it.  Works perfect.

    Thanks!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, June 23, 2013 8:57 PM