Copy Access Query to Excel via VBA (error 3343)

Question

I'm attempting to copy the results of an access query and paste it into an excel tab. I've googled around but can't seem to get it to work, I get the error "Error 3343: Unrecognized database format" so I assume it has something to do with the references I have checked.

Does anyone know the correct references I need to get this to work?

References:

Visual Basic For Application

Microsoft Excel 14.0 Object Library

OLE Automation

Microsoft Office 14.0 Object Library

Microsoft ActiveX Data Objects 2.8 Library

Microsft DAO 3.6 Object Library

Sub Query()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim iCol As Integer

Sheets("DataDump1").Select
With Selection.ClearContents

End With
Set db = OpenDatabase("C:\Folder\DatabaseName.accdb")
Set rst = db.OpenRecordset("Query 1")

For iCol = 1 To rst.Fields.Count
 ActiveSheet.Cells(1, iCol) = rst.Fields(iCol - 1).Name
 Next iCol

ActiveSheet.Range("A2").CopyFromRecordset rst
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

End Sub

Parfait · Accepted Answer · 2016-07-04 15:24:18Z

1

Consider calling the Access Object before initializing the Database and Recordset objects. Also, use the OpenCurrentDatabase method as OpenDatabase is for the DBEngine Workspace object.

Sub Query()
    Dim accObj As Object
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim sql As String
    Dim iCol As Integer

    Sheets("DataDump1").Cells.ClearContents

    Set accObj = CreateObject("Access.Application")
    accObj.OpenCurrentDatabase("C:\Folder\DatabaseName.accdb")

    Set db = accObj.CurrentDb
    Set rst = db.OpenRecordset("Query 1")

    For iCol = 1 To rst.Fields.Count
        Sheets("DataDump1").Cells(1, iCol) = rst.Fields(iCol - 1).Name
    Next iCol

    Sheets("DataDump1").Range("A2").CopyFromRecordset rst
    rst.Close
    db.Close

    Set rst = Nothing
    Set db = Nothing
    Set accObj = Nothing

End Sub

Alternatively, no need to interface with the Access object as Access is a database not just an .exe so can be connected via ODBC/OLEDB like any other RDMS (Oracle, SQL Server, MySQL, etc.)

Sub RunSQL()
    Dim conn As Object, rst As Object
    Dim strConnection As String, strSQL As String
    Dim iCol As Integer

    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

    Sheets("DataDump1").Cells.ClearContents

'    strConnection = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};" _
'                      & "DBQ=C:\Folder\DatabaseName.accdb;"
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                       & "Data Source='C:\Folder\DatabaseName.accdb';"

    strSQL = " SELECT * FROM [Query 1];"

    ' OPEN DB AND RECORDSET
    conn.Open strConnection
    rst.Open strSQL, conn

    ' COLUMN HEADERS
    For iCol = 1 To rst.Fields.Count
        Sheets("DataDump1").Cells(1, iCol) = rst.Fields(iCol - 1).Name
    Next iCol

    ' DATA ROWS
    Sheets("DataDump1").Range("A2").CopyFromRecordset rst

    rst.Close
    conn.Close    
End Sub

answered Jul 4, 2016 at 15:24

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JBlack Over a year ago

Thank you, your second solution worked perfectly for me.

Parfait Over a year ago

Great! And do note, second option does not need MSAccess.exe installed on the user's machine. Just have the .accdb file and a PC (which should have the Ace/Jet engine -Windows .dll files) installed.

Hello World · Accepted Answer · 2016-07-04 15:21:49Z

0

I think a references issue would give user defined type not recognised error. ADODB rather than DAO should work:

Sub Query()
Dim db As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim iCol As Integer

db.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Folder\DatabaseName.accdb;"
rst.Open "Query 1", db

For iCol = 1 To rst.Fields.Count
 ActiveSheet.Cells(1, iCol) = rst.Fields(iCol - 1).Name
 Next iCol

ActiveSheet.Range("A2").CopyFromRecordset rst
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

End Sub

EDIT: Please add latest microsoft activex data objects library as a reference for this to work

answered Jul 4, 2016 at 15:21

Hello World

3085 silver badges20 bronze badges

Collectives™ on Stack Overflow

Copy Access Query to Excel via VBA (error 3343)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related