Change connection settings when using pass-through request

Question

I have an Access 2003 application that I would like to create reports for using stored procedures through end-to-end queries. Except for one, everything works fine. When I specify a stored procedure to use for end-to-end query, I need to select a DSN to provide database connection information. I need to be able to change connection information for a stored procedure used in a pass-through request using code . This means that I can switch to the development, production, testing environment from the application.

Currently, all my data access (additions, updates, changes) is using ADO, and I am building connection strings through VBA code. I am not sure how to change the end-to-end query connection information through code. Any suggestions? Thank.

Answer 1

Look at the Connect property of your end-to-end request. You can change the connection to VBA. This procedure switches between prod and dev connections.

Public Sub SwitchPassThroughConnection(ByVal pQuery As String, ByVal pTarget As String)
    Dim db As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim strConnect As String
    Dim blnError As Boolean

    Set db = CurrentDb()

    Select Case pTarget
    Case "dev"
        strConnect = "ODBC;DRIVER={PostgreSQL Unicode};DATABASE=dev;SERVER=cmpq;" & _
            "PORT=5432;UID=hans;PWD=changeme;CA=d;A6=;A7=100;A8=4096;" & _
            "B0=255;B1=8190;BI=0;C2=dd_;CX=1b502bb;A1=7.4"
    Case "prod"
        strConnect = "ODBC;DRIVER={PostgreSQL Unicode};DATABASE=prod;SERVER=cmpq;" & _
            "PORT=5432;UID=hans;PWD=changeme;CA=d;A6=;A7=100;A8=4096;" & _
            "B0=255;B1=8190;BI=0;C2=dd_;CX=1b502bb;A1=7.4"
    Case Else
        blnError = True
        MsgBox "Unrecognized target."
    End Select

    If Not blnError Then
        Set qdef = db.QueryDefs(pQuery)
        qdef.Connect = strConnect
        qdef.Close
    End If
    Set qdef = Nothing
    Set db = Nothing
End Sub