Kioskea
Recherche
Ask a question »

VBA - Connecting a database (MDB) to Excel

March 2015

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.

VBA - Connecting a database (MDB) 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)     
    Next     
    'Caller from 1st record    
    Table.MoveFirst     
    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    
    Wend     
End With    
    Table.Close     
    BDexp.Close     
    Set BDexp = Nothing     
    Set Table = Nothing     

End Sub

For unlimited offline reading, you can download this article for free in PDF format:
Vba-connecting-a-database-mdb-to-excel.pdf

See also

In the same category

VBA - Connecter une base de donnée (MDB) à excel
By lermite222 on February 9, 2009
Original article published by lermite222. Translated by deri58. - Latest update by Jeff
This document entitled « VBA - Connecting a database (MDB) to Excel » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.