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

    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)

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

    If qdef.Connect Like "*DSN=MyDB1*" Then
    ElseIf qdef.Connect Like "*DSN=MyDB2*" Then




  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Ontario, Canada
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  3. #3
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    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