Connect to DataBase from VBA/Excel Post 3.0

Through VBA we can connect Microsoft Database(MDB) and can access the data from tables in the MDB. This data can be populated in the Excel sheet from these tables.
I have already explained some of the basics regarding VBA in my earlier posts. So today we will see how we can connect to MDB through VBA.
Below is the code which can be used to pull data from MDB and populate in Excel:
Before moving the actual code we should understand what is .accdb file. The accdb file format was introduced with release of Microsoft Access 2007 to replace the older MDB file format. So when we use ACCDB format allow users to leverage enhanced fucntionality in access ,including the use of multivalued fields and strong cryptography.




Sub prConnecttoMDB()
On Error GoTo Err_prConnecttoMDB
Dim strConnection   As String
Dim strSQl          As String


strConnection = "ODBC; DSN=MS Access Database;DBQ=D:\LearnVBA\Test_01.accdb; Driver={Driver do Microsoft Access (*.accdb)}"
strSQl = "SELECT TestTable.SerialNo, TestTable.MemberName, TestTable.School,TestTable.NativePlace,TestTable.Income FROM TestTable"


With ActiveSheet.QueryTables.Add(Connection:=strConnection, Destination:=ActiveSheet.Range("Test"))
    .CommandText = strSQl
    .Name = "TestTable"
    .Refresh BackgroundQuery:=False
End With


Exit_prConnecttoMDB:
    Exit Sub


Err_prConnecttoMDB:
    GoTo Exit_prConnecttoMDB
End Sub

The below code pulls the data from the MDB file placed at D:\LearnVBA\Test_01.accdb to Excel active sheet in range Test.