Solved

Converting from Excel and Access 2003 to Excel and Access 2007

Posted on 2010-11-10
4
508 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 500 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now