VBA - Connecting a database (MDB) to Excel

Ask a question
Microsoft Excel, which is popular SpreadSheet software in Office, enables users to connect to the Microsoft Access MDB database. Connecting Microsoft Access MDB database to Excel can be carried out using macros. A little knowledge of Excel and macros may be required. A Microsoft DAO object library is required to connect the Access database to Excel Worksheets. It is possible to open an Access Database in Excel and access the data in the database. Various data manipulations such as copying data in the fields to columns in the Excel Worksheet and other calculations can be carried out by connecting Microsoft Access database to Excel.
Below are tips on how to connect an Access database (MDB) to Excel
  • Add reference
  • Microsoft DAO object library X.X

In a general module, (eg Module1) paste the code below

Sub CopyDBaccess()     
Dim BDexp As Database     
Dim Table As Recordset     
Dim TbDef As TableDef     
Dim Ch As String, Lig As Long, i As Integer     
    Ch = "PAth & \ & NameofDB.MDB"     
    Set BDexp = DBEngine.Workspaces(0).OpenDatabase(Ch)     
    Set Table = BDexp.OpenRecordset("NameofTable", dbOpenDynaset)     
    'Debug.Print Table.Name     
    Set TbDef = BDexp.TableDefs("NameofTable")     
    Lig = 3     
dim  Name(TbDef.Fields.Count - 1) As String     
'Put the titles in the column    
With Sheets("Sheet1")     
    For i = 0 To TbDef.Fields.Count - 1 'Pour avoir toute la ligne     
        'Debug.Print TbDef.Fields(i).Name     
        Name(i) = TbDef.Fields(i).Name     
        .Cells(Lig, i + 3) = Name(i)     
    'Caller from 1st record    
    Lig = 4     
    While Not Table.EOF     
        For i = 0 To TbDef.Fields.Count - 1 'For all the line    
            .Cells(Lig, i + 3) = Table(Name(i))     
        Next i     
        Lig = Lig + 1     
        Table.MoveNext  'Go to next record    
End With    
    Set BDexp = Nothing     
    Set Table = Nothing     

End Sub

Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team