VBA Code to Import Access Table /Query data to Excel

Share on facebook
Share on twitter
Share on linkedin

Import Access Table to Excel with VBA (Images and Code)

What is it?

This VBA code helps you to download or import Access Table data or Query to Excel in one click.

 

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.

XModule

Step 2: Copy and Paste the below code in it.

				
					Sub importAccessdata()

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

Sheet1.Range("A1").CopyFromRecordset rs

rs.Close

Set rs = Nothing

cnn.Close

Set cnn = Nothing

Exit Sub

End Sub
				
			

Step 3: Click the Run button or F5 (Also you can insert a button/shape in excel sheet then right click and assign this macro to run everytime.)

import access table

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]).

import access table



Anson Antony

Anson Antony

Anson is a contributing author and founder at ASKEYGEEK.com. Learning anything new has always been his passion, ASKEYGEEK.com is an outcome of his passion for technology and business. He has got a decade of versatile experience in Business Process Outsourcing, Finance & Accounting, Information Technology, Operational Excellence & Business Intelligence. During the tenure, he had worked for organizations like Genpact, Hewlett Packard, M*Modal and Capgemini in various roles and responsibilities. Outside business and technology, he is a movie buff who spends hours together watching and learning Cinema and a Film Maker too!

8 Responses

  1. Hi , wanted to check if I keep my Access DB in shared drive and distribute this excel macro in my organisation, will it work ? I want my excel macro to read some values from this Access DB and then update some variable when opening excel .

    Also if Access doesn’t work , whether I can use another excel kept in organisation one drive and access it instead of DB. I tried with workbook open method in my macro to read my onedrive excel which is shared to all in my company but it did not work .

  2. I finallly did.

    i’m using 2 variables (textbox1 as value ‘currency, format R$0.000,00’ and textbox2 as ‘date’, format mm/dd/yyyy)

    Code ↓

    ‘====================================================|
    ‘========================================== Kauê Vaz==|
    ‘====================================================|

    Option Explicit

    ‘ Click Tools, References and select
    ‘ Microsoft ActiveX Data Objects 2.0 Library
    Sub getDataFromAccess(strValue, strDate)

    Dim DBFullName As String
    Dim Connect As String, Source As String
    Dim Connection As ADODB.Connection
    Dim Recordset As ADODB.Recordset
    Dim Col As Integer
    Dim mySQLVariable As String
    Dim mySQLVariable_1 As String
    Dim strSQL As String

    mySQLVariable = strValue ‘Value from textbox1 (Currency format R$0.000,00)
    mySQLVariable_1 = strDate ‘Value from textbox2 (Date format mm/dd/yyyy)

    ‘SQL code
    strSQL = “SELECT * FROM tblDatabase WHERE ”
    strSQL = strSQL & ” [Value] = ‘” & (mySQLVariable) & “‘ and ”
    strSQL = strSQL & ” [Date] = ‘” & (mySQLVariable_1) & “‘ ”

    ‘ Database, select your database access
    DBFullName = “C:\Users\Desktop\Databases\BD_PaynotProcess\bd_PaynotProcess.accdb”

    ‘ Open connection
    Set Connection = New ADODB.Connection
    Connect = “Provider=Microsoft.ACE.OLEDB.12.0;”
    Connect = Connect & “Data Source=” & DBFullName & “;”
    Connection.Open ConnectionString:=Connect

    ‘ Creat RecordSet
    Set Recordset = New ADODB.Recordset
    With Recordset

    ‘ Search values using SQL Code variable strSQL
    Source = strSQL
    .Open Source:=Source, ActiveConnection:=Connection

    ‘ Importing columns from Database.
    For Col = 0 To Recordset.Fields.Count – 1
    Range(“A5”).Offset(0, Col).Value = Recordset.Fields(Col).Name
    Next

    ‘ Importing lines from Database.
    Range(“A5”).Offset(1, 0).CopyFromRecordset Recordset
    End With

    Set Recordset = Nothing
    Connection.Close
    Set Connection =

    ActiveSheet.Columns.AutoFit

    End Sub

    Regards

  3. Can i import just some datas? Filtering by 2 variables?
    Example:

    X = 13/03
    Y = ‘122315124’

    Bring from the access bd just the lines that contains this variables?

Leave a Reply

Your email address will not be published. Required fields are marked *

small_c_popup.png

Let's have a chat

Fill out the form below to consult with us