Results 1 to 9 of 9
  1. #1
    barryseymour is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Location
    Los Angeles, CA
    Posts
    7

    Question How to deploy the required DSN to the LAN when publishing an ACCDE? BRLFQ

    I am developing an Access 2016 application that connects to SQL Server. It is for use within our corporate LAN only. All users have Office 365 with Access included.

    All workstations have security restrictions that prevent them from installing applications, so my plan was to place the ACCDE file on a shared folder on the LAN. My question is - what do I do about the ODBC DSN?




    • I was going to write code to recreate the DSN locally each time, but workstation security restrictions would prevent that code from running.
    • I was going to store the DSN file in the same folder as the application, but that file is a plain text file and can be easily read. That would be a bad security practice.
    • I found a link to a site that tells you how to convert your linked tables to ones using 'DSN-less' connections, but that seems like a lot of complexity, fraught with error. (http://www.accessmvp.com/djsteele/DSNLessLinks.html)
    • As part of my testing I put the ACCDE on the LAN and deleted all my DSNs. It still worked! Perhaps Access 2016 only requires a DSN when you're initially setting up the table? Do I actually need a DSN when deploying an ACCDE?


    I would be keen to hear of any recommendations on how to configure DSNs so I can correctly and securely deploy this application to the LAN.

    Kind thanks in advance.

    Barry

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Several thoughts. Even if the accde file is on the server, Access still needs to be installed on the user's computer, so I'm not sure what you gain by putting the file on the server. You lose some network efficiency, and each user should be running their own copy of the front end anyway.

    I'm not sure what you mean when you say the code to link wouldn't run. If it doesn't run, no other code in the db would run, rendering it useless anyway.

    An option to DSL-less connection is a File DSN stored on the SQL Server machine. I use DSN-less now, but used File DSN for a long time. I used a hidden share so users couldn't see the file, if somebody actually looked for it. I adapted this:

    https://support.microsoft.com/en-us/kb/892490

    to be used with a local front end table that contains fields for table name, server name, etc. I did that because many of my apps connect to multiple databases and even multiple servers. I use Windows Authentication, so no password is stored in the table or DSN.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    barryseymour is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Location
    Los Angeles, CA
    Posts
    7
    Paul:

    Thanks for your quick response. Answers for you:

    1. Access is installed on all workstations.

    2. If I write VBA code to manually recreate the DSN every time, the security restrictions on the workstation would very likely prevent that bit from running. That's what I meant.

    As for a DSN file on the Server, could you clarify on what a 'hidden share' is?

    FWIW as I've continued to research on the web, I found this. Power Tip To Improve the Security of Database Connections.
    I'm thinking the idea of creating a connection at startup that is cached and re-used might be the way to go.

    Thanks again,

    Barry

  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
    Somebody browsing the network won't see it. You have to know it exists.

    https://answers.microsoft.com/en-us/...e-e2dba34ef6fd
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    barryseymour is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Location
    Los Angeles, CA
    Posts
    7
    Ah. OK. I get that now.

    I have a test user who gets an ODBC error when they run the ACCDE. Fair enough. But I'm having trouble even debugging this darn thing. I deleted the system DSN I used to link to my tables. I also deleted the file-based DSN I had used previously. My Access app still works! Does Access cache this information between sessions? Am I going to have to restart my system?

    I was able to implement the 'cached connection' suggestion in the link I mentioned above; it did not help my test user.

    I'll continue to research. Any thoughts are welcome, thanks.

    Thanks,

    B

  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
    What's the error, or is it one of those cryptic ones? Does it sound like the code is running okay but the connection isn't good? I assume it runs successfully on your computer?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    barryseymour is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Location
    Los Angeles, CA
    Posts
    7
    Runs fine for me, of course - but just a cryptic 'ODBC Error' on others. I manually created a file DSN on a user's computer, saved it to the hidden shared folder on the server, worked fine - but his username is in the DSN file. That won't work for a shared installation.

    Going for the 'DSNLess' approach tomorrow. I'll post my results.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You would need to have linked the tables using that DSN. In any case, certainly try the DSN-less approach and we'll figure it out. I didn't use Doug's method, but I know a lot of people have so it should be solid.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    barryseymour is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Location
    Los Angeles, CA
    Posts
    7
    I'm going to. Each connection has to have the correct Windows username and computer name anyway, so this will have to do it. I'll post my results.

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

Similar Threads

  1. Replies: 5
    Last Post: 04-05-2016, 12:37 PM
  2. Publishing as ACCDE Questions
    By Paul H in forum Access
    Replies: 3
    Last Post: 04-05-2016, 07:26 AM
  3. Deploy same split database to different companies
    By Lowell in forum Database Design
    Replies: 7
    Last Post: 02-24-2013, 08:50 PM
  4. How to deploy a database to be used on 2 different pcs
    By brownsugar7210 in forum Access
    Replies: 3
    Last Post: 01-14-2013, 06:02 PM
  5. How to best deploy a database?
    By robertdbuckley in forum Access
    Replies: 3
    Last Post: 05-23-2012, 12:28 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