Changing connection params when using pass-through query

Question

I have an Access 2003 application that I would like to create reports for using stored procedures via pass-through queries. Everything works fine with one exception. When I specify the stored procedure to use for the pass-through query I have to choose a DSN to provide the database connection info. I need to be able to change the connection info for the stored procedure used in the pass-through query via code. This is so I can switch to development, production, testing environments from within the application.

Currently all of my data access (additions, updates, edits) uses ADO and I build the connection strings via VBA code. I am unsure how how to change the connection info of the pass-through queries via code. Any thoughts? Thank you.

Answer 1

Look at the Connect property of your pass-through query. You can change Connect with VBA. This procedure switches between prod and dev database 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