[VBA] Connecting a database (MDB) to excel

Below is a tips of how to connect an Access database (MDB) in an application excel
Add reference
Microsoft DAO object librairy X.X


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

<ital>Sub CopieDBaccess ()
Sunday BDexp As Database
Sunday Table As Recordset
As Sunday TbDef TableDef
Sunday Ch As String, Lig As Long, i As Integer, Name (4) As String
Ch = "& path \ & NomDeLaBD.MDB"
Set BDexp = DBEngine.Workspaces (0). OpenDatabase (Ch)
Set Table = BDexp.OpenRecordset ( "tablename", dbOpenDynaset)
'Debug.Print Table.Name
Set TbDef = BDexp.TableDefs ( "companydetails")
Lig = 3
'Place the column
With Sheets ( "Sheet1")
For i = 0 To TbDef.Fields.Count - 1 'For any line
'Debug.Print TbDef.Fields (i). Name
Name (i) = TbDef.Fields (i). Name
. Cells (Lig, i + 3) = Name (i)
Next
'Caller registration on 1
Table.MoveFirst
Lig = 4
While Not Table.EOF
For i = 0 To TbDef.Fields.Count - 1 'For any line
. Cells (Lig, i + 3) = Table (Name (i))
Next i
Lig Lig + 1 =
Table.MoveNext 'move to the next record
Wend
End widt
Table.Close
BDexp.Close
Set BDexp = Nothing
Set Table = Nothing