Results 1 to 3 of 3
  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81

    Question Using VBA to update connect string (password) for multiple pass-thru queries

    Hi All,


    I'm trying to determine how to go about programmatically updating the connect string for multiple pass-thru queries in a database - each of which is using 1 of 6 different ODBC data sources. Thus, the password may not always be consistent for each data source (they're all Oracle Databases, but have differing PW expiration intervals/requirements/etc.)

    I assume I'd use something like the below as the basis (i.e.: looping through the QueryDefs in the database for anything containing a "Connect" string)

    Code:
    For Each qdef In CurrentDb.QueryDefs
        
        If qdef.Connect <> "" Then
            
            <Something>
            
        End If
        
    Next
    ...but is there a way to cover each possible ODBC Data Source? Would it just require a series of "IF" statements, i.e.:

    Code:
    If qdef.Connect Like "*DSN=MyDB1*" Then
    
    <Something>
    
    ElseIf qdef.Connect Like "*DSN=MyDB2*" Then
    
    <Something>
    
    etc.
    Example:

    ODBC;DSN=MyDB1;UID=ops$jdoe;PWD=ABC123;DBQ=MyDatabase1;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T ;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM =F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;O DA=F;

    ODBC;DSN=MyDB2;UID=ops$jdoe;PWD=XYZ321;DBQ=MyDatabase2;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T ;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM =F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;O DA=F;

    ODBC;DSN=MyDB3;UID=ops$jdoe;PWD=DEF678;DBQ=MyDatabase3;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T ;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM =F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;O DA=F;

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    For more than maybe 3 IF's I'd use a Select Case block:
    Select Case qdef.Connect
    Case "*DSN=MyDB1*"
    Do this...
    Case ..

    End Select
    That's OK if you're only going to make one connection, because a select block stops processing upon the first true result. If 6 connections, you'll have to use 6 separate IF's - you can't nest them.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81
    Thanks! I'll give that a shot.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-04-2018, 03:44 AM
  2. Replies: 6
    Last Post: 06-20-2016, 01:29 PM
  3. I can not get pass my password
    By danvadis in forum Access
    Replies: 1
    Last Post: 03-19-2016, 08:44 AM
  4. Connect System DSN using connection string
    By jbailey4545 in forum Access
    Replies: 0
    Last Post: 11-08-2013, 01:29 PM
  5. Replies: 0
    Last Post: 03-19-2006, 11:52 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums