While testing an Access file after restructuring I frequently flipped between credentials for the UID and PWD of the ODBC connection string, but something I did recently has caused Access to no longer show the full connection string.
I have 4 production pass-through queries and 1 test pass-through query. The test pass-through query shows the full ODBC connection string, but the 4 production pass-through queries show only ODBC;DSN=schema_name;. I tried restating the ODBC connection:
a) I click the three dots
b) The "Select Data Source" box opens
c) I select the data source
d) I choose "Yes" to save the password in the connection string.
Yet after this it still only displays the abbreviated connection string. What do I need to do to have MS Access display the full connection string?
Should the connection string UID and PWD always match that which is saved in the ODBC data source configuration box?
- Using Access 2010
- Saved in .mdb (Access 2000) format
- User DSN: MySQL ODBC 5.3 Unicode Driver
- System DSN: MySQL ODBC 5.3 ANSI Driver
The saving of the UID/password is determined how you FIRST created the table link. If you checked the “save password” in the connection string, then any re-linking and including the uid/password WILL BE saved and will be CLEARLY visible in the string.
If you did NOT check the box “save password” then re-linking and EVEN WHEN YOU include the uid/password, it will NOT be saved in the connection string. And thus if you have not logged into that server, then opening such a linked table will cause a logon or an odbc connection error. However, if you logged into that table, then access caches the uid/password for that session.
So “how” you created the link will determine if the uid/password is saved. The tabledef attribute that controls this setting is thus:
MyTableDef.Attributes = DB_ATTACHSAVEPWD
As a general rule it is MUCH better to NOT include the uid/password in those links. Best to execute a logon at the start of the application. This also means that you can use different uid/passwords and NOT have to re-link the tables. I don’t think that querydefs have this setting, but they will ALSO work without the uid/password if the user has logged on to the database.
So, to answer your question: An option (attribute) setting exists that will cause the uid/password to be saved, or not saved. This option OVERRIDES the cause when INCLUDE the uid/password in the connection string – it will be removed and NOT saved if the table def does NOT have the above attribute setting that allows saving of the uid/password in the connection string.
Here is a great article that explains how to have all your tables connect and work without having to include the uid/password in the connection. The “trick” involves simply executing a logon at the start of your application.
Power Tip: Improve the security of database connections
http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx
follow up:
As a general rule when you using Access with SQL server, you use linked tables. You then can continue to use Access as you always did. You can thus simply bind a form to that linked table to SQL server and thus you have forms and reports that work without the need for writing ANY code. So both tableDefs (linked ones) and querydefs have a connection property.
It looks like querydefs don’t have the “save password” attribute I outline above. This likely means that if you specify a DSN, then no password etc. is saved since it is expected in the DSN. So, try removing the DSN part – by doing this, you are creating a DSN less connection. Leaving out the DSN part should allow you to save/specify the uid/password in the string. I would also check for possible different behaviors if using a file or machine DSN (one uses a file, the other uses the registry).
