Solved

Converting from Excel and Access 2003 to Excel and Access 2007

Posted on 2010-11-10
4
516 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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.

632 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