As ever, you use this free program at your own risk.
Feel free to use the free software on this site but please honor the copyright.
This page will walk through the steps needed in order to connect an
Access™ database to Excel™. The collateral (including the video
demonstration) is available as a Zip file. It includes the VBA code, the
sample 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.
As normal the usual disclaimers apply.
Demonstration of Connecting an Access Dataset to Excel.
If you wish to see a demonstration video of connecting an Access database to an Excel, please click the button below
It is important to note that we intentionally made it fail in order to show what reference needs to be included in order to create the connection.
Step 1: Ensure the Workbook is Macro Enabled.
Open the target workbook. Make sure that the workbook is macro enabled. It should have an XLSM extension. If the workbook is not macro enabled, the code will not work.
Step 2: Activate the Developer Tab.
The first step is to activate the developer tab in Excel if it is not 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 below.
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.
A YouTube™ video on how to activate the "Developer" tab is available by clicking on the button below.
Step 3: Download the Zip file Containing the Files.
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.
The Zip file is available by clicking the button below.
Step 4: Extract the Zip File Members.
The next thing to do is to unzip the contents of the zip file.
Once the Zip file is downloaded you need to extract the sample files. Newer versions of Windows have a built in facility 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.
Step 5: Change the 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;" PRVD = "Microsoft.ace.OLEDB.12.0;" ' Connection Provider ' 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 available either in the Zip file or by clicking the button
below
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 field names. You should now be able to read the data from the databases into the worksheet. That is all there is to it. |
This material has been used successfully by Abbydale Systems LLC. and to the best of our knowledge this material and any system(s) of which it is a part are operational as of the service level or date stated in the body of this material (if so stated). However, NO warranty is given or implied as to the accuracy of this material or any related material or systems, and no responsibility is assumed for any effect or modification directly or indirectly caused by the use of this material.
It is the responsibility of any user of this material to evaluate its usefulness to the user's environment.
Abbydale Systems LLC. does not guarantee to keep this or any related material current, nor does it guarantee to provide any corrections or extensions described by any users of this material or any corrections or extensions made in the future by Abbydale Systems LLC. itself.
