VBA - Connect Excel 2007 Spreadsheet to Access 2007 Database via ADODB

Question

Currently there is a Excel 2002 spreadsheet writing/retrieving data from a Access 2002 database. IT is presently researching a upgrade to Office 2007 and when I upgrade both the spreadsheet and database the ADODB connection fails.

The 2002 era VBA connection string is:

Provider=Microsoft.Jet.OLEDB.4.0;
Jet OLEDB:Database Password = stackoverflow; 
Data Source= \\test\test.mdb"

In usage:

Dim cnt As ADODB.Connection
Set cnt = New ADODB.Connection
cnt.Open LiveCon

Changing the .mdb suffix to .accdb breaks the connection. Can someone direct me to the new syntax for the connection string?

Answer 1

You may find this helpful: http://www.connectionstrings.com/access-2007

Answer 2

Try changing

Provider=Microsoft.Jet.OLEDB.4.0

to

Provider=Microsoft.ACE.OLEDB.12.0