This page will walk through the steps needed in order to connect an
Access™ database to Excel™. The colateral (including the video demonstration) is available as
a Zip. It includes the VBA code, the smaple Access database and the Excel spreadsheet.
If you chose to use the Access database correctly you will need to change the VBA code to point
to the fully qualified location where you have placed the database.
Download the Zip file that contains the sample Access and Excel files and save it onto your desired storage device.
This also contains the VBA code and the above video file.
Extract the Zipped Files
Once the Zip file is downloaded you need to extract the sample files. Newer versions of Windows have a built in facilty for doing this, or
you can use any of the Zip products available for free.
We use 7-Zip™ which is freely available from www.7-zip.org
Activating the Developer Tab
In order to run the code in the Excel spreadsheet and to include the required references you need
to activate the developer tab if it isn't already there.
There are two ways of achieving this.
If you want to permanently have the developer tab available you can do this by following the directions in the video alongside.
The other method is to press "ALT"+"F11" at the same time. Remember that your keyboard may also require you to press the "fn" key also.
Either of these methods should open the "Developer" screen.
Change Database Path in VBA
Public Sub ConnectDatabase()
Dim conn As New ADODB.Connection, rs As New ADODB.Recordset
Dim DBPATH, PRVD, connString, qry As String
Dim WorkSheet As String
Worksheet = "Sheet1" ' Specify the target Worksheet
' Declare fully qualified name of database. DBPATH = "D:\Abbydale Systems\Access Databases\Abbydale Sample.accdb;"
' Declare the ODBC Connection Name connString = "Provider=" & PRVD & "Data Source=" & DBPATH
conn.Open connString ' Open the connection
' Build the query qry = "SELECT * FROM tblSample1 ORDER BY tblSample1.[fldManufacturer],tblSample1.[fldColor];"
rs.Open qry, conn, adOpenStatic ' Now run it If rs.RecordCount > 0 Then
x = 1
Do Until rs.EOF
ThisWorkbook.Sheets(Worksheet).Cells(x, 1).Value = rs.Fields(1).Value ' Make
ThisWorkbook.Sheets(Worksheet).Cells(x, 2).Value = rs.Fields(2).Value ' Model
ThisWorkbook.Sheets(Worksheet).Cells(x, 3).Value = rs.Fields(3).Value ' Color
rs.MoveNext ' Move to next record
x = x + 1 ' Next output line
Loop
End If
rs.Close ' Close the recordset
conn.Close ' Close the database connection
Set rs = Nothing
Set conn = Nothing
End Sub
The code alongside is avail either in the Zip file or by clicking the link below
The code itself will need changing to xcorrectly identify the fully qualified path and name of the Access database (denoted in red).
If you chose to rename the tables and fields in the database you will need to change the fields denoted in purple to
match your own table and fiel names.
You should now be able to read the data from the databases into the worksheet.
If you need any support or assistance with any of the code on this site or if you would like to contact us, please click here
