locked
Using VB6 to open Access 2007 (*.accdb) database

    Question

  • Hi,


     background

    A.     A Visual Basic program is developed to read a text file to Access 2003 database using the Data Access Objects (DAO). The following is the DAO command:

    Set db = DAO.DBEngine(0).OpenDatabase("C:\Temp\MyAccess2003.mdb")

    B.     The office Access 2003 database (*.mdb) is going to be upgraded to Access 2007 database (*.accdb).

    C.     The Visual Basic program is revised to read the same text file to Access 2007 database using the same Data Access Objects (DAO), The following is the DAO command:

    Set db = DAO.DBEngine(0).OpenDatabase("C:\Temp\MyAccess2007.accdb")

    D.     The following error message occurs when the revised Visual Basic program is executed:

    “Run-time error ‘3343’: Unrecognized database format”

    I had previously used VB6; I am now using VS2008; What is the best manner to now open the database using the VB code?  The code works with the Access 2003 database.  Any help appreciated.

    Thanks.

    Tuesday, April 14, 2009 6:04 AM

Answers

All replies

  • Hi,

    you can try the code below:

    Dim myOleDbConnection As New OleDb.OleDbConnection
    
    ' Set your Database location here
    
    myOleDbConnection.ConnectionString = _
    
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\temp\SQL Server 2000 Sample Databases\Nwind.mdb"""
    
    
    
    Try
    
        myOleDbConnection.Open()
    
    Catch ex As Exception
    
        MessageBox.Show(ex.Message, _
    
            "Error while opening the database.")
    
    End Try
    
    
    
    Dim myOleDbSelectCommand As New OleDb.OleDbCommand
    
    myOleDbSelectCommand.Connection = myOleDbConnection
    
    myOleDbSelectCommand.CommandText = "select * from customers"
    
    
    
    
    
    Dim myOleDbDataAdapter As New OleDb.OleDbDataAdapter
    
    myOleDbDataAdapter.SelectCommand = myOleDbSelectCommand
    
    
    
    
    
    Dim myDataSet As New DataSet
    
    myOleDbDataAdapter.Fill(myDataSet, "Customers")
    
    
    
    
    
    

    Mark the thread as answered if the answer helps you. This helps others who have the same problem !
    • Edited by Heslacher Tuesday, April 14, 2009 6:34 AM Changing to the right Provider
    Tuesday, April 14, 2009 6:28 AM
  • ...What is the best manner to now open the database using the VB code?  The code works with the Access 2003 database.  Any help appreciated.

    Thanks.


    Use the Data Sources window to add a new OleDB data source which points to the access database.

    Expand the database in the Data Sources window.

    Drag the desired table(s) onto the Form.

    If you used an upgrade wizard, then you need to go to the Interop and Upgrade forum as ask the question there.

    Personally, I would suggest throwing away the VB6 code and starting fresh in Visual Studio 2008.
    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"
    Tuesday, April 14, 2009 2:28 PM
    Moderator
  • Hello,

    Access 2007 use a different data base format. You need to download the AccessDatabaseEngine and install it on your target machine.
    http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

    And then,
    a) Deselect Microsoft DAO 3.6 object library
    b) Select Microsoft Office 12 access database engine object library

    Please also note that, this forum is for Visual Basic.NET (7, 7.1, 8, ...) issues only, so, your Visual Basic 6 question is an off-topic here and this isn't the best place to ask it, here is a list of forums, newsgroups, resources and sites, to get an answer about your question :

    Remember, you can use Visual Basic 2005 Express (.NET) for free, because it's free-to-download, free-to-register, easy-to-use, easy-to-learn and funny!, why you don't want to take a tour about it?


    We have published a VSTO FAQ recently, you can view them from the entry thread http://social.msdn.microsoft.com/Forums/en/vsto/thread/31b1ffbf-117b-4e8f-ad38-71614437df59. If you have any feedbacks or suggestions on this FAQ, please feel free to write us emails to colbertz@microsoft.com.
    • Marked as answer by Ji.ZhouModerator Monday, April 20, 2009 5:07 AM
    • Unmarked as answer by fjaconda199 Monday, April 20, 2009 11:01 AM
    • Marked as answer by Ji.ZhouModerator Tuesday, April 21, 2009 12:35 AM
    • Unmarked as answer by fjaconda199 Friday, June 12, 2009 2:56 PM
    • Marked as answer by fjaconda199 Wednesday, June 24, 2009 3:57 AM
    Thursday, April 16, 2009 8:08 AM
    Moderator
  • Thank U , you Gave me what i need
    Thursday, October 20, 2011 5:33 PM
  • THanks Dude this was helpful
    Wednesday, March 14, 2012 5:36 AM
  • I tested the provider string and it works in ASP Classic pages:

    Set Conn = Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.CursorLocation = adUseClient
    Conn.open "Provider=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=D:\Data\html\ASP\data\Issues.accdb"
    strSQL = "SELECT * FROM Contacts"
    rs.open strSQL, Conn, 3, 3

    My environment is:

    Server: Microsoft-IIS/7.5
    X-Powered-By: ASP.NET

    Monday, December 03, 2012 6:19 AM