MS Access在属性表中不再显示完整的ODBC连接字符串 [英] MS Access no longer shows full ODBC connection string in property sheet

问题描述

在重组后测试Access文件时，我经常在ODBC连接字符串的UID和PWD的凭据之间切换，但是最近我做过的事情导致Access不再显示完整的连接字符串.

我有4个生产通过查询和1个测试通过查询.测试传递查询显示完整的ODBC连接字符串，但是4个生产传递查询仅显示ODBC;DSN=schema_name;.我尝试重新建立ODBC连接:

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.

在此之后，它仍然仅显示缩写的连接字符串.要使MS Access显示完整的连接字符串，我该怎么做?

连接字符串UID和PWD是否应始终与ODBC数据源配置框中保存的字符串匹配?

• 使用Access 2010
• 以.mdb(Access 2000)格式保存
• 用户DSN:MySQL ODBC 5.3 Unicode驱动程序
• 系统DSN:MySQL ODBC 5.3 ANSI驱动程序

解决方案

UID/密码的保存取决于您首先创建表链接的方式.如果您在连接字符串中选中了保存密码"，那么任何重新链接以及包括uid/密码的操作都将被保存，并且在字符串中将清晰可见.

如果未选中保存密码"框，则重新链接，甚至在包含uid/密码时，也不会将其保存在连接字符串中.因此，如果您尚未登录该服务器，则打开这样的链接表将导致登录或odbc连接错误.但是，如果您登录到该表，则访问将为该会话缓存uid/密码.

因此，创建链接的方式"将确定是否保存了uid/密码.因此，控制此设置的tabledef属性为:

MyTableDef.Attributes = DB_ATTACHSAVEPWD

通常，最好不要在这些链接中包含uid/密码.最好在应用程序启动时执行登录.这也意味着您可以使用不同的uid/密码，而不必重新链接表.我不认为querydefs具有此设置，但是如果用户登录到数据库，它们也将在没有uid/password的情况下工作.

因此，要回答您的问题:存在一个选项(属性)设置，该设置将导致保存或不保存uid/密码.当表def没有上述允许将uid/密码保存在连接字符串中的属性设置时，此选项会覆盖连接字符串中的uid/密码时的原因-如果表def不具有允许在连接字符串中保存uid/密码的属性设置，它将被删除而不保存.

这是一篇很棒的文章，解释了如何使所有表连接和工作而不必在连接中包含uid/password. 技巧"仅涉及在应用程序开始时执行登录.

电源提示:提高数据库连接的安全性

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).

这篇关于MS Access在属性表中不再显示完整的ODBC连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！