different versions of ODBC driver on computers on network ok? (1 Viewer)

[cagilbert12]

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?

 

[sonic8]

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.

 

[cagilbert12]

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.

 

[cagilbert12]

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.

P.S. My clients are generally smaller shops, max 10-20 stations.

 

[The_Doc_Man]

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.

 

[cagilbert12]

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)?

 

[tvanstiphout]

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.

 

[cagilbert12]

thanks.

 

[The_Doc_Man]

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.