使用 Ms Access ...
我试图更改从 Access 连接到 SQL Server 数据库的用户。我的 Access 应用程序的启动表单通过 Microsoft 知识库上发布的例程的更改版本动态连接到表。在 Essence 中，它会删除与您调用它的名称 localName 相匹配的 tabledef，调用 Currentdb.CreateTableDef 以使用适当的新连接字符串创建一个新表，并将其附加到 CurrentDb.TableDefs 集合中。据我所知，它是实际联系数据库的附加函数，如果您的连接字符串错误，则会抛出错误。
就我而言，我对数据库中的所有表进行了原始传递，使用旧的（标准化的 - 即我通常使用这个）字符串调用此例程，并且所有表都连接良好。
我现在正在尝试使用新的用户/密码组合第二次通过此启动表单，以便测试数据库中的不同权限。
然后，我使用修改后的名称和密码构建了一个新的连接字符串，目前正在单步执行我刚才描述的函数。
我有一个变量 td，其中包含新创建的 tabledef，我就在将其附加到 Currentdb.TableDefs 集合之前
在调试器“立即”窗口中 ?td.connect 给出了带有新创建的连接字符串正确形成连接字符串，并且 ?td.name 给出 tblConfig 作为表名称。
为了确保 TableDef 已从当前集合中正确删除，我从立即窗口中执行了 ?CurrentDb.Tabledefs("tblConfig").Connect 操作，并收到一个对话框，显示该集合没有' t 包含具有该名称的成员
，然后单步跨过该行
CurrentDb.TableDefs.Append td
现在从调试器立即窗口再次调用 ?CurrentDb.Tabledefs("tblConfig").Connect ，响应是旧的连接字符串不是新的。
该例程中有一个 OnError 处于活动状态，但在单步执行期间并未激活。
我能想到的唯一解释是，由于在某个时刻保存数据库，旧的连接字符串以某种方式存储在磁盘上，并且当最终附加时，附加不会在连接字符串中进行复制。
有人有更好的解释吗？
Using Ms Access ...
I am trying to change the user to which I am connected to a SQL Server Database from Access. The startup form from my Access Application dynamically connects to the tables via an Altered version of a routine posted on Microsoft's KB. In Essence it deletes the tabledef that matches the name localName you call it with, Calls Currentdb.CreateTableDef to create a new one with the appropriate new connection string and appends it the the CurrentDb.TableDefs collection. As far as I can see, its the append function that actually contacts the database and throws an error if your connection string is wrong.
In my case, I did an original pass through all the tables in my database calling this routine with an old (standardised - ie I normally use this) string and all tables connect fine.
I am now trying a second pass through this start up form with a new user/password combination so as to test different permissions in the database.
I have then built a new connection string with a revised name and password and am currently single stepping through the function I just described.
I have a variable td which contains the newly created tabledef and I am just prior to appending it to the Currentdb.TableDefs collection
In the debugger "Immediate" window ?td.connect gives the connection string with the newly formed connection string correctly and ?td.name gives tblConfig as the table name.
Just to ensure the TableDef has been correctly deleted from the current collection I did ?CurrentDb.Tabledefs("tblConfig").Connect from the immediate window and was rewarded with a dialog box that said the collection didn't contain a member with that name
I then single step over the line
CurrentDb.TableDefs.Append td
And now call ?CurrentDb.Tabledefs("tblConfig").Connect again from the debuggers Immediate window and the response is the old connection string not the new one.
There is an OnError active in this routine, but It is not activated during the single stepping.
The only explanation I can think of for this, is that somehow the old connection string is stored on disk as a result of saving the database at some point and append doesn't copy across the connectionstring when it is finally appended.
Does anyone have a better explanation?
发布评论
评论（2）
事实证明，连接字符串确实被缓存了
http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx
It turns out that the connection string is indeed cached
http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx
我的新连接字符串密码不正确（一个字符错误），并且当该问题发生时，访问似乎不会存储新的用户名和密码。
不知怎的，尽管用旧的用户名和密码删除了旧的 tabledef，Access 仍然能够取回它 - 也许它被缓存了，我不知道 - 并使用它。显然这是有效的，所以它继续使用那根绳子而不是新的绳子。
似乎因为旧字符串有效，所以它不会抛出错误，所以我现在使用不具有与我预期相同的权限的服务器登录名连接到表。
I had an incorrect password for the new connection string (one character was wrong) and it appears that access does not store the new username and password when that problem happens.
Somehow, despite deleting the old tabledef with the old username and password, Access was able to get it back - maybe it was cached, I don't know - and use it. Obviously that worked and so it carried on with that string rather than the new one.
It seems that because the old string worked, it didn't bother to throw an error, so I was now connected to the table with a server login that didn't have the same permissions as I expected.