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)
...but is there a way to cover each possible ODBC Data Source? Would it just require a series of "IF" statements, i.e.:Code:For Each qdef In CurrentDb.QueryDefs If qdef.Connect <> "" Then <Something> End If Next
Example:Code:If qdef.Connect Like "*DSN=MyDB1*" Then <Something> ElseIf qdef.Connect Like "*DSN=MyDB2*" Then <Something> etc.
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;