Question Locked

MS Access frontend with SQL Server backend

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have an MS Access app (.mdb) on a Citrix server dynamically connecting to one of three SQL DBs depending on the particular assignment of the logged in user. All users use the same .mdb in Citrix.

When a user logs in the app determines which SQL DB the user should connect to and re-establishes the links to that SQL DB.

However, each time a new user logs in the 'relinking' occurs for all users to the same DB assigned to the newest logged in user. So all currently logged in users may or may not be connected to his / her respectively assigned DB.

The 'relinking' complicates things further by disconnecting already logged in users from their DBs and any data edits currently in process fail with 'ODBC connection failed' errors.

So, is there any way that a single .mdb can allow dynamic and unique connectivity to the various backend SQL DBs without adversely affecting other users logged into the same app?

I've read on this site where some apps will copy the .mdb to the user's own machine at login but that would not work here because the users, app. and SQL DBs are all in different states (USA). That's why we use Citrix.

Thanks for your help / suggestions.

3 replies

by lowlands

about the ability to have one access frontend connect to different database backends for different users at the same time. Sounds like an pretty troublesome legacy application. I deally I think I'd have the front end rewritten to a 'real' front end .NET or even VB app. That's probably not an option, but how about copying the master mdb file to a user specific folder on the Citrix server. Whether that be a new directory structure specific to this app or simply to documents and settings. Create a little script, batch or vbs, to check if the file is there, if not copy and then start the app.

by tony hopkinson

Knee jerk reaction is you've been a bit too clever.
Take the choose the sql db logic out, replace it with a choose the mdb, and have one for each SQLDB already linked up.

is my first thought.

This discussion is closed to new replies.