Get Data From Access Database with Excel VBA

Question

I am having trouble getting data from an Access Database. I found this code online, and it seems to work (to an extent), but for some reason it will only pull the column headers, and none of the data from the query. I am not too familiar with Access, that is why I pulled one from offline.

Someone had a similar post a while back, where the code they used was the same, and our queries were exactly the same, but we had different issues. Importing Data From Access Using Excel VBA

Would anyone happen to know why the data won't pull?

Sub getDataFromAccess()   

Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim startdt As String
Dim stopdt As String
Dim refresh

refresh = MsgBox("Start New Query?", vbYesNo)
If refresh = vbYes Then
    Sheet1.Cells.Clear
    startdt = Application.InputBox("Please Input Start Date for Query (MM/DD/YYYY): ", "Start Date")
    stopdt = Application.InputBox("Please Input Stop Date for Query (MM/DD/YYYY): ", "Stop Date")

    DBFullName = "X:\MyDocuments\CMS\CMS Database.mdb"
    ' Open the connection
    Set Connection = New ADODB.Connection
    Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
    Connect = Connect & "Data Source=" & DBFullName & ";"
    Connection.Open ConnectionString:=Connect

    Set Recordset = New ADODB.Recordset
    With Recordset
        Source = "SELECT * FROM Tracking WHERE [Date_Logged] BETWEEN " & startdt & " AND " & stopdt & " ORDER BY [Date_Logged]"
        .Open Source:=Source, ActiveConnection:=Connection

        For Col = 0 To Recordset.Fields.Count - 1
            Range(“A1”).Offset(0, Col).Value = Recordset.Fields(Col).Name
        Next

        Range(“A1”).Offset(1, 0).CopyFromRecordset Recordset
    End With
    ActiveSheet.Columns.AutoFit
    Set Recordset = Nothing
    Connection.Close
    Set Connection = Nothing

End Sub

Answer 1

An easy way to get data in Excel, especially from Access, is to use the menu "Data > Access". This creates a connection to a table, that you can freely edit.
At the very least, that is a convenient way to limit your investigations to:

• the query you typed (the connection string will always be OK, so if you're getting no values, it comes from the query)
• or the VBA itself (if the table is returning values but not the corresponding VBA Sub, then you know it comes from the VBA itself, not the SQL).

I'm skipping the creation of connection becuse it's really straightforward; it's better to focus on what you can do once the table has been created.

Edit the connection

When you select the table and go to menu "Data > Properties", then in the window you click on the top right button "Connection properties", you get to the definition of the connection, i.e. some properties in the first tab and the actual definition in the second tab.

If you move the .mdb file, you'll have to change the connection string accordingly. There should be no other events forcing you to alter it.

If you want to type an actual complex query, you'll need to:

1. Change the command type from "Table" to "SQL"
2. Type the query in the bottom edit box.
   Note if you want to define dynamic parameters in the WHERE clause, you can put question marks (?) instead of hardcoded values. Question marks can be linked to either constants (with a prompt to change their values) or cell.

Use in VBA

Once you checked with the connection that everything works, you have 2 solutions to put that in VBA.

Either use exactly the code you have above; in that case, you can make things easy by simply copying the connection string and the query.

Alternatively and this is what I would recommend, the table we have built previously can be updated very easily in VBA.

Use this piece of code:

WorksheetWithTable.ListObjects(1).QueryTable.Refresh 

You really don't need more than this 1 line of code to do the refresh.
If you set your query to automatically refresh when a cell's value is being modified, then you do not even need it at all.

Note #1: Instead of an index in .ListObjects(1), you can use the table name.
Node #2: Refresh has an optional parameters to drive if the query is to be refresh in the background. True means the VBA code will not wait for the execution to end before moving to the next instruction. False, obviously, is the opposite.

Answer 2

I solved the answer to my own question after hours, i found a different set of code that worked fine. Thank you all for your help!

Sub getdatamdb()
          Dim cn As Object, rs As Object
          Dim intColIndex As Integer
          Dim DBFullName As String
          Dim TargetRange As Range

10        DBFullName = "X:\MyDocuments\CMS\CMS Database.mdb"

20        On Error GoTo Whoa

30        Application.ScreenUpdating = False

40        Set TargetRange = Sheets("Sheet1").Range("A1")

50        Set cn = CreateObject("ADODB.Connection")
60        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"

70        Set rs = CreateObject("ADODB.Recordset")
80        rs.Open "SELECT * FROM Tracking WHERE [Date_Logged] BETWEEN #" & startdt & "# AND #" & stopdt & "# ORDER BY [Date_Logged]", cn, , , adCmdText

          ' Write the field names
90        For intColIndex = 0 To rs.Fields.Count - 1
100           TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
110       Next

          ' Write recordset
120       TargetRange.Offset(1, 0).CopyFromRecordset rs

LetsContinue:
130       Application.ScreenUpdating = True
140       On Error Resume Next
150       rs.Close
160       Set rs = Nothing
170       cn.Close
180       Set cn = Nothing
190       On Error GoTo 0
200       Exit Sub
Whoa:
210       MsgBox "Error Description :" & Err.Description & vbCrLf & _
             "Error at line     :" & Erl & vbCrLf & _
             "Error Number      :" & Err.Number
220       Resume LetsContinue
End If
End Sub

Answer 3

The posted code is missing End If line. Perhaps this is just a posting typo because code should not compile and run.

The query SQL needs # delimiters for the date parameters:

Source = "SELECT * FROM Tracking WHERE [Date_Logged] BETWEEN #" & startdt & "# AND #" & stopdt & "# ORDER BY [Date_Logged]"

Text field would need apostrophe delimiters. Number field does not need delimiters.