How to Retrieve Access Database Data in Excel Using VBA and SQL
Question details
The user needs to retrieve records from an online Microsoft Access database and place them directly into an Excel worksheet using VBA and SQL.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating data retrieval by connecting an Excel workbook to an external Access database via code.
- Observed behavior
- Requires establishing a successful database connection using the correct ACE or Jet OLE DB provider, running an SQL query, and pasting the resulting recordset into the worksheet.
Ensure you have the exact file path or URL for your Access database and verify whether it is an older .mdb file or a newer .accdb file, as this determines the required connection provider.
Use ADODB Connection and CopyFromRecordset in VBA
This method establishes a direct connection to the Access database using ActiveX Data Objects (ADO), runs an SQL query, and dumps the retrieved data into Excel efficiently.
To connect Excel to Access, you must use the Microsoft ActiveX Data Objects library. The connection relies on either the Microsoft Jet OLE DB provider (for .mdb files) or the Microsoft ACE OLE DB provider (for .accdb files).
Once the connection is open, the script executes an SQL query to gather the required data into a Recordset, which is then written to the worksheet using the native CopyFromRecordset method.
Open Excel and press ALT + F11 to launch the VBA Editor. Go to the top menu and click Tools > References. Scroll down the list, check the box for 'Microsoft ActiveX Data Objects 6.1 Library' (or the highest version available), and click OK.
Create a new Module and declare your variables for ADODB.Connection and ADODB.Recordset. Set up your connection string. For .accdb files, use 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YourDatabasePath.accdb;'. For .mdb files, use 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourDatabasePath.mdb;'.
Use the Open method on your Connection object. Next, define your SQL SELECT statement as a string (e.g., 'SELECT * FROM TableName'). Open the Recordset by passing the SQL string and the active connection object to the Recordset.Open method.
Specify the destination cell in your Excel worksheet (for example, Worksheets("Sheet1").Range("A2")). Use the CopyFromRecordset method on this range, passing your Recordset object as the argument to paste all retrieved records instantly.
Always close the Recordset and Connection objects using the .Close method, and set the objects to Nothing to free up memory resources and prevent database locking.
Automate Your Data Tasks with WPS Spreadsheets
WPS Office provides robust spreadsheet functionalities with excellent support for complex data processing. Premium versions of WPS Office fully support VBA macros, allowing you to run your ADO database connection scripts seamlessly.
- 1. Download and Install WPS Office: Get the latest version of WPS Office from the official website and install it on your computer.
- 2. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheets and open your .xlsm file containing the database connection code.
- 3. Access the VBA Editor: Navigate to the Developer tab and click on the VBA Editor to view, edit, or run your SQL retrieval scripts.
Frequently Asked Questions
What is the difference between ACE and Jet OLE DB providers?
The Microsoft Jet OLE DB provider is an older engine primarily used to connect to legacy Access databases (.mdb files). The Microsoft ACE OLE DB provider is the newer engine required for connecting to modern Access databases (.accdb files) and Excel workbooks, while also providing backward compatibility for older formats.
Why does my VBA code throw a 'User-defined type not defined' error?
This error occurs because the Microsoft ActiveX Data Objects library has not been enabled in your project. To fix this, open the VBA Editor, navigate to Tools > References, and check the box next to the appropriate Microsoft ActiveX Data Objects Library version.
Can I retrieve data from a password-protected Access database?
Yes, you can retrieve data from a password-protected database by adding the password to your connection string. For example, append 'Jet OLEDB:Database Password=YourPassword;' to your provider string before opening the connection.
Why is CopyFromRecordset pasting empty data?
This happens if your SQL query returns no matching records, or if the Recordset cursor has already reached the end of the file (EOF). Ensure your SQL SELECT statement is correct and that you haven't already looped through the recordset before calling CopyFromRecordset.
