VBA Code - Data from Access to excel

[Bigyin]

Hi,

I have searched your forum and looked in Google but cant find what I am looking for. I am not the best with VBA hence why im asking for some help!

what I am looking to do is create code so that i call it to run a query within access to pull data into Excel.

something im looking to do would be a bit of code like below.

Importdata(database1,query1,sheet1,1,1);

Database1 - would be the filename of the database
Query1 - would be the query to run within access
Sheet1 - would be the sheet within excel I want the data to go
1,1 - would be were the data within that sheet the data would go, so this would start from a1.

I would save all the databases within the same folder and all have the same password, so could set a global variable of path where the databases would be saved?

Hope someone can help, and thanks in advance!

[mancubus]

welcome to vbax.

try this.

Sub Import_Access_Table(strDB As String, strQuery As String, strSheet As String)
'requires a reference to Microsoft Activex Data Objects X.X Library
'from Tools - References in VBE

    Dim cnt As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim ws As Worksheet
    Dim FieldNum As Long, i As Integer
    
    cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strDB & ";"
    rst.Open strQuery, cnt
    FieldNum = rst.Fields.Count
    
    Set ws = Worksheets(strSheet)
    With ws
        .Cells.Clear
        For i = 0 To FieldNum - 1
            .Cells(1, i + 1).Value = rst.Fields(i).Name
        Next i
        .Cells(2, 1).CopyFromRecordset rst
    End With
 
End Sub

you can call it like this:

Sub import_test()

    Dim accDb As String, accQuery As String, xlSheet As String
    
    accDb = "C:\Users\Me\Documents\ms_access\my_access_db.accdb"
    accQuery = "SELECT * FROM MyAccessTableName"
    xlSheet = "Sheet1"
    
    Call Import_Access_Table(accDb, accQuery, xlSheet)

End Sub

[mancubus]

maybe...

Public Const lRow As Long = 8
Public Const lCol As Long = 3
Public Const dbPath As String = "C:\Users\Me\Documents\ms_access\"
Public Const sUser As String = "Admin"
Public Const sPass As String = "MyPassword"

Sub Import_Access_Table( _
        strDB As String, _
        strQuery As String, _
        strSheet As String, _
        Optional lngRow As Long = 1, _
        Optional lngCol As Long = 1, _
        Optional strUser As String = "", _
        Optional strPass As String = "")
     'requires a reference to Microsoft Activex Data Objects X.X Library
     'from Tools - References in VBE
     
    Dim cnt As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim FieldNum As Long, i As Integer
     
    cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB & ";User Id=" & strUser & ";Password=" & strPass
    rst.Open strQuery, cnt
    FieldNum = rst.Fields.Count
     
    With Worksheets(strSheet)
        For i = 0 To FieldNum - 1
            .Cells(lngRow, lngCol).Offset(, i).Value = rst.Fields(i).Name
        Next i
        .Cells(lngRow, lngCol).Offset(1).CopyFromRecordset rst
    End With
     
End Sub


Sub import_test()
     
    Dim accDb As String, accQuery As String, xlSheet As String
     
    accDb = dbPath & "eks_pls.accdb"
    accQuery = "SELECT * FROM MyAccessTableName"
    xlSheet = "Sheet1"
     
    Call Import_Access_Table(accDb, accQuery, xlSheet, lRow, lCol, sUser, sPass)
     
End Sub

[snb]

where

c00 is the fullname of the Access file
c01 is the name of the table (or Query) in the database you want to retrieve
Sheet1 is the codename of the sheet you want the data to be imported in.

Sub M_snb()
    c00 = "G:\Access\fiets.mdb"
    c01 = "Q_test"
    
    With Sheet1.ListObjects.Add(0, "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & c00 & """", , , Range("$A$1")).QueryTable
        .CommandType = 3
        .CommandText = c01
        .Refresh False
    End With
End Sub