How to set Application.CurrentProject.Connection i

Question

we are in the process of converting an ADP project into ACCDB as ADP's are no longer supported in Office 2013 version.

I have the below code to change the application connection in ADP :

Function ChangeADPConnection(strServerName As String, strDBName As _
   String, Optional strUN As String, Optional strPW As String) As Boolean
Dim strConnect As String
On Error GoTo EH:
Application.CurrentProject.CloseConnection
'The Provider, Data Source, and Initial Catalog arguments are required.
strConnect = "Provider=SQLOLEDB.1" & _
";Data Source=" & strServerName & _
";Initial Catalog=" & strDBName
If strUN <> "" Then
    strConnect = strConnect & ";user id=" & strUN
    If strPW <> "" Then
        strConnect = strConnect & ";password=" & strPW
    End If
Else  'Try to use integrated security if no username is supplied.
    strConnect = strConnect & ";integrated security=SSPI"
End If
Application.CurrentProject.OpenConnection strConnect
ChangeADPConnection = True
Exit Function
EH:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Connection Error"
ChangeADPConnection = False
End Function

But , I am not sure how to change the current project connection in ACCDB ?

I am using linked tables with SQL Server as backend

Here is what i have tried :

Public Function ChangeACCDBConnection(strServerName As String, strDBName As _
   String, Optional strUN As String, Optional strPW As String) As Boolean

    Dim strConnect As String
    Dim cnn As ADODB.Connection
    Application.CurrentProject.Connection.Close
    On Error GoTo EH:
    strConnect = "XXXXX"
    Set Con = New ADODB.Connection
    Con.ConnectionString = strConnect
    CurrentProject.OpenConnection strConnect // Error here
    ChangeConnection = True
    Exit Function

EH:
    MsgBox Err.Number & ": " & Err.Description, vbCritical, "Connection Error"
    ChangeConnection = False
End Function

I am getting an error ,change the current project connection. The connection string is fine and it is connecting sucessfully to the database.

The only thing is how to set that connection to Application.CurrentProject inorder to use it acrosss the application.

Any help much appreciated..

Answer 1

Your code can continue to use currentProject.connection, BUT YOU CAN NOT CHANGE it.

In effect, currentproject connection will ALWAYS resolve to your current front end database.

However, what this means is that existing code that uses this connection object will continue to work.

So, if you had this:

Dim rs As New ADODB.Recordset

rs.Open ("select * from tblHotels"), CurrentProject.Connection
Do While rs.EOF = False
   Debug.Print rs!FirstName
   rs.MoveNext
Loop
rs.Close
Set rs = Nothing

What occurs behind the scenes is the above resolves to our font end, and then to tblHotels (a linked table to sql server). So, Access will use the CURRENT front end database as the current connection. It resolves to that table, but that table is linked (pointed to) sql server, so then access will use the information in the linked table to resolve this connection for you.

I cannot stress more that the current project connection thus resolves to the local object (linked table). Since that table points to sql server, then access will use the information in the linked table, and you be just fine.

So, at a conceptual level: Continue to use current project. You NEVER change, touch, or edit, or modify the current project connection that access returns.

At a practical level: To point, or change what sql server the sql table resoles to, you have to re-link the tables and point them to the new/different database.

So, in place of changing the whole application connection, you simply have to call your re-link table code. Once you done this re-link, then you can continue to use the current project connection object in your code. So the only real difference here is you can’t (never) change the connection object, but you simply will have re-linked the tables to point to the correct database.

At the end of the day, this sill means you NEVER have to deal with connection strings in code, except for the re-link routines.

Note that you should have your re-link code also change/set any PT query you have.

So, to use a PT query, you can go:

Dim rst     As DAO.Recordset

With CurrentDb.QueryDefs("qryPassR")
  .SQL = "select * from tblBooking where id = " & BookingID
  Set rst = .OpenRecordset()
End With

Keep in mind the above is raw T-SQL being sent to SQL Server.

Or, to call a stored procedures, you can go:

Dim rst     As DAO.Recordset

With CurrentDb.QueryDefs("qryPassR")
  .SQL = "exec MyStoredProc " & BookingID
  .Execute
End With

Note how in above we did not deal with connection strings in code, and we did not even have to deal with a connection object. (nor did we even have to create a connection object to call the SQL Server stored procedure. If the stored procedure is to return records, then of course we would use the first syntax:

Dim rst     As DAO.Recordset

With CurrentDb.QueryDefs("qryPassR")
  .SQL = "exec MyStoredProc " & BookingID

  Set rst = .OpenRecordset()
End With

If you had/have forms based on parameters, then you now bind the form DIRECTLY to the linked table.

To pass (restrict) the forms data set, then you can use this

Docmd.OpenForm "frmBooking",,,"id = " & bookingID

The above will load the form, and ONLY pull the ONE record from sql server despite the form being bound directly to the linked table.

So, parameters to forms (to restrict the record(s) pulled) should use the above “simple” where clause of the open form command.

So, you don't need some new global connection object, but can continue to use currentproject connection.

Answer 2

As far as I know, in a standard Access database application (not an ADP) you can manipulate your connection(s) in only these ways:

1. Delete linked tables and recreate those links using code. If you do this correctly you can link/relink to any valid data source. This has no affect though on any ADO connections. Linked tables actually use a combination of DAO and ODBC as the data access layers.
2. Create an ADO connection object as a global variable in a code module. Write functions to change its connection when you want to switch data sources. (You could also wrap all this in a class.)

I don't think a non-ADP application has a built-in global ADO connection like ADP so you have to use the methods listed above.