Read data from Access file into Excel

Question

I am a newbie and just started learning VBA. I am trying to read/import data from a table in Access into a tab in excel spreadsheet. The data will be names/positions/compensations of certain companies.The headers of the columns in both tables (access and excel) are the same. When i read the data, the logic is - located the company ID in another Access table and then retrieve the data from the access table that stores the data (the company ID is important since the table stores the data of every company and ID is used to identify which company's data to exact).

This is the template i got from online:

Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, _
    FieldName As String, TargetRange As Range)
' Example: DAOCopyFromRecordSet "C:\FolderName\DataBaseName.mdb", _
    "TableName", "FieldName", Range("C1")
Dim db As Database, rs As Recordset
Dim intColIndex As Integer
    Set TargetRange = Sheets("MIC").Cells(1, 1)
    Set db = OpenDatabase(DBFullName)
    Set rs = db.OpenRecordset(AIF_MIC, dbOpenTable) ' all records
    'Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _
        " WHERE " & FieldName & _
        " = 'MyCriteria'", dbReadOnly) ' filter records
    ' write field names
    For intColIndex = 0 To rs.Fields.Count - 1
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    Next
    ' write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

And here is my actual code:

Sub DAOCopyFromRecordSet("H:\HAPPY\Happy Folder\Happy DB.mdb", "Happy_Table","A,B,C" ,Sheets("Happy").Range("C5:M32")
Dim db As Database, rs As Recordset
Dim intColIndex As Integer
    Set TargetRange = Sheets("Happy").Cells(1, 1)
    Set db = OpenDatabase(DBFullName,false,false,";pwd=HAPPY")
    Set rs = db.OpenRecordset(Happy, dbOpenTable) ' all records
    'Set rs = db.OpenRecordset("SELECT * FROM " & Happy & _
        " WHERE " & FieldName & _
        " = 'MyCriteria'", dbReadOnly) ' filter records
    ' write field names
    For intColIndex = 0 To rs.Fields.Count - 1
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    Next
    ' write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

I am not sure how to continue my coding...My target cells are C5:M32 in my spreadsheet - sheets "happy"...How does the coding import the data correctly into the cells belongs to each column with different header names...and I have to make sure the coding is giving me the data for the correct company since all companies have the same type information saved in the access table..

I apologize if I cannot explain this with a good logic..If any programming guru can help me on skype, I would appreciate too!

It is nice to join this community!

Any help is appreciated!

to make it more clear: there will be 12 columns in both access table and the spreadsheet table...I need to make sure the data can be read into the cells under the correct column...There is a loadID in access table too that indiciates which company the data points to... — Golden Sun
You can do this quickly and easily with no code: microsoft.com/en-us/microsoft-365/blog/2012/08/14/… — HackSlash
@HackSlash Thanks for your feedback. For my work, i need to save data into the access file using the excel and at the same time, i will need to extract data of a company from the access file when i want. I have the macro buttons set up in the main worksheet. So, it is not just one step but it will be a repetitive work. Hope this makes sense :((( — Golden Sun
Yeah, the link I gave shows how to do that. Maybe they don't know that it's a link. You can choose various options about when you want to refresh the latest data from Access. — HackSlash

June7 June7 · Accepted Answer · 2020-06-10T04:19:23

This procedure is designed to run in a General Module in Excel VBA, make sure that's what you really want.

For starter, the Sub declaration is wrong. This is not where you specify the database, table, field, range. Look at the example you adapted. It has variable arguments in the declaration, not literal strings. Could remove the arguments and hard code source within procedure. Since you specify TargetRange within procedure, can certainly eliminate the TargetRange argument.

Since code is declaring specific type objects (Database and Recordset), need to set reference in VBE > Tools > References > MS Office x.x Access database engine Object Library. This is 'early binding'.

One issue will probably encounter is if this is overwriting data already in cells, really need to clear the data or could end up with leftover data if new data does not extend through end of existing rows.

No idea how you want to obtain filter criteria for the SQL statement. Could prompt user for inputs.

I tested and following worked for me.

Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, FieldName As String)
Dim db As Database, rs As Recordset
Dim intColIndex As Integer
Dim TargetRange As Range
    Set TargetRange = Sheets("Happy").Range("C5")
    Set db = OpenDatabase(DBFullName,false,false,";pwd=HAPPY")
    Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records
    'Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _
        " WHERE [" & FieldName & "] = " & InputBox("Enter customer ID", , 0), dbReadOnly) ' filter records
    'clear old data
    Sheets("Happy").Range("C:M").Value = ""
    ' write field names
    For intColIndex = 0 To rs.Fields.Count - 1
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    Next
    ' write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

Call the procedure in an event such as a button click.

DAOCopyFROMRecordSet "H:\HAPPY\Happy Folder\Happy DB.mdb", "Happy_Table", "CustomerID"

Read data from Access file into Excel

1 Answers