INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ODBC connection to SQL Server tables for runtime version of Access

ODBC connection to SQL Server tables for runtime version of Access

(OP)
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!

RE: ODBC connection to SQL Server tables for runtime version of Access

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

RE: ODBC connection to SQL Server tables for runtime version of Access

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: ODBC connection to SQL Server tables for runtime version of Access

(OP)
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! smile 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!

RE: ODBC connection to SQL Server tables for runtime version of Access

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close