[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Converting from Excel and Access 2003 to Excel and Access 2007

Posted on 2010-11-10
4
Medium Priority
?
521 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:ca1358
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:rogerard
ID: 34105027
silly question, but did you perform a convert on the old database to the new 2007 format?
0
 

Author Comment

by:ca1358
ID: 34105046
Yes
0
 
LVL 7

Accepted Solution

by:
rogerard earned 2000 total points
ID: 34105369
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
 

Author Closing Comment

by:ca1358
ID: 34105951
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question