Exporting data from Access 2007 to Excel 2007 using VBA

[mrblack85]

Hi, i am automating data transfer from Access 2007 to Excel 2007 and am having trouble with setting up the connection.

below is what i have so far but i am having no luck with connecting to the data base via the connection string an defining my record set.

i am also having trouble with figuring out how to paste the data selected from the table over to excel.

Any ideas????

Dim strMyDB As String
Dim conMyDB As ADODB.Connection
Dim rstMyDB As ADODB.Recordset
Dim strQuery As String

strMyDB = "C:\Users\Shadow\Documents\University\Year 4 Information systems\Project\DataBase.accdb;"
Set conMyDB = New ADODB.Connection
conMyDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & strMyDB & "';" _
& "ACE.OLEDB"
Set rstMyDB = New ADODB.Recordset
strQuery = "SELECT * FROM Table2"

 

[Derek Brown]

Hello and welcome to The Board.
There may be some errors in the following - I have just done a quick bit of copy/paste to put it together:

Sub MyProc()
Dim wb As Workbook
Dim ws As Worksheet
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strDB_Path As String
Dim strSQL As String
Set wb = ThisWorkbook
Set ws = wb.Worksheets("My Sheet")
strDB_Path = "C:\MyDatabases"
'
ws.Activate
            '
            strSQL = "SELECT * FROM MyDatabaseTable;"
            Set objConn = New ADODB.Connection
            objConn.Provider = "Microsoft.ACE.OLEDB.12.0"
            objConn.Open strDB_Path
            If objConn.State = adStateOpen Then
                Set objRS = New ADODB.Recordset
                objRS.Open strSQL, objConn, adOpenForwardOnly
                '
                On Error Resume Next
                objRS.MoveFirst
                If Err.Number = 0 Then
                    On Error GoTo 0
                    ws.Cells(2, 1).CopyFromRecordset objRS
                End If
            End If
On Error Resume Next
    objRS.Close
    Set objRS = Nothing
    objConn.Close
    Set objConn = Nothing
    Set ws = Nothing
    Set wb = Nothing
End Sub

Hopefully you can adapt this for your needs.

 

[mrblack85]

Thank you so much, this works a treat.