ODBC connection to SQL Server tables for runtime version of Access

[Eutychus]

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!

 

[dhookom]

I had a solution for you until you said you didn't want to use VBA. You can walk around to each computer and manually create either a machine or user ODBC/DSN. Creating a machine DSN typically requires admin privileges.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

BTW, as you “walk around to each computer” be careful, as it would be appalling to fall out of any windows. ;-)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Eutychus]

Thank your for your responses. I am open to the VBA solution if it will not be a maintenance problem. I am not eliminating any options. Regarding walking around to each machine, I could to that, too, and I'll stay away from open windows! If I do that on the other workstations that do not have full Access, would I go the Administrative Tools and "ODBC Data Sources (64-bit)" and create a DSN to connect to the SQL Server database? Will runtime Access recognize that?
Thanks again!

 

[dhookom]

I use the code from Doug Steele that creates DSN-less connections. You should only have to run this code once to change your linked tables to DSN-less. Then create and distribute your Access front-end file.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016