Current project

[saif009972]

Hello guys can u help me how to open table from another database. For clarity I am giving example below.

MyRS.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

This code will look for table from current project. But If I want to open one query in current project but from the table that resides on differnt microsoft access project or file then what will be the code in place of

CurrentProject.Connection

Please help me by mentioning one example.

 

[ezfriend]

Put this in a module and call it when you need to open the database.
You need to add the Microsoft ActiveX Data Objects to your ferences.

Option Explicit

Public gdb As ADODB.Connection

Public Sub OpenDatabaseConnection()

    Dim sConnectionString As String
    
    Set gdb = New ADODB.Connection
    
    'MS ACCESS BE CONNECTION
    sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\db\datasource_be.mdb;"

    gdb.ConnectionString = sConnectionString
    gdb.Open
    
End Sub

Public Sub CloseDatabaseConnection()
    
    If gdb Is Nothing = False Then
        gdb.Close
    End If
    
    Set gdb = Nothing
    
End Sub

This is how to call the subs.

Private Sub DoThis()

   Dim rs as ADODB.Recordset
   Dim sSQL as String

   On Error GoTo ErrorHandler
   
   'connect to the database
   OpenDatabaseConnection

   sSQL = "SELECT * FROM tblA "

   Set rs = gdb.Execute(sSQL, , adCmdText)

   Do While rs.EOF = False

        'do whatever

        rs.MoveNext
   Loop

   rs.Close
   Set rs = Nothing
   
   'close the connection since you are done with the database
   CloseDatabaseConnection

   Exit Sub

ErrorHandler:
    msgbox err.Number & ": " & err.Description
    'close connection if error occurred
    CloseDatabaseConnection

End Sub

 

[saif009972]

Thankyou very much. Can u please explain why adcmdtext is required.
My 2nd query is...if my desired destinated accessdatabase is already open by someone then can I open that database with this code? If not then how can I open it