Opeing multiple databases with a single login RRS feed

  • Question

  • I have a SQL Server database with tables for several Access databases for different clients.  They all use the same User ID and password to get in the SQL database.   I want to be able to login one time and then open one or more of the Access databases as needed.  Is there a "front-end" function or program that would let me do this? 
    Monday, September 30, 2019 2:12 PM

All replies

  • It can be done with basic coding.  If all these separate application do share a common table (linked back end) - then you can set a flag value in that table when a user has logged in - - that the other applications can read.

    To explain it is one thing.  To implement it is more difficult to describe  in detail as a post.  But essentially one assumes there is a table that holds the correct PW for each user; one could add a field to that table (i.e. LoggedIN) that writes a datetime stamp...  then the other apps when they open can look at that and if it is within a certain time length then skip the log in..... not sure if one explicitly logs out of your app so there are a bunch of little nuances that need to be managed....

    Monday, September 30, 2019 8:25 PM
  • I do this all the time.

    It is assumed (presumably) that each front end is linked to a particular database. Be a developer using accDB back ends, or say SQL server back ends, we all quite much have resorted to building our re-link code.

    You no doubt have one instance of sql (likely free express version) of SQL running. In this case, I have say 4-5 databases (perhaps for testing for different clients).

    In all of the above, I use the same Logon + password. The only issue is do you have one front end, or a different front end for each client?

    If you have different front ends, then of course once the FE is linked to the given database, then just launching each front end will be linked/connected to the correct BE database, and they all will and can use the same UID/Password. And now you are free to work + develop on that FE.

    If you have a bunch of separate and “different” back end databases, but one front end, then of course you have to re-link the table(s) in question to point to the correct back end.

    So re-linking during the development process will not have to occur if you have a separate FE for each database.

    If you have one front end, and several different test versions or copies of client databases for the back end, then of course you need to re-link to “point” the FE to the appropriate back end.

    As noted, be it different customers with accDB back ends, or different customers with different SQL back ends? At the end of the day, on start-up you have to provide (or have) some code to check if current linked DB is correct, and if not then you do a one-time re-link on start-up.

    There are a good number of “example” table re-linkers available, but even without a sql back end, some type of re-link system should be cobbled together.

    I use an external setup.ini file that contains the SQL server, the database, the UID, and the password. So, on application start-up I read in these values. Then you code has to check the current linked database. If they match, then you just move on to launching your main start-up form, if not, then you re-link and then launch your main start-up form.

    While you CAN change the user/password without having to re-link tables, to change the back end your front end points to will require a re-link.

    So in summary:

    We have quite much have resorted to some re-link system.

    You CAN change the user/password without a re-link.

    You MUST re-link if you need to point to different BE database (but CAN continue to use the same uid/password for all of the databases).

    At deployment time?

    If you are on client site, then you link to production database.

    Compile your accDB to an accDE

    Deploy FE to all desktops (again, hopefully you have some automated system to do this).

    If you are off site, and can’t re-link, then the suggested idea of having an external text file (or setup.ini) file that you can read on start-up, and re-link to the customers production back end is required (at least for those cases in which you provide a update, and can’t be on-site to pre-link the database to the correct (customers) database.


    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Tuesday, October 1, 2019 12:34 AM
  • You just store the credentials to e.g. a Table and you just reuse them to have them linked
    Tuesday, October 1, 2019 6:49 AM