For years, I have been using MS-Access to connect to SQL Server databases using DSN's, and the process has always been painful & frustrating: do you create a User, System, or File DSN? Each has its advantages, but User / System requires a visit to each PC to config the DSN; In some cases having to log in with Admin credentials to make that happen. And a File DSN sits out there somewhere on the network which makes me nervous.
I had read about "DSN-less" connections but never explored that option until I used the SQL Server Data Migration Assistant (SSMA) to migrate the data from an Access database into SQL Server. After reviewing the wizard output. this approach is definitely easy to implement using a connection string.
Now, connection strings can also be scary; writing one from scratch can be a challenge because of so many options. But if we narrow the focus to MS-Access 2000-2003 connecting to SQL 2005, after you have one actually working you can easily modify it.
The basic connection string looks like the following. I am using a Global Constant My_Connection, and I've broken out each portion for clarity, but the actual string should be all on one line:
Global Const My_Connection =
"ODBC;
DRIVER=SQL Native Client;
SERVER=my_server;
UID=my_username;
PWD=my_password;
APP=my_appname;
WSID=my_workstation;
DATABASE=my_database;"
There is some code, not shown here, which refers to the My_Connection string & then loops thru the tables to update the connections.
The approach works just fine if you are using SQL Server Authentication. We decided that this would be the easiest to manage since we don't have to maintain all the various windows ID's; plus, when we grant permissions we can simply say "GRANT SELECT ON dbo.my_tablename TO my_username". The application calls a Windows API to get the network ID and this controls who gets to do what within the app itself.
Now, if you have multiple SQL instances installed, e.g. if the development instance is called DEV you only need to change SERVER = my_server\DEV and it works fine; just remember to also change the my_username and my_password since those are usually different for the production servers.
One more little trick - if you are working remotely using a VPN connection, because Windows uses nslookup to find the IP address for the server name, this can cause a timeout & prevent you from connecting. The solution here is to replace the my_server_name with the actual IP address, e.g. if your server name is my_server and the IP adress is 192.168.1.2 then you can substitute that IP address for my_server. For a named instance it would be 192.168.1.2\DEV.
In our case, each of the possible connection strings to the various production, QA, DEV, and local testing database are written in plain text in a standard module; so after we run the "relink code" we delete all those strings and add a line Global Const My_Connection = "" so all the sensitive link data is removed, being replaced by a do-nothing statement that only exists to prevent compile errors.
