Hello all, I use Access for the front end and am using SQL Server 2016 for the back end. I am ready to start testing a few things on another computer, but I am not able to connect to the server on the other computer. I did not install the driver on that computer because I am looking for a way to create a connection to the server without having to go to all the computers in the plant to install a driver.
Is it possible to do this via code? A bat file maybe?
Can this be done (Active Directory environment) by the IT admin as some sort of group update/setting?
Thanks for your advice.
does your company have Software center?
I would go with some sort of a deployment exe bundler.
how many users?
Are you sure all users have same operating system
all users have same ms office version?
I am not familiar with Software center.
We would have somewhere around 40 users on my last count.
I believe all concerned machines have the same OS and Office versions.
The generic driver did not work, which is what I found on my sample PC.
Deployment bundler: can you give me more info on what this would contain and how I would go about it?
Also, I did hear from another source that it may be possible for the IT staff to install the appropriate driver(s) remotely, so I hope that is the case. But, I am still not sure if I would need to go around to each machine and re-link the tables with the DSN file.
yeah I would go with your Sys Admin route or whoever does company wide deployments. Maybe they can deploy this driver via AD group policy, logon script etc. Whatever choice you make, think of it in terms of scale. For this some sort of automation is the way to go. Scale: Can I do this if I had 100 users, 200, 300 etc.
I had to come up with a similar solution over a year ago but it wasn't feasible to manually go to each system and install the drivers. Especially when systems are replaced etc.
After hours of digging through solutions on the Internet, I came up with a solid solution for our network setup.
Inside Access, I created a few Functions that run when someone opens the Access Front End.
The autoexec Macro contains connection information which includes a password.for the database connection. This doesn't appear to be a problem since the users run a complied version of the Access Database.
The first Function that runs in the autoexec Macro, copys DSN information from a network location to the Users C:\Users\username\Documents folder.
I have two databases that need connections to, so I have a two DSNConnection Functions, which are copied to the Users Documents folder next.
Finally, the autoexec Macro launches the Form that the users see.
After clicking on the app shortcut, an MS Access Security Notice window pops up and the users acknowledge the "Risk". From there the app opens.
If you are interested in going this route, it will take me some time to document a generic version of the Functions, autoexec Macro and the network paths.