Connect to MS Access To Excel VBA

How to connect to ms access database using excel VBA

In this query, we have to follow multiple steps.

  • We need to add a library of Microsoft ActiveX Data Objects 6.1 Library in the tool. This is the Early binding method also we can do this from the Late Binding method so we will declare a variable as an object.
    • Go to tools then references
    • Check Microsoft ActiveX Data Objects 6.1 Library in the tool. As per the installed office version, the Library version is also different. You need to select as per the given version
  • Declare Connection and Recordset Variable
  • Create Connection and Recordset object
  • We need to give the provider name as per the installed version of MS Office
  • We will give here database path in the code
  • Then we can do transactions of data from Access to Excel and Excel to Access.

Option Explicit

Sub AccessToExcel()
    
    'Early Binding
    
    'Declare Connection object
    Dim Conn As ADODB.Connection
    'Declare Recordset Object
    Dim Rec As ADODB.Recordset
    Dim SQL As String
    Dim C As Integer
    
    'Create Connection object
    Set Conn = New ADODB.Connection
    'Create Recordset object
    Set Rec = New ADODB.Recordset
    
    Conn.Provider = "Microsoft.ACE.OLEDB.12.0"
    'Conn.Provider = "Microsoft.JET.OLEDB.4.0"
    
    Conn.Open "C:\Users\akash\OneDrive\Desktop\Akash\VBA Training\Jayesh\Data.accdb"
    
    SQL = "Select id, name, address from Data"
    
    Rec.Open SQL, Conn, adOpenDynamic, adLockOptimistic
    
    For C = 0 To Rec.Fields.Count - 1
        Sheet2.Range("A1").Offset(0, C).Value = Rec.Fields(C).Name
    Next C
    
    Sheet2.Range("A2").CopyFromRecordset Rec
    MsgBox "Done !!!"
End Sub


Sub ExcelToAccess()
    
    'Early Binding
    
    'Declare Connection object
    Dim Conn As ADODB.Connection
    'Declare Recordset Object
    Dim Rec As ADODB.Recordset
    Dim R As Integer, C As Integer
    
    'Create Connection object
    Set Conn = New ADODB.Connection
    'Create Recordset object
    Set Rec = New ADODB.Recordset
    
    Conn.Provider = "Microsoft.ACE.OLEDB.12.0"
    Conn.Open "C:\Users\akash\OneDrive\Desktop\Akash\VBA Training\Jayesh\Data.accdb"
    
    '"Excel is table here"
    
    Rec.Open "Excel", Conn, adOpenDynamic, adLockOptimistic
    
    For R = 1 To 52
        Rec.AddNew
        For C = 0 To 6
            Rec.Fields(C).Value = Sheet3.Cells(1, 1).Offset(R, C).Value
        Next C
        Rec.Update
    Next R
    
    
    Conn.Close
    Set Conn = Nothing
    
End Sub


Sub AccessToExcel1()
    
    'Late Binding
    
    'Declare Connection object
    Dim Conn As Object
    'Declare Recordset Object
    Dim Rec As Object
    Dim SQL As String
    Dim C As Integer
    
    'Create Connection object
    Set Conn = CreateObject("ADODB.Connection")
    'Create Recordset object
    Set Rec = CreateObject("ADODB.Recordset")
    
    Conn.Provider = "Microsoft.ACE.OLEDB.12.0"
    Conn.Open "C:\Users\akash\OneDrive\Desktop\Akash\VBA Training\Jayesh\Data.accdb"
    
    SQL = "Select * from Data"
    Rec.Open SQL, Conn, adOpenDynamic, adLockOptimistic
    
    For C = 0 To Rec.Fields.Count - 1
        Sheet2.Range("A1").Offset(0, C).Value = Rec.Fields(C).Name
    Next C
    
    Sheet2.Range("A2").CopyFromRecordset Rec
    
End Sub


Sub ExcelToAccess1()
    
    'Early Binding
    
    'Declare Connection object
    Dim Conn As Object
    'Declare Recordset Object
    Dim Rec As Object
    Dim R As Integer, C As Integer
    
    'Create Connection object
    Set Conn = CreateObject("ADODB.Connection")
    'Create Recordset object
    Set Rec = CreateObject("ADODB.Recordset")
    
    Conn.Provider = "Microsoft.ACE.OLEDB.12.0"
    Conn.Open "C:\Users\akash\OneDrive\Desktop\Akash\VBA Training\Jayesh\Data.accdb"
    
    '"Excel is a table here"
    
    Rec.Open "Excel", Conn, adOpenDynamic, adLockOptimistic
    
    For R = 1 To 52
        Rec.AddNew
        For C = 0 To 6
            Rec.Fields(C).Value = Sheet3.Cells(1, 1).Offset(R, C).Value
        Next C
        Rec.Update
    Next R
    
End Sub

Akash Vishwakarma

Hi This is Akash Vishwakarma. I am working as software developer. I have knowledge in VBA, SQL Server, Python. I have developed this website from Django Framework.