ADO OpenConnection to mdb file

[jwkeene]

I am trying to establish an ADO connection from an Access (2003) project to an Access (2003) mdb datbase as the 'base' connection - that is, by using the OpenConnection method of the CurrentProject.Connection

I have no difficulty establishing a connection to the mdb if I create a connection object within code. I have no difficulty using the OpenConnection method to create a connection to a SQL database. But every time (using all of the combinations for ConnectionString that I can think of) I try to use the OpenConnection with an mdb file as the target I get the error:

Method 'OpenConnection' of object '_CurrentProject failed - -2147467259(80004005)

Has anybody actually been able to do what I am trying to do? If so, can you show me the connection string that worked for you?

 

[PHV]

AFAIK an adp is designed for working with an SQLserver BE, dot.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jwkeene]

Yeah, I know. But circumstances beyond my control are calling for me to be able to support the same data structure (tables) in either an mbd format or a SQL layout. So it's not a case of 'I wanna', more a case of 'I gotta'...

 

[PHV]

I have no difficulty establishing a connection to the mdb
So, where is the problem ?
You know that you can't use the OpenConnection method but creating a connection object within code works.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

I don't think you can change CurrentProject.Connection to point to another database. According to the VBA Help:

The Connection property actually returns a reference to a copy of the ActiveX Data Object (ADO) connection for the active database. Thus, applying the Close method or in anyway attempting to alter the connection through the Connection object’s methods or properties will have no affect on the actual connection object used by Microsoft Access to hold a live connection to the current database.

However, what you could do is change the connection of all the tables that the front-end MDB has links to:

Public Sub ChangeSQLConnection(NewConnection As String)
    Dim dbs As DAO.Database
    Dim tdf As TableDef
    
    Set dbs = CurrentDb
    
    For Each tdf In dbs.TableDefs
        'Only change tables linked to SQL server
        If Left(tdf.SourceTableName, 4) = "dbo." And Left(tdf.Name, 4) <> "MSys" And _
         tdf.Connect <> "" Then
            tdf.Connect = NewConnection
            tdf.RefreshLink
        End If
    Next
    
    Set tdf = Nothing
    Set dbs = Nothing
    
    MsgBox "Finished Relinking!", vbInformation, "Finished"
End Sub

Of course, if your front-end is actually an ADP (Access Data Project) I have my doubts that it would work. And for sure it wouldn't work with an MDB back-end because ADP's are exclusively for SQL Server.

 

[danvlas]

What are you actually trying to do?
If you need to get a recordset from an mdb file, just create a new ADODB.Connection and open a recordset.
Like below (change the database path & name in the connection string.

Sub TestIt()

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database.mdb;User Id=admin;Password=;"
conn.Open
rst.ActiveConnection = conn
rst.Source = "Select * from tblStock"
rst.CursorLocation = adUseClient
rst.Open
rst.MoveLast
MsgBox rst.RecordCount
rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing

End Sub

HTH


Daniel Vlas
Systems Consultant