Pass through query odbc connection

[mischa]

I made multiple pass through queries that receive their data via an ODBC link which is established. Currently the username and password is entered seperately in every query but I was wondering if it is possible (and how) to make both look for the values in a table (OR somewhere else if possible) for these values.

The following code (with edited information) is used in all queries.

ODBC;DSN=DSNNAME;Driver=Firebird/InterBase(r) driver;Dbname=Database.FDB;CHARSET=NONE;PWD=Password;UID=User;

The reason that I would like to do this is to ensure that the user can change it easily.

 

[namliam]

Not that easily no, you can write some VBA code to change the ODBC connection/user/pw for you from say a table or user input... But that is as far as you can go.

 

[mischa]

Not that easily no, you can write some VBA code to change the ODBC connection/user/pw for you from say a table or user input... But that is as far as you can go.

Do you have any suggestions on how I could build this code?

 

[namliam]

I have a form with a progress meter kinda thing on it with 2 rectangles... which holds this code:

    Dim slash As Integer, LinkedFolder As String, CurrentFolder As String, LinkedDB As String
    Dim tbl As TableDef
    Dim x As Long, MaxX As Long
    Dim tblDB As String
    Me.Visible = False
    LinkedDB = CurrentDb.TableDefs("Import").Connect
    slash = InStrRev(LinkedDB, "\") + 1
    LinkedDB = Mid(LinkedDB, slash)
    CurrentFolder = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
    tblDB = CurrentFolder & LinkedDB
    If Dir(tblDB) = "" Then
        MsgBox tblDB & " is niet gevonden. " & vbCr & vbCr & _
                "De applicatie is niet operationeel en word afgeloten", vbCritical
        Application.Quit
    End If
                    
    MaxX = 1 ' first count all attached tables
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then MaxX = MaxX + 1
    Next tbl
    x = 1 ' Now update them
    
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then
            slash = InStrRev(tbl.Connect, "\") + 1
            LinkedFolder = Mid(tbl.Connect, 11, slash - 11)
            LinkedDB = Mid(tbl.Connect, slash)
'            Stop
'            Debug.Print CurrentFolder, LinkedFolder, LinkedDB
            If tbl.Attributes = dbAttachedTable _
                    And LinkedFolder <> CurrentFolder Then
                Me.Visible = True
                Me.Repaint
                tbl.Connect = ";Database=" & CurrentFolder & LinkedDB
                tbl.RefreshLink
            End If
            x = x + 1
            Me.Fill.Width = x / MaxX * Me.FillTo.Width
        End If
    Next tbl
    If Me.Visible Then
        Me.lblWait.Caption = "Done relinking ... "
        Me.Repaint
        MaxX = Timer + 2
        Do While Timer <= MaxX
        Loop
    End If
    DoCmd.OpenForm "Main Form"
    DoCmd.Close acForm, Me.Name

Which relinks tables, you can probably re-hash it to work with ODBC connections instead....