Results 1 to 3 of 3
  1. #1
    Glenn_Suggs is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    72

    Cool Programmatically changing login credentials on ODBC connections

    Hello all... I work with a backend server that is connected via ODBC. We use an Access front end and an Oracle backend. I have a need to refresh the links to all backend tables since the login credentials will be changing often. I'd like to have each application access a common area on the network (whenever a user runs it) where it can pick up the User ID and Password that are currently in effect. This should ensure that the app will log in to the backend data and hopefully the process will be invisible to the end user. I can set that part up. The problem is that I'm not sure how to go about using VBA to programmatically change the User ID and Password once I have the correct variables loaded with the User ID and Password. Is there a special object library or something like that that I should reference in order to change the properties of the ODBC connection? If so, what library would that be? Any help is greatly appreciated.



    Thanks in advance...
    Glenn

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    something like:

    Code:
    'show linked table paths
    Public Sub ReLinkTbls()
    Dim vTbl, vPath, vTName, vDbTbl, vFile, vMdb, vLocalDir
    Dim db As Database
    Dim tdf As TableDef
    On Error Resume Next
    Set db = CurrentDb
    sConn = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD"
    For Each tdf In db.TableDefs
       If tdf.Connect <> "" Then tdf.Connect = sConn
    Next
    Beep
    endit:
    Set tdf = Nothing
    Set db = Nothing
    End Sub

  3. #3
    Glenn_Suggs is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    72
    Thanks for the response, ranman. Is this DAO? Looks like it. What kind of variable is sConn in this code? Is it DAO.Connection?

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

Similar Threads

  1. ODBC connections to avoid bandwidth bottleneck on split database
    By notableaudio in forum Database Design
    Replies: 2
    Last Post: 12-18-2015, 10:09 AM
  2. ODBC Automatic login
    By Metrazal in forum Macros
    Replies: 3
    Last Post: 10-09-2014, 03:08 PM
  3. Tracking ODBC Connections (Access 2010)
    By JeffGeorge in forum Access
    Replies: 9
    Last Post: 08-01-2014, 03:25 PM
  4. Changing ODBC Linked Table Stored Credentials
    By JeffGeorge in forum Access
    Replies: 1
    Last Post: 08-09-2013, 01:53 PM
  5. Replies: 8
    Last Post: 03-16-2012, 11:04 AM

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