sql server - How can I programmatically create a DSN?

Question

I have an Access Form that uses a linked sql server table as a datasource. I will need to distribute this file to other users soon and I need a way to programmaticly install the DSN to their machines. This is the process of manually setting up the link:

External Data > More > ODBC Database > Link to data source > Machine data source tab > press new > user data source > sql server > name=up to you; server= serverName > How should SQL server verify the autheticity of the login ID? With windows NT authentication using the network login ID > Attach database File Name (database name) > choose the table and press ok

That is what I did to access my table but I would like it so that the user can press a button and get access to the table and at the same time be authenticated by using windows NT authentication.

I am having trouble finding a way to write this in access vba code can someone direct me in the right direction?

Answer 1

As a general rule you find MUCH better success by using a DSN less connection. This will eliminate many issues and problems. How to use a DSN less connection is outlined here:

http://www.accessmvp.com/DJSteele/DSNLessLinks.html

And also you do NOT want to store the user name + password in the connection string, but only “log on” one time. Again this saves huge hassles and also means your connection strings and/or DSN does not have to save and expose the user name and password in the actual links.

And this approach means you can have different logons and NOT have to re-link or change existing table links.

The follow shows how to use a cached logon and this thus allows one to have different logons without having to re-link your tables.

https://blogs.office.com/en-us/2011/04/08/power-tip-improve-the-security-of-database-connections/

I highly recommend you adopt both of the above approaches when using linked tables to SQL server.