Attempt to connect to a valid database from outside Access (Outlook/Excel) using DAO generates a 3343 unrecognized database format error

Question

Thanks for your site. Wonderful information.

In a nutshell, I'm trying to execute the following code from Outlook (2007), although it fails in Excel as well. Works great INSIDE Access!

Sub Test

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Const dbPath As String = "C:\Users\e574651.GLOBAL\Documents\Northwind 2007.accdb"
    On Error Resume Next
    Set db = DAO.OpenDatabase(dbPath)
    'Set rs = db.OpenRecordset("customers")

    Debug.Print Err.Number, Err.Description

End Sub

3343 Unrecognized database format 'C:\Users\e574651.GLOBAL\Documents\Northwind 2007.accdb'.

I can access (no pun intended) this database all day long using ADO, and I suspect the problem lies with the following ADO statement:

ADOConn.Provider = "Microsoft.ACE.OLEDB.12.0"

How do I provide this functionality using DAO?

I have included a reference to the DAO 3.6 library in my VBA preferences. I've included the other Microsoft 12.0 library references, so I've either clobbered something or omitted something.

Any assistance will be greatly appreciated.

Thanks!

Answer 1

The most recent DAO libraries are in the format :

Microsoft Office x.x Access Database Engine Object Library

So get rid of the 3.6 reference and use a more recent library. Then, an example:

Sub XLAccess()
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim sDb As String
Dim sSQL As String
Dim qdf As QueryDef

    sDb = "Z:\Docs\Test.accdb"

    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(sDb)

    ''A stored query would be better
    sSQL = "Parameters p1 Text, p2 Datetime; " _
    & "INSERT INTO Table1 (AText,ADate) Values ([p1],[p2])"

    Set qdf = db.CreateQueryDef("", sSQL)

    qdf.Parameters!p1 = "ABC"
    qdf.Parameters!p2 = #1/17/2013#
    qdf.Execute dbFailOnError
    Debug.Print qdf.RecordsAffected
End Sub

Answer 2

Use a more recent version or the latest access database on your references.

For example: Inside your Visual Basics Window = Go to Tools > References > Microsoft Office 14.0 Access Database Engine Object Library.

Then use the following to open your database:

Dim database_data As DAO.Database
Dim rsData As DAO.Recordset
Dim field_index As Integer

Set database_data = DAO.OpenDatabase("demo1.accdb")

At times, it might be necessary too type out the full path to your database file, e.g. "C:\User\Documents\projects\demo1.accdb"