MyTetra Share
Делитесь знаниями!
How To – Connect Excel To Access – Database Using VBA
Время создания: 10.10.2019 07:03
Текстовые метки: Connect Excel To Access, ADO, Connection, Recordset, Excel-Access
Раздел: !Закладки - VBA - Access - ADO


How To – Connect Excel To Access – Database Using VBA

Kumarapush


Learn how to connect Access database to Excel using VBA macro code. Thus uses ADODB connections methods to connect & query MDB Access database.

How to Connect Access Database in Excel Macros?


Updating Excel Spreadsheet from Access Database using this step by step Excel VBA Access Macro code is just that simple. We are going to use a ADODB connection in this sample code.


Just copy paste this Excel VBA Access MDB conenction code to your VBA Project.

Excel To Access Connection – Simplest Code


Just change these two things in the code. It is enough for this to work better.


Database path – sDBPath : This should have the exact folder path & MDB Access file name that is being accessed with this code.

Query String – sQuery : The SQL query in this variable should match the Database Table & the fields exactly as how it is defined in the MDB database.


Once these two fields are edited, then this code will perfectly fine in a Excel VB Editor.


'--------------------------------------------------------------------------------

'Code by author@officetricks.com (or) kumarapush777 (Fiverr)

'Visit http://officetricks.com to get more Free & Fully Functional VBA Codes

'--------------------------------------------------------------------------------

Sub VBA_Connect_To_MDB_ACCESS()


'Make Reference to Microsoft AxticX Data Objects Library

Dim dbConn As ADODB.Connection, dbRecSet As ADODB.Recordset

Dim sConnString As String, sQuery As String

Dim sDBPath As String

'Define MDB ACCESS file path

sDBPath = "D:\OfficeTricks\SalesOrders.accdb"

'SQL Query String

sQuery = "SELECT CustomerID, CustFirstName , CustLastName from Customers;"

'ADODB Conenction String to initiate connection with MDB ACCESS

sConnString = "Provider=Microsoft.ace.OLEDB.12.0; Data Source=" & sDBPath & ";"

'Open Connection

Set dbConn = New ADODB.Connection

dbConn.Open ConnectionString:=sConnString

'Execute SQL Query & Get Records matching the query to recordset

Set dbRecSet = New ADODB.Recordset

dbRecSet.Open Source:=sQuery, ActiveConnection:=dbConn

'If Query Returned Values, Read them one by one

If (dbRecSet.RecordCount <> 0) Then

Do While Not dbRecSet.EOF

MsgBox dbRecSet.Fields(1).Value

dbRecSet.MoveNext

Loop

End If

'Close Connection & RecordSet

Set dbRecSet = Nothing

dbConn.Close

Set dbConn = Nothing

End Sub


Make sure that the MDB database Table has correct field names as specified in the query. Also it has enough data.


Note: Before executing this code, from your VB Edifor go to Tools in the menu -> References & add a reference to “Microsoft ActiveX Data Objects Library”. This is to make sure that ADODB object can be created from within the VBA Macro code.


The loop after that is present after the recordset.open command will get records from the table one by one, till end of the table. Make sure the replace the msgbox command with some assignment. If not you will end up in giving too many ‘OK’ clicks for the message box that pops up for every record fetch.


Apart from recordset.Movenext, there are other commands available to move the cursor or current position to First or last record or to any desired point as well.


In the next tutorial, we will see how to query Access database & load them to a list box in userform.

Так же в этом разделе:
 
MyTetra Share v.0.52
Яндекс индекс цитирования