I'm trying to get an additional PC on a network of Windows 10 computers with an 32-bit Access (365) front-end connected via ODBC to SQL Server Express (version 11) that all use ODBC Driver 13 for SQL Server. The added PC runs Windows 11. When I try to install that ODBC driver it says it's incompatible. This is the case as I work up the versions so I'm assuming it is going to require a very recent version of the ODBC driver.
Question: Can this additional PC run a different version of the ODBC driver? The front-end is stored on the server so is shared. I assume that once the front-end is linked to SQL Server tables those links will be using the same driver for everyone that uses the front-end. Is that right? Would a solution be then to copy the front-end on to the new PC and run it separately with the more recent ODBC driver?
Yes, that should work.
It is generally advisable to copy the front-end to each user's local hard disk and thus give them their own file for exclusive use.
Yes, I know this advice but it makes for more complicated roll-out of updates. I've never had any problem sharing one front-end file.
So, confirming, there cannot be one front-end Access file linked via different (per station) ODBC drivers to SQL Server, presumably because the ODBC driver settings are stored with the front-end file.
Yes, I know this advice but it makes for more complicated roll-out of updates. I've never had any problem sharing one front-end file.
So, confirming, there cannot be one front-end Access file linked via different (per station) ODBC drivers to SQL Server, presumably because the ODBC driver settings are stored with the front-end file.
Yes, I know this advice but it makes for more complicated roll-out of updates. I've never had any problem sharing one front-end file.
So, confirming, there cannot be one front-end Access file linked via different (per station) ODBC drivers to SQL Server, presumably because the ODBC driver settings are stored with the front-end file.
Device drivers are generally stored with Windows itself (in the /SYSTEM folder or one of its child folders) and have little or nothing to do with Access front-ends. It should also be noted that in many commercial networked environments, users lack sufficient privileges or permissions (or both) to install a driver. If your shops have pro-active security staff (a "busy" IT shop), driver choice might be defined by a project manager's advice, but the IT shop will be the one to force the right driver to be installed. It would be good to know and understand that part of your environment.
Does this mean that if I link a shared Access front-end to SQL Server tables on a new workstation using a particular ODBC driver that that won't affect the links that other workstations have to the SQL Server tables using a different ODBC driver (again all within one shared Access file)?
Yes, I know this advice but it makes for more complicated roll-out of updates. I've never had any problem sharing one front-end file.
So, confirming, there cannot be one front-end Access file linked via different (per station) ODBC drivers to SQL Server, presumably because the ODBC driver settings are stored with the front-end file.
I foresee problems. User1 opens the shared FE and relinks the tables with ODBC13. User2 opens it and relinks with ODBC18. What will happen to User1?
Bite the bullet, and give every user their own FE, per best practices. If you look around, there are several tools / scripts available to keep the FE updated for all users, minimizing the time needed to roll out a new version.
I agree with Tom. This is a problem waiting to happen. Your setup is NON STANDARD. MS has ALWAYS recommended that the FE not be shared. Once you understand how to distribute the FE correctly, there will be no problem with updates. All you do is move the new FE to the server side distribution folder and the next time anyone opens their shortcut, the new FE is automagically downloaded.
There are two common methods. The more complicated version uses an Access database which is what you open. This db then installs the correct version and opens it and closes itself. The simpler, no code version uses a batch file. You can convert this to VBScript if you prefer.
Each of the two batch files contains only four instructions and no error checking. You can add some if you feel the need but it isn't necessary. If there is a problem, the user needs to call you to fix it.
Standard LAN version:
Code:
md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
start c:\DwgLog\DrawingLog.accdb
Citrix version:
Code:
md %USERPROFILE%\DwgLog
del %USERPROFILE%\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" %USERPROFILE%\DwgLog
start %USERPROFILE%\DwgLog\DrawingLog.accdb
Using this method will eliminate conflicts caused because of differences in the versions on your workstations.
One other thing I recommend is that you add two version tables. One to the FE and one to the BE. When the app opens, you compare the FE version to the BE version and if they match, open the menu or whatever you use as the initial form. If they are different, give the user a meaningful message and shut down without opening.
The setup procedure - ONE TIME:
1. Create the Batch file using the folder and database names for your application.
2. Store the Batch file in the server-side distribution folder.
3. Create a Shortcut that opens the batch file from the server folder.
4. Distribute the Shortcut to all users. Have them place it on their desktop for easy access.
5. Make sure they disable their previous shortcut and remove the file they currently open to avoid issues in the future.
To add a new user -
Add the shortcut on the new user's desktop.
To update the FE -
If the change is minor and did not include any BE changes, simply archive the FE file currently in the distribution folder and copy the new version. If I want the users to immediately switch to the new version because I made an important bug fix, I send them an email and ask them to shut down the app and reopen. There are other more complicated ways to do this. You may already use one. Otherwise, the next time they run the shortcut, they get the new version.
BE changes should ALWAYS be done while all users are offline. Once the BE has been linked to the new FE and tested, place the FE in the distribution folder and notify users they can access the app.
You got a lot of advice regarding having per-user copies of the FE file and one shared version of the BE file - but nobody told you WHY sharing the FE is so bad.
Access is designed to work using the Windows File and Printer Sharing protocol. (It is called SMB or Server Message Block.) This protocol supports having an application program write directly into the middle of a file opened using this protocol.
When you open a feature in the FE file and a query is involved, Access uses the opened FE file as the repository for the notes about what is to be altered. These notes (commonly referred to as "scratchpad" notes) are per-user. if you have more than one user in the FE file at the same time, they both are using identical Access algorithms that related to how those notes are stored. Both of these competing users will use the same algorithm and thus will try to choose the same area to expand in the file.
Windows has a rule... a HARD AND FAST rule - about inter-process isolation. Translation: User A and user B have different processes (tasks) touching the same file so they can't know about each other except through Windows file locking - which is NOT tracked to individual block levels within the file. And because of the parallel use of the Access algorithm, these two putative users A and B don't know about each other and so will try to modify the same part of the file at the same time.
This leads to the case of "the left hand not knowing what the right hand is doing." The technical name for that is "destructive interference" which is what happens when two code threads attempt to update the same area in a shared memory area but the updates will necessarily be different. SOMEONE is going to lose data. I.e. whoever writes last wins - if you call it winning. The implications of destructive interference could lead to database corruption making the file unusable to all potential users.
Having multiple FE files means you don't have two users opening the shared FE file. No destructive interference there. And if you use the standard methods for setting up the BE file, Access knows how to do that in a more orderly fashion. NOT perfect - but usually very good. I had an app that had 30-40 registered users and maybe 5-8 of them were online at once. With a per-user FE and a shared BE, I suffered corruption in the FE maybe once or twice a year due to power failures.
