Results 1 to 3 of 3
  1. #1
    JohnK is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2022
    Posts
    2

    I keep getting asked to select ODBC DSN multiple times

    After opening a DB, Access keeps asking me to select an ODBC DSN, and to the to provide credentials.
    The DB was created by someone else, but I am modifying it.
    Before my last modification, the DB App asked you to select an ODBC DSN only once, immediately after opening it (it has an Autoexec Form).


    After my last modification (I added a pass through query), it asks multiple times.
    I created the pass through query to get around the 255 column limit.
    The DB contains two links to two SQL tables, and now a new pass through query to one of the two linked tables.
    I've created an ODBC connection definition that uses Windows NT Authentication to make it easier for the users of the app.

    Does anyone know how I can get it to only ask for the ODBC one time when you start the App? Or better yet, only ask for it once, then use the same ODBC DSN every time after that.

    Any help / insight to my problem would be greatly appreciated.

    By the way, if anyone thinks that this post belongs in another Forum, please let me know.

    JohnK
    Last edited by JohnK; 10-11-2022 at 02:57 PM.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Remove the DSN ,then add it back with SAVE CREDENTIALS, so it won’t keep asking.

    relink will not ask any more.
    you can always ask for creds at main menu launch. (Using VB, tho has nothing to do with DSN.)

  3. #3
    JohnK is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2022
    Posts
    2

    Post Solution: I keep getting asked ODBC DSN multiple times

    Thanks for your replies.
    In digging through my application, I found two areas that resolved my problems that involve defining the Connection String.
    When creating Pass Through Queries, in Design Mode, you can look at the property sheet for the query. There is property called "ODBC Connect Str".
    The connect string allows you to specify which DSN to use and other connection information. You can identify Login credentials, and save them as part of the connection if so desired.
    The connection string that I used for my connection was: "ODBC;DSN=MyDB_ODBC_Windows_Auth;Description=O DBC To MyDB for Windows Authentication;Trusted_Connection=Yes;DATABASE=MyD B;"
    The DSN that I set up uses Windows Authorization. In MyDB, I authorize specific access (Select, Update, Delete ... permissions) to objects needed for the Access DB.
    Generally, I use AD Security Groups specific to the Access App, and add users to the Security Group. When I grant permissions to the objects (Tables, Views...) in MyDB, I grant permissions to the Security Group.
    This allows me to maintain security access to the MS Access app by just maintaining the AD Security Group members.
    This approach forces the Access DB app to use the DSN that I want it to use (it no longer asks the user to choose the DSN), and since it is a Windows Authenticated connection, it does not ask for a password. The app just opens up.
    I also used the same approach for the Linked Tables that I set up in the app. In this case, if you right-click on the Linked Table, and select "Linked Table Manager", you can select the ODBC DSN and "Edit" (click on the Edit button) the connection. The connection string I used was the same as the one I used for my Pass-Through query.
    Once I made these changes to my Linked Table Manager, and to the Pass-Through query "ODBC Connect Str" property, I was able to launch the Access App without having to select a DSN, or enter credentials.
    This was what I was hoping to achieve.
    Before my resolution, Access had connection strings that only had an "ODBC=" parameter, which caused it to ask which DSN to use. And it did it for each Linked Table and each Pass-Through query in the app. I am assuming that when creating Linked Tables, and Pass Through queries that it is MS Access' default.
    I've added this post in case anyone searching for a solution to this problem will have a solution that could work for them.
    Regards, and good luck!
    JohnK

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

Similar Threads

  1. Replies: 8
    Last Post: 05-18-2021, 10:05 AM
  2. Using a table in a query multiple times.
    By vgillis in forum Queries
    Replies: 1
    Last Post: 03-05-2013, 01:29 PM
  3. Replies: 3
    Last Post: 12-01-2012, 12:37 PM
  4. parameter prompting multiple times
    By sandyg in forum Access
    Replies: 1
    Last Post: 08-15-2011, 10:10 AM
  5. Replies: 5
    Last Post: 12-10-2009, 10:33 PM

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