Read data from Access file into Excel

Question

I am trying to read/import data from a table in Access into a tab in an 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 - locate 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 extract).

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

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

My target cells are C5:M32 in my spreadsheet - sheets "happy".
How do I import the data into the cells belonging to each column with different header names?
Also 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.

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
– Golden Sun, Commented Jun 9, 2020 at 19:55
You can do this quickly and easily with no code: microsoft.com/en-us/microsoft-365/blog/2012/08/14/… — HackSlash
– HackSlash, Commented Jun 9, 2020 at 20:16
@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
– Golden Sun, Commented Jun 9, 2020 at 20:29
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
– HackSlash, Commented Jun 9, 2020 at 22:22

June7 · Accepted Answer · 2020-06-10 16:52:15Z

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"

Wow, June 7! Thanks for editing the code for me! I will do a test. Yes, I have Microsoft office 16.0 Access database engine object checked in VBE "References".
Hi June 7, just tested your code and I think I have made some mistakes. Set db = OpenDatabase(DBFullName,false,false,";pwd=HAPPY") Do i need to use the actual path of the access file here to replace DBFullname? something like: Set db = OpenDatabase("H:\Summary\test Folder\test.mdb", False, False, ";pwd=AIF") And where do i put DAOCopyFROMRecordSet "H:\HAPPY\Happy Folder\Happy DB.mdb", "Happy_Table", "CustomerID" When i run the module, the macro table pops up and the code is not running. Why is that? I must have done something wrong here.
Sorry for so many questions. I have two buttons set up in the main sheet. One is to write data into database, the other one is to import data from database. My spreadsheet has 8 tabs and 1-7 tabs are importing data correctly (this is handled by one part of the coding). And for last tab - tab 8, it needs to use the coding I am asking you guys to do the import.. So I create a separate module to test the import for tab 8 only, later on i will want to move the coding into the coding section where I import data for tab 1-7.
So ultimately, when i click one button and choose the company name, it will give me all data for tab 1-8.
As I said, can hard-code the workbook path and table name and field name and eliminate the arguments and call. Declare and set the variables within the procedure - as done with TargetRange, except String variables don't need Set.

Collectives™ on Stack Overflow

Read data from Access file into Excel

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related