How to open Access Database from VBA

[MarkBStewart]

I am getting an "invalid database format" error trying to open an Access database from Access VBA. Here is the 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]

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

[MarkBStewart]

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")

[MarkBStewart]

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

[QVeen72]

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

[MarkBStewart]

Making the new DB Visible

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

[QVeen72]

Hi,

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

[MarkBStewart]

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.

[QVeen72]

Hi,

Check If This Helps You

[MarkBStewart]

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.