Hello
I have been developing applications for several years in the form of front-end on the users' local PCs and back-end on a server using the splitting feature included in MS Access. I need to migrate an application BE to a MS SQL Server back-end. There's no problem converting my JET back-end to MS SQL Server. Then, I was able to create links with the tables via an ODBC connection.
My difficulty lies in the distribution for many users; how can I automate the ODBC connection? Is there a method via VBA or other?
Thanks
I have relied on the J Street Developer Downloads in arnelgp's link for many years.
With regard to distribution for many users, though, there are a few things to consider.
First, once you migrate the data to SQL Server and create the ODBC connection in the Access FE, you deploy that Access FE to each user's computer with the ODBC connection in it. I don't see any "automating" required for that deployment. What could happen down the road is that you decide to make changes in one or more tables in the SQL Server database, or add new views, or remove views or tables. At that point, you would need to update the ODBC connections for those new or modifying data sources. Day to day, though, the linked tables (and views) should not need to be relinked.
In some deployments, I understand that some developers do refresh links regularly, so I'm not saying it's one way or the other, just that a stable deployment doesn't necessarily need to have ODBC connections automated for normal operation.
One thing you do have account for is the ODBC driver used. There are strong reasons to use the latest driver, which is
ODBC Driver 18. But that also means it must be installed on the computer of every user who will work with your Access FE. And that means a visit to each workstation to install it. Of course, if you have IT support to do that, it's not an onerous task. That's a one time installation, though.
Second, it's true that the mechanics of migrating Access tables to SQL Server and connecting them up are pretty straightforward. However, the performance can be quite disappointing if one has not taken sufficient care to redesign the interface to work in a client-server configuration.
Before deploying this to a number of users, I suggest a solid round of acceptance testing with one or two key users who can provide immediate feedback on your design.
