none
How to create an ODBC connection for runtime Access to SQL Server database tables RRS feed

  • Question

  • I want to do something I have not done before and need some expert advice/direction. I want to connect an Access 2016 database front end to a SQL Server 2016 back end--the tables will reside in the SQL Server database. The front end application will reside on multiple users' computers (about 8) who will run the Access front end application using the Access 2013 runtime version. I have created a machine ODBC connection and successfully linked the tables to the Access front end on a single machine where I have the full version of Access on the same machine as the SQL Server database resides (full version). From what I've read, I think I need to create an ODBC connection and/or a Data Source file so that each computer sees the linked SQL Server tables. The research I've done has pointed me in what appears to be different directions and none of them includes an Access runtime. Can anyone advise me as to the best way to go and especially HOW to create the connection between the front end and back end so that I do not have to create the connection on each of the 8 user's computers. I'm shying away from VBA code based on what I've read. This is the first time I've done this with SQL Server as the back end, although I have used Access as the back end many times where I just need to be sure the users' computers have the same path to the back end. So, how do I create a connection that I can copy to each user's computer using the Access runtime? Pardon me if I am not phrasing this correctly.

    I appreciate your help!
    Friday, October 20, 2017 3:05 PM

Answers

  • How you do this with the runtime, or the full edition of Access is quite much the same.

    So on your machine with the full edition, you then simply just create a file DSN (always create a file dsn, do NOT use anything else).

    Once you link the table to sql server, at that point you are done. You should THEN compile the accDB to an accDE.

    This pre-linked accDE can now be deployed and copied to any workstation – the links to sql server will REMAIN intact, and you do NOT have to setup any kind of DSN or deploy any DSN to each workstation.

    So access by default creates a DSN-less connection if you use FILE DSNs.

    So there no need to use any VBA,

    No need to run some VBA code on start up to relink.

    No need to setup or install any kind of DSN on each workstation.

    All you have to do is link the tables to SQL server “one” time with the full edition – thus no code is required. You then can deploy that copy of the application to each workstation that is using the runtime. You not need to setup any DSN’s or anything else on each workstation.

    So there is nothing in this process that requires code, VBA or anything else. You can pre-link the tables with the full edition, and then deploy to all the workstations. Access by "default" creates DSN-less connections. What this means is that Access ONLY uses the FILE dsn ONE time "DURING" linking - after that you dsn-less and you don't need the FILE dsn on your computer, or any of the workstations you plan to run the application on.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by Sopater Wednesday, October 25, 2017 3:23 PM
    Friday, October 20, 2017 8:37 PM
  • Hum, the only reason I see in your info is that you used the native 11 driver, and that is not (may not) installed on each workstation by default.

    While the “native 11” or later drivers are a better choice, you do have to ensure that the native 11 drivers are installed on each workstation.

    If you not using “later” features of SQL server (such as datetime2) columns, then I suggest you re-link using the “SQL server” driver that appears in the ODBC setup.

    So you want to re-link using the “legacy” ODBC driver.

    If you really want to use the “native 11” (or newer) drivers, then you have to install that driver on each workstation.

    So what you doing should work without issue. Also, the fact that you’re linking on the SAME server as you running Access means that issues of permissions and rights could come into play. Are you linking the tables using windows authentication, or are you using a SQL logon + password? You can “always” link using windows authentication on the SAME server because you have by default windows authenticated rights. If you not using a “domain” system for your network, then you REALLY want to link the tables using SQL logons as opposed to windows authentication WHEN you are linking the tables.

    You can regardless of the windows authentication ALWAYS use sql logons if you wish (sql logons work for both “domain” networks, or networks that don’t use domain control).

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by Sopater Wednesday, October 25, 2017 3:23 PM
    Monday, October 23, 2017 6:42 PM

All replies

  • Hi,

    Just curious, why did you say you don't want to use VBA? If you do, you can better control how the front end connects to any back end at any location. In any case, I though once you connect an Access FE to a SQL Server BE, it doesn't matter how you do it initially, Access will save the connection. If so, then you shouldn't have to do anything more to transfer the FE to the user's computer. Have you tried it yet and it didn't work?

    Friday, October 20, 2017 3:25 PM
  • Can anyone advise me as to the best way to go and especially HOW to create the connection between the front end and back end so that I do not have to create the connection on each of the 8 user's computers. I'm shying away from VBA code based on what I've read.

    Based on what you are asking here, the suggestion I offer is based on an understanding of what the various development platforms offer.   Access is an integrated (self contained) development platform -- contains its own backend and frontend which is ideally manipulated using VBA code (and is the king of rapid application development - RAD).  When you introduce Sql server, which is a significantly more powerful backend than the Access Jet engine backend, you are now moving away from an integrated environment.  You have a separate backend and a separate frontend. 

    The Access frontend can be used with a sql server backend (but to me that seems a little redundant - why use an external backend with a platform that contains its own backend?).   The "Ideal" platform for a sql server backend would be a .Net frontend.  With the evolution of development software, .Net now contains the Entity Framework, which makes working with .Net (almost) as easy as working with Access as a frontend except you now have the power of object oriented programming (OOP) and Linq (language integrated queries), so just like Access you can write your own queries within the client application.

    Here is what you need to evaluate -- the target audience (users) of your application.  If the target is just in house users (inter office users) Access would be a great platform - easy to develop and maintain.  Sql server is for Industrial use (like world wide web, WANs, ...).   If your target includes remote location users, Access as a front end would be kind of counter intuitive --  would not be able to take full advantage of the horse power of sql server.   

    If your audience is local, and you are not dealing with millions of records, I would use Access as the back and front end application.  If you audience is not local and you need to stay with the sql server, I would think about .Net for the front end.  It's like if you are just commuting to work, the store, use a smart car.  IF you need to haul large cargo a long distance use an 18 wheel truck.  It would counter intuitive to put a 1500 horse power engine in a smart car.


    Rich P


    • Edited by Rich P123 Friday, October 20, 2017 7:52 PM ......
    Friday, October 20, 2017 4:43 PM
  • I created an ODBC connection from within Access and it worked.  I used full Access on the same machine on which SQL Server is running.  I created an .ACCDE version of the database and ran it with the runtime and it works fine on the same machine.  However, when I try to run the .accde file with the runtime on anther machine on the network, it fails with an error:  "ODBC--connection to "MyData" failed.  I am open to using VBA if by using it I can better control how the FE connects to any BE at any location.  I would like to not have to set up each individual's connection separately.  Is there a way to set up the connection so that I just install the Access runtime, copy the database file (Mydatabase.accde) to the user's computer and then run the database from the runtime. 

    Also, I am not using an Access Back End because the tables have over 2 million records currently.  Also, all the users are at the same business location on the same network.

    Can you tell me how to create the connection using VBA or in some other way?  Is do you have a link to instructions somewhere.  Not having done this before, I would appreciate detail. 

    Thank you much!

    Friday, October 20, 2017 6:13 PM
  • Thank you for your reply! 

    I am not using an Access Back End because the tables have over 2 million records currently.  Also, all the users are at the same business location on the same network.  Thus, it made sense to me to use Access as the FE and SQL Server as the BE.  Additionally, I know Access but have not used .NET, so there is the learning curve I did not want to have to deal with.  (I have used HTML and ASP some years ago.)  I am familiar with the fact that Access is for smaller applications (like this one), SQL Server is for larger applications.  And then there's Oracle.  :-)

    My response to .theDBguy gives more detail about my problem and what I'm trying to do.  I am glad to use VBA if that accomplishes what I need to do.  Can you provide further guidance on HOW to get done what I need to do?  Thank you again!

    Friday, October 20, 2017 6:33 PM
  • My response to .theDBguy gives more detail about my problem and what I'm trying to do.  I am glad to use VBA if that accomplishes what I need to do.  Can you provide further guidance on HOW to get done what I need to do?  Thank you again!

    Be happy to... Have you seen this article?

    Hope it helps...

    Friday, October 20, 2017 7:04 PM
  • How you do this with the runtime, or the full edition of Access is quite much the same.

    So on your machine with the full edition, you then simply just create a file DSN (always create a file dsn, do NOT use anything else).

    Once you link the table to sql server, at that point you are done. You should THEN compile the accDB to an accDE.

    This pre-linked accDE can now be deployed and copied to any workstation – the links to sql server will REMAIN intact, and you do NOT have to setup any kind of DSN or deploy any DSN to each workstation.

    So access by default creates a DSN-less connection if you use FILE DSNs.

    So there no need to use any VBA,

    No need to run some VBA code on start up to relink.

    No need to setup or install any kind of DSN on each workstation.

    All you have to do is link the tables to SQL server “one” time with the full edition – thus no code is required. You then can deploy that copy of the application to each workstation that is using the runtime. You not need to setup any DSN’s or anything else on each workstation.

    So there is nothing in this process that requires code, VBA or anything else. You can pre-link the tables with the full edition, and then deploy to all the workstations. Access by "default" creates DSN-less connections. What this means is that Access ONLY uses the FILE dsn ONE time "DURING" linking - after that you dsn-less and you don't need the FILE dsn on your computer, or any of the workstations you plan to run the application on.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by Sopater Wednesday, October 25, 2017 3:23 PM
    Friday, October 20, 2017 8:37 PM
  • This solution looks like just what I need.  I tried it, and can connect to the database on the machine that SQL Server is running on.  However, when I try to connect to the database on a different machine on the network, I get the following error:  "ODBC--Connection to 'SQL Server Native Client 11.0INTSQLSVR' failure."  Can you tell me what I need to do to resolve this?  BTW, all machines are 64 bit and I created a 64 bit ODBC File DSN.  INTSQLSVR is the name of the machine running SQL Server on which the SQL Server database resides.

    Thanks!

    Monday, October 23, 2017 5:21 PM
  • Hum, the only reason I see in your info is that you used the native 11 driver, and that is not (may not) installed on each workstation by default.

    While the “native 11” or later drivers are a better choice, you do have to ensure that the native 11 drivers are installed on each workstation.

    If you not using “later” features of SQL server (such as datetime2) columns, then I suggest you re-link using the “SQL server” driver that appears in the ODBC setup.

    So you want to re-link using the “legacy” ODBC driver.

    If you really want to use the “native 11” (or newer) drivers, then you have to install that driver on each workstation.

    So what you doing should work without issue. Also, the fact that you’re linking on the SAME server as you running Access means that issues of permissions and rights could come into play. Are you linking the tables using windows authentication, or are you using a SQL logon + password? You can “always” link using windows authentication on the SAME server because you have by default windows authenticated rights. If you not using a “domain” system for your network, then you REALLY want to link the tables using SQL logons as opposed to windows authentication WHEN you are linking the tables.

    You can regardless of the windows authentication ALWAYS use sql logons if you wish (sql logons work for both “domain” networks, or networks that don’t use domain control).

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by Sopater Wednesday, October 25, 2017 3:23 PM
    Monday, October 23, 2017 6:42 PM
  • That was what I needed--to install the SQL Server Native Client 11.0.  Thank you!  I used SQL authentication and was able to then open the tables without errors.   I still need to go back and re-create the ODBC connection without using the sa account, which is what I use to test.  I will let you know if I can get it to work with a user account.  What I would prefer to do is use one SQL Logon account for all of the users rather than create one for each user.  I assume I can do that, but have to test that and see how to make that happen.
    Monday, October 23, 2017 10:20 PM
  • Sounds like you on the right train tracks. And yes, it is "very" common if not most cases where we link using the same one SQL logon/password, and then use that for everyone (so again, a good and common approach on your part).

    I can suggest in the future there is a VERY cool way to link the tables without having to include the logon/password. This is "nice" for several reasons, and one being that users thus can't see the logon/password in linked tables. (for any non runtime user, if they "hover" their cursor over a linked table, you can see the logon/password.

    This approach takes a bit of code - but just keep this cool idea for down the future.

    Good luck!

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada


    Tuesday, October 24, 2017 2:11 AM
  • Hi Sopater,

    Has your issue been resolved? If it has, I would suggest you mark the helpful reply as answer which is the way to close a thread here.

    Regards,

    Tony


    Help each other

    Wednesday, October 25, 2017 7:06 AM
  • I was able to access the data by creating a single user account that is not the sa account.  I had to install the SQL Server Native Client 11.0 on the test user workstation, but Microsoft did not make it easy to find that file.  However, after installing the client on the test system, it seems to be working as desired.

    Thank you so much for your help!!!

    Wednesday, October 25, 2017 3:30 PM
  • Sopater,

    The linked server situation was not fully addressed.  There are several linked server security solutions and presumably the one you have in place is not an issue?  There is a primary security posture wherein the use of either integrated security or sql authentication is used at the connection level.  Then subsequent to that, there are security postures for the linked server as well.  Given integrated security, the linked server connection can be made without a security context (not made - lets anyone through), it can be made for the security context of the user making the request, or it can be made by the service account, or with sql authentication, it can be made by a user given specific rights.   My guess is that you are still using sql authentication on the backend which makes it considerably less complicated.

    Here's the drill:

    Assuming you use integrated windows security to access the sql backend, the better solution is to create an SPN for a specific account (generally a service account and preferably a group Managed Service Account).  Once the SPN is created, access the Active Directory and set up the designated account with Kerberos delegation to the two (assuming you have two linked servers - you need one SPN per linked server) SPNs.   This way, when a user logs in, you can delegate the permissions across the linked server based on the user login rather than by some standard rule.  It also allows you to track activity back to a user rather than trying to figure out who was logged in as a user taking advantage of a sql authenticated account.

    I was also not overly excited about this answer because it never really addressed your issue on whether it was appropriate to use a 64 bit ODBC connection or a 32 bit ODBC connection and there is actually additional confusion there.   In the system32 folder there is a 32 bit location for both 32 and 64 bit odbc connections, and in the syswow64 folder, there are similar libraries.  The location wasn't addressed because it was assumed that the accDE would handle the situation.  Either way, it would be nice if it had been addressed.   I'm going to have to look elsewhere for an answer and it may be why this thread is still sitting at zero recommendations.

    It is also a couple years old, but two years ago, the issues of security were also not as pressing as the news about unsafe routers and problems with switch vulnerabilities had not yet become so public.  If you are thinking about changing your security posture, they may help to set you in a better direction.

    TO SETUP AN SPN

    Use the Microsoft tool to get started:  https://www.microsoft.com/en-us/download/details.aspx?id=39046

    WHAT IS A Group Managed Service Account

    https://blogs.msdn.microsoft.com/markweberblog/2016/05/25/group-managed-service-accounts-gmsa-and-sql-server-2016/

    Kerberos Delegation for beginners: (these are older articles but address the security posture for the linked server in a more effective manner)

    https://www.mssqltips.com/sqlservertip/2312/understanding-when-sql-server-kerberos-delegation-is-needed/https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ee191523(v=sql.100)

    WHAT ABOUT PROTECTING THE STREAM:

    There is one other item not covered here and that is whether the stream between Access and SQL Server should be enabled.   It's one of the more important items on my list.  For example, what happens if someone hires a third party to put in a VOIP on your network and it isn't properly isolated.  Can the VOIP connection be used to spy on the data streams between your sql backend and the connected MSAccess machines?


    R, J


    • Edited by Crakdkorn Thursday, March 14, 2019 12:59 PM
    Thursday, March 14, 2019 12:44 PM