Linking Access Front-End to SQL Back-End in a Interesting Environment

We currently have a split Access database where we need to move the back-end to a SQL database.  What makes this tricky is we have an interesting network environment.  We have satellite offices in other locations.  Some of these offices were acquired in mergers, and are currently on separate networks (the networks have not been consolidated yet).

Most of our users are on the “main” network where the SQL server resides, but some of our employees in these satellite offices are not.  They have user credentials for the “main” network, but seldom use them (this database will be one of the few things they will need to access on the “main” network.

So, I have been trying to figure how to set up the ODBC connections for them so they can seamlessly connect to the SQL server on the “main” network and use the database.  I have played around with various different options (User DSN, File DSN), but have not had the success I desire.

My initial hope was to set up a File DSN, so I didn’t need to manually create User DSN connections on each and every workstation.  However, I ran into issues there.  These “satellite” users have to use SQL Server Authentication, since the Windows Authentication they are logged in under is on a different network (and therefore different credentials).  

We don’t really expect these “satellite” users to remember their credentials on the “main” network, so we created a general user account on the “main” network with an unexpiring password that has access to the SQL Server database we need.  So the thought was to use these credentials in the SQL Server Authentication of the ODBC connection.  However the issue we have run up against is that regardless if we try a User DSN or File DSN, the password is not remembered, so it always prompts the user for the password every time they try to access a new table during their session.  Obviously, that is not going to fly.

So I am not really sure what to do here.  Does anyone have any ideas that would work in our scenario?  

I fully expect to have two different front-ends, one for the “local” users and one for the “satellite” users.  I don’t think the local users will be a problem, as they can just use Windows Authentication.  But these satellite users situation is really causing me some headaches here.

Note.  I started a discussion here on this topic, but have seemed to run into a dead-end (in fairness, the person did answer my original question, but I added on to it).

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I would not use a frontend on the user's computer in this case. Simply install a Terminal Server on the main network and install the frontend there. If you use at least Windows 2008 Server you have the possibility to create Remote Desktop Applications where the user not gets a complete desktop but only the desired program, here an Access application running in the runtime version of Access. On that way you can simply send the little remote application installer which Windows automatically creates on demand to the user so the user gets an application icon to start, then he only needs to authenticate with a Windows login (which is in your case of the main network with the credentials of this domain) and the user is no in the application on his desktop but working on the Terminal Server - and that means: You can use Windows authentication for the application/SQL Server login as it runs on the main network and also the user gets probably a better performance as only two servers talk with each other, in best case with a server-internal network usually used in server locations. Another big advantage is that the user don't need to install a runtime version of Access, only the server needs to install one (and only one which is used by all users) so you do not have any local update issues with newer Office versions or other local individual installation problems. The Terminal Server solution also works if someone is connected with VPN from home or in a hotel etc., and all users can get one version of the application only.

By the way: Forget DSNs, look into Google for "DSNless connection" and open the Microsoft article, this explains how to setup the connection data into the linked table/view directly so you never need to setup a DSN anymore, it's done once by the developer (and of course if you relink the table) and it can be done with a VBA script so you can automate the process for all links.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JoeMiskeyAuthor Commented:
Thanks for the reply.  I will get with my network guys over the next few days and discuss this with them to see if this is a possibility in our environment, and post back.
JoeMiskeyAuthor Commented:
We didn't go with the Terminal Services approach, as our network guys said it was cost prohibitive (not wanting to pay for enough licenses for everyone), but the last paragraph gave us what we need.

We went with the "DSN-less" connection described in Method 1 here:

Thanks for your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.