Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Converting from Excel and Access 2003 to Excel and Access 2007

Posted on 2010-11-10
4
Medium Priority
?
519 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

824 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