What is it?
Why is it?
You can import Access table data to Excel without even opening the Access DB.
How to import Access table data to Excel
Step 1: Open the Excel Workbook and got to VBA code builder (Alt + F11) and Open a New Module.
Step 2: Copy and Paste the below code in it.
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String
strFilePath = “C:\DatabaseFolder\myDB.accdb” ‘Replace the ‘DatabaseFolder’ and ‘myDB.accdb’ with your DB path and DB name
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open “Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=” & strFilePath & “;”
sQRY = “SELECT * FROM tblData” ‘Replace ‘tblData’ with your Access DB Table name or Query name from which you want to download the data
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
Set rs = Nothing
Set cnn = Nothing
Access DB records have been successfully downloaded to Excel, Open your Excel workbook and check the data.
Note: Make sure ‘Microsoft ActiveX Data Objects Library’ is enabled from the Tools – References (number use latest version [6.1 as of this post]).