connect to Access 2007

[Dave B]

In 2005, thanks to code posted on this board, I created a workbook
which connects to Access & retrieves data from recordsets. The code
works fine, except now when I try to use it with Access 2007 I get the
error:
"Unrecognized database format 'C:\...\myfile.accdb'

Is there a way to update the code so it will work with Access 2007?
Thanks in advance for any help! Here is the relevant section:

Public cnMinistry As ADODB.Connection
Private Function ConnectToDatabase() As Boolean
'instantiate the connection & connect
Set cnMinistry = New Connection
cnMinistry.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" & strFileToOpen & ";"
cnMinistry.Open '<--- error happens here
If cnMinistry.State = adStateOpen Then ConnectToDatabase = True
End Function

References:
Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft.ActiveX Data Objects 2.5 Library

Some more code that may have to change:

Private Sub ImportAccessTable(cn As ADODB.Connection, TableName As
String, TargetRange As Range)
Dim rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
Set rs = New ADODB.Recordset
rs.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value =
rs.Fields(intColIndex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data
rs.Close
Set rs = Nothing
End Sub

 

[Cringing Dragon]

I don't know if it's related to the problem or not, but you may need to
change your references to point to the Excel 12 and Office 12 libraries.
Those libraries may be available on PCs which have been upgraded from Office
2003 to Office 2007, but will probably not be available on computers with
only Office 2007.

To check your connection string, try the following:
In Excel, start recording a macro.
Make a manual connection to the database (I use Excel 2003, so the menu may
be different, but something like Data - Import External Data - New Database
Query). Go through the steps to create a query, but it doesn't matter what
the query is as we're only checking the connection string, so just choose any
field from an appropriate table.
Once the data has been returned to Excel, stop the macro recording and look
at what's been recorded. Does the connection string match what you have in
your macro?
I'm guessing that if the database has been upgraded to Access 2007, then the
provider needs to change.

 

[Tom Ogilvy]

try using

Provider=Microsoft.Jet.OLEDB.12.0

You might read through these article:

http://support.microsoft.com/kb/247412
http://support.microsoft.com/kb/246335

Also check the name of your access file extension. MDB might be something
like ACDB

 

[urkec]

try using

Provider=Microsoft.Jet.OLEDB.12.0

Also check the name of your access file extension. MDB might be something
like ACDB

This connection string worked for me:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myDatabase.accdb;"

Hope this helps.