Results 1 to 6 of 6
  1. #1
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591

    User DSN vs System DSN


    We've been doing some testing on a laptop with multiple users and need to set up some DSNs with a SQL Server back end. After a few failures connecting it was decided it would be simpler to set up a System DSN which would be accessible to all users. We did this using identical credentials as the User DSN that we already knew worked for me. Long story short, this did not work. The Access database would not connect to the SQL Server back end using the System DSN. As a matter of fact, Access couldn't even see the System DSN. We discovered this by trying to manually create a linked table using the new System DSN. We gave it a unique name, and could not find it in the list of Machine Data sources. We are now back to created a new User DSN for each person who logs in to this machine. This is doable and not terribly inconvenient, but I (we) would like to know if it is possible to use System DSNs at all or if Access simply can't do it. If so we won't waste any more time trying.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I have not reviewed this stuff for several months. IIRC, all you need to do is use a system DSN and the correct drivers on the clients. Then, use the connection string defined by your Database Admin. I recommend incorporating the connection string in VBA. Obfuscate a function that will manage the connection. Then, distribute accde versions of the FE.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    For what it's worth:

    I also have Win XP and Access 2010.
    I have SQL Server Express 2008 R2 on the XP computer (Local) and SQL Server Express 2014 on a Win 7 computer (on the LAN).

    I have set up a System DSN for each SQL Server.
    For Win XP & SQLSE 2008 R2, the driver I used is SQL Server Native Client 10.0
    For Win7 & SQLSE 2014, the driver I used is SQL Server Native.

    On the XP computer, I have an Access 2010 FE linked to the SQLSE 2008 R2 dB.
    Also on the XP computer, I have an different Access 2010 FE linked to the SQLSE 2014 dB.


    I am not having any problems with either FE/ SQL dB set up.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would use a file DSN on the server rather than either (though lately I've been using DSN-less connections).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    I have no experience setting up connections in VBA. Not sure I want to go that route just yet. I did create a file DSN using the ODBC Manager. I've put it on the network in hopes this one file will serve all, but I'm guessing we will revert to each user having an individual DSN. There really aren't that many of them and most laptops have only one user.

    Thanks for all the replies.

    Paul

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The file DSN should work. I created one on the server, linked tables using it, and never touched user machines.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 07-20-2012, 05:35 PM
  2. Replies: 2
    Last Post: 04-12-2011, 10:14 AM
  3. User Logging System
    By GraemeG in forum Programming
    Replies: 2
    Last Post: 03-25-2011, 03:27 AM
  4. USG Clinic exam system: Changing User
    By isnpms in forum Access
    Replies: 2
    Last Post: 08-22-2010, 07:31 PM
  5. New access user -- help in creating a sub system
    By fidget_sane in forum Access
    Replies: 20
    Last Post: 04-22-2010, 11:46 AM

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