Connecting a Multi User Access Front End to SQL Server Back End
Thank you for your help as always!
I have built an Access database that we intend to distribute to ~100 end users. I am currently running a pilot program for 5 users. I have migrated the tables to SQL 2014 Server but currently the back end of the pilot program is in a shared network folder (location unknown to them). I distributed the Access front end (accde file type) by saving the file on that shared Network and instructing the end users to save a copy to their desktop. The pilot is working fine, if not a little slow.
The issue that I am having is this:
I am able to set up an ODBC Connection on my machine and am able to connect to the SQL Server backend through that connection. However, I am not sure how to get my end users Access to the tables on the server. Do I need to create a user name for each user on the server and give them read and write access? Do I also need to create an ODBC connection on each machine that we plan to install the Front End on? I would prefer to not have to create an ODBC connection on each machine, is it possible to work around this? Thank you!
Access Version: 2013 SQL: 2014 Working on SSMS 2014
-Charlie
Answer
A few things:
When you create a linked table, simply use the ribbon import and link – and then ODBC database. Just choose a file DSN. The reason for this is that access by DEFAULT will use a DSN-less connection. In simple terms, this means when you link the tables, then you can distribute your applcatation to each workstation and there no need to setup a SYSTEM/Machine DSN.
So just keep in mind that use the default file DSN – once Access creates the link to SQL server, then such links are DSN-less, and you don’t need any setup on each workstation.
As for creating users on SQL server? Well, you likely don’t need to unless you want some kind of special security for each user. If you using SQL logon, then MAKE sure that during the above linking process you “check” the save password option. Once again, since by default the linked tables are DSN-less, then every user will in fact be using the one and same SQL User/password and thus this will be transparent to each user (they will not have to log on).
If you using windows authentication for SQL logons, then security is setup with the windows system and not SQL server. In this case then each users windows logon will be used to control (permit) use of the SQL server. If you not using a domain controller, then you be using SQL logons, and likely just the one logon that you are using will suffice. Often even in a corporate environment because I don’t want to call up the IT admin folks for each logon and permissions to SQL server, then I still OFTEN choose SQL logons. Thus “once” the IT admin folks give me enough rights to the SQL server, then I am free to create my own logons, or just use the “one same” logon for everyone and thus don’t have to waste time bothering the IT folks.
A few additional final points: Ignore suggesting to use all kinds of ADO and VBA code and connection strings etc. – they are not required. In fact in most cases you want to AVOID ADO code in your application. And oleDB is being depreciated for SQL server (which ADO tends to rely on).
You STILL as a matter of good deployment want to place the front end program you have on each workstation. Just like you install word on each workstation, or your accounting packages, now that YOU ARE developing software, then you install your software on each workstation like the IT industry done for the last 30 years. You can certainly share data on a shared folder, but you install the actual application (word, Excel, or in this case YOUR applcatation on EACH workstation. And you should compile the accDB to an accDE before any deployment.
So you don’t really need any special code on start up to “connect” or “link” to SQL server if your deployment to such users is on your same network. If you are developer or consultant “offsite”, then you likely need to add some code on start up to re-link to THEIR sql server that no doubt would be different than the one your developing with offsite. So some ability to re-link to a “different” SQL server then the one your are developing with would be required if you not able to develop on site, or that the SQL server you working with is a “copy” or “test” version of the Actual production SQL server being used.