How to open Access Database from VBA

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • MarkBStewart
    New Member
    • May 2010
    • 20

    How to open Access Database from VBA

    I am getting an "invalid database format" error trying to open an Access database from Access VBA. Here is the code:

    Code:
      Dim MyConn As ADODB.Connection
        Dim MyRecSet As ADODB.Recordset
        Dim strMake As String
    
        Set MyConn = New ADODB.Connection
        MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\Tools\Test_DB.accdb';"
        MyConn.Open
    Any ideas? Thx
  • QVeen72
    Recognized Expert Top Contributor
    • Oct 2006
    • 1445

    #2
    Hi,

    Connectionstrin g for .accdb is different..

    ConnStr = "Provider=Micro soft.ACE.OLEDB. 12.0;" _
    & " Data Source='c:\Tool s\Test_DB.accdb ;Persist Security Info=False;"

    Check This

    Regards
    Veena

    Comment

    • MarkBStewart
      New Member
      • May 2010
      • 20

      #3
      Is this what you mean?

      Is this is the proper code then:

      Dim MyConn As ADODB.Connectio n
      Dim MyRecSet As ADODB.Recordset
      Dim strMake As String

      Set MyConn = New ADODB.Connectio n
      MyConn.ConnStr = "Provider=Micro soft.ACE.OLEDB. 12.0;" _
      & " Data Source='c:\Tool s\Test_DB.accdb ;Persist Security Info=False;"

      MyConn.Open
      MsgBox ("DB4")

      Comment

      • MarkBStewart
        New Member
        • May 2010
        • 20

        #4
        Next Try VBA Open

        This is the code I have now, but I am getting a format error:

        Dim MyConn As ADODB.Connectio n
        Dim MyRecSet As ADODB.Recordset
        Dim strMake As String

        Set MyConn = New ADODB.Connectio n
        MyConn.Connecti onString = "Provider=Micro soft.ACE.OLEDB. 12.0; Data Source='c:\Tool s\Test_DB.accdb ;Persist Security Info=False;"

        MyConn.Open
        MsgBox ("DB4")

        Error is occuring on MyConn.Open - Error: "format of initialization string does not conform to OLE DB specification".

        Where am I going wrong? Thx
        Last edited by MarkBStewart; May 26 '10, 01:48 PM. Reason: Spelling errors

        Comment

        • QVeen72
          Recognized Expert Top Contributor
          • Oct 2006
          • 1445

          #5
          Hi,

          there is single quote after Data Source... remove that...

          MyConn.Connecti onString = "Provider=Micro soft.ACE.OLEDB. 12.0; Data Source=c:\Tools \Test_DB.accdb; Persist Security Info=False;"

          Regards
          Veena

          Comment

          • MarkBStewart
            New Member
            • May 2010
            • 20

            #6
            Making the new DB Visible

            Thank you removing the ' worked. I think. How do I make the new DB visible?

            Comment

            • QVeen72
              Recognized Expert Top Contributor
              • Oct 2006
              • 1445

              #7
              Hi,

              What do you mean by 'How do I make the new DB visible"...?

              Comment

              • MarkBStewart
                New Member
                • May 2010
                • 20

                #8
                I want to spawn another version/copy of Access as If I manually double-clicked on the 2nd copy of Access. Does that make it clear? What other info can I provide? thank you for your help.

                Comment

                • QVeen72
                  Recognized Expert Top Contributor
                  • Oct 2006
                  • 1445

                  #9
                  Hi,

                  Check If This Helps You

                  Comment

                  • MarkBStewart
                    New Member
                    • May 2010
                    • 20

                    #10
                    I am concered about this approach as it is for Access 2000. I am running 2007. This appears to be just what I need, but likely too far down-level.

                    Comment

                    Working...