Converting from Excel and Access 2003 to Excel and Access 2007

I am trying to convert this Excel Spreadsheet to 2007.  It connected to Access.  So I changed the paths and provider.  Also changed the References to MS Access 12.0 Object Library and MS Excel 12.0 Object Library.
In the Code I changed from mdb to accdb.
I am getting a runtime error 3343 unrecognized database format then it lists the path.  
The path is right.  
In the code , first is the new code and in the second piece I comment out which worked.  
Any help would be appreciated.

Public Sub OpenDB2()
 Dim db As DAO.Database
 Dim ws As DAO.Workspace
 Dim rst As DAO.Recordset
 Dim i As Integer
 
 Cells.Clear
 MsgBox "Update"
 
 Set ws = DBEngine.Workspaces(0)
 Set db = ws.OpenDatabase _
 ("\\XXX\XX\XX_2007.accdb", _
 False, False, "MS Access;PWD=XXXX")
 


' Set ws = DBEngine.Workspaces(0)
' Set db = ws.OpenDatabase _
' ("XXX\XX\XX\XX.mdb", _
' False, False, "MS Access;PWD=XXXXX")
'
'

Open in new window

ca1358Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rogerardConnect With a Mentor Commented:
I found this, it might be related?

What happened was Office 2007 released a new and incompatible version of  Jet named ACE.  You can get the necessary OLE DB provider as well as  ACCDAO by installing the components at 2007 Office System Driver: Data  Connectivity Components  (http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en).
 
 Microsoft goofed and failed to include this in the office 2007  release... or more likely it simply was not ready when Marketing  insisted they push Office 2007 onto store shelves.
 
 The degree of ADO support in ACE is quite similar to that in Jet.
dim acc as access.application
Dim db As DAO.Database
 Dim ws As DAO.Workspace
 Dim rst As DAO.Recordset
 Dim i As Integer
 
 Cells.Clear
 MsgBox "Update"
 set acc as new
 Set ws = DBEngine.Workspaces(0)
 Set db = ws.OpenDatabase _
 ("\\XXX\XX\XX_2007.accdb", _
 False, False, "MS Access;PWD=XXXX")
 


' Set ws = DBEngine.Workspaces(0)
' Set db = ws.OpenDatabase _
' ("XXX\XX\XX\XX.mdb", _
' False, False, "MS Access;PWD=XXXXX")
'
'

Open in new window

0
 
rogerardCommented:
silly question, but did you perform a convert on the old database to the new 2007 format?
0
 
ca1358Author Commented:
Yes
0
 
ca1358Author Commented:
I uncheck in the References
Microsoft DAO 3.6 Object Library
Check
Microsoft Office 12. Access database engine Object and
Microsoft Office 12. Object Library
Microsoft Excel 12. Object Library

Thanks for the input.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.