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
See also
Knowledge communities.
Original article published by
lermite222. Translated by
deri58. -
Latest update by Jeff 
