访问缓存 ODBC 连接设置 [英] Access Caching ODBC connection settings

问题描述

我的公司大量使用 SAI，为了从该系统创建报告，我们使用 ODBC 连接到数据库.我们目前的设置是区域性的，因此我们为每个区域设置了单独的环境.当尝试使用 MS ACCESS 访问这些环境时，我发现我无法链接到不同环境中的表并检索准确的数据.

My company uses SAI heavily and in order to create reports from this system we use ODBC to connect to the DB. Our setup at the moment is regional and thus we have separate environments for each region. When trying to access these environments using MS ACCESS I am finding that I cannot link to tables in different environments and retrieve accurate data.

我可以使用特定的 DSN 创建指向各种环境的链接.然后我可以访问数据，但是我打开的第一个表，无论它属于哪个区域，都可以正常工作.但是，在那之后，所有表，无论它们属于哪个区域，都使用来自打开的第一个表的 DSN 设置.如果我关闭 Access 并重新启动，我可以从另一个环境中的数据开始，然后可以工作，但是其余的数据将反映这些 DSN 设置.此外，当我查看属性表时，DSN 设置是应该的.

I can create the links to various environments using the specific DSN's. I then can access the data, however the first table that I open, no matter the region it belongs to, works fine. After that, however, all of the tables, regardless to the region they belong to, all use the DSN settings from the first table that was opened. If I shut Access down and restart I can then start with data from another environment which will then work, however the rest of the data will then reflect those DSN settings. Additionally, when I look at the property sheet the DSN settings are as they should be.

我还要补充一点，每个区域的表都具有相同的名称.IE销售额表是所有环境的销售额，生产表是所有地区的生产表.因此，当我从多个环境链接到 sales 表时，需要在 MS ACCESS 中重命名.

Let me also add that the tables for each region all have the same name. IE the sales table is sales in all environments, the production table is the production table in all regions. As a result as I link to the sales table from multiple environments the need to be renamed in MS ACCESS.

推荐答案

我怀疑你遗漏了一些我们需要的细节.

I suspect you leaving out a few details that we need.

当您打开第一个表时，系统会提示您登录吗?(这是关键信息).如果您使用不同"链接并在这些链接中保存用户 ID/密码，那么您将不会收到任何 ODBC 提示，因此您可以轻松地处理多个区域.

When you open that first table, are you prompted for a logon? (this is critical info). If you using "different" links and saving the userid/password in those links, then you should not get any ODBC prompts and you can thus easily work with more than one region.

但是，听起来您有一组链接，并且想要重新指向/重新链接到不同的服务器.这可以工作 - 但如果您看到/允许 ODBC 提示，则无效.

However, it sounds like you have one set of links, and want to re-point/re-link to a different server. This can work - but NOT if you seeing/allowing ODBC prompts.

如果您在链接中包含用户 ID/密码，那么您应该能够重新链接(切换)到任一系统.但是，当您执行此操作时，两个 uid/密码组合都将同时激活.

If you ARE including the userid/password in the links then you should be able to re-link (switch) to either system. However when you do this, then BOTH uid/password combos ARE and WILL be active at that same given time.

事情很糟糕的地方是，如果您以错误的登录重新链接，则将使用以前的 uid/密码！事实上，如果您测试登录(即使是错误的登录！)，则将使用第一个合法登录！归根结底，这意味着这里的弱点是当/如果您要求登录，即使登录不好，它也会为合法登录返回是"！(因为 Access 将恢复使用以前的合法登录)您必须处理此问题.

Where things go VERY wrong is that if you re-link with an incorrect logon then the previous uid/password WILL be used! And in fact if you TEST for a logon (even a bad one!), then the first legal logon will be used! At the end of the day, this means the weak point here is WHEN/if you ask for a logon, it WILL return "yes" for a legal logon EVEN if the logon is bad! (because Access will revent to the previous legal logon used) You MUST deal with this issue.

很可能是因为您的代码在重新链接之前进行了登录测试".我的建议是您的测试"登录代码返回 OK，然后您执行传递查询以返回数据库名称 - 如果该数据库名称/服务器错误，则拒绝该登录并且不要重新链接.

So likely things point to your code that does a logon "test" before re-link. What I would suggest is that your "test" logon code returns OK, you THEN execute a pass-through query to return the database name – if that database name/server is wrong, then you reject that logon and do NOT re-link.

对于新登录/服务器的测试方式如此重要?而且您绝对不希望出现 ODBC 登录提示——因为如果用户取消或输入错误的登录信息，那么您的重新链接代码将使用之前缓存的登录信息.

So critical here is how you are testing for the new logon/server? And you most certainly NEVER want the ODBC logon prompt to appear – since if a user cancel, or enters the wrong logon, then your re-link code will use the previous cached logon.

您应该能够重新链接相同的给定表集并将它们指向另一台服务器 - 但您需要确保您使用的登录确实有效.

You should be able to re-link the same given set of tables and point them to another server – but you need to ensure that the logon you used did in fact work.

最后但并非最不重要的:Access 始终使用无 DSN 的连接.唯一的例外是如果您使用的是系统 DSN.因此，当您创建文件 DSN 并重新链接时，将忽略且不使用该点的 DSN.(这允许您说将应用程序分发到其他桌面而无需复制/包含 DSN).所以实际上你几乎总是使用无 DSN 的连接，如果你不是，那么我建议你转储系统 DSN，因为 Access 不能使用来自此类系统 DSN 的用户/密码——即使你在其中包含用户/密码DSN 它仍然被忽略.

Last but not least: Access ALWAYS uses a DSN less connection. The ONLY exception is if you are using a system DSN. So when you create a file DSN, and re-link then the DSN FROM THAT point on is ignored, and not used. (this allows you to say distribute the application to other desktops without having to copy/include a DSN). So in effect you near always using a DSN-less connection and if you are not, then I suggest you dump the SYSTEM DSN’s, since Access cannot use the USER/password from such system DSN’s – even if you include the USER/password in that DSN it is STILL ignored.

此外，当您重新链接时，您是否在使用 dbAttachSavePWD – 您不应该必须这样做，但我会在测试时将其包含在内.

Also when you re-link, are you using the dbAttachSavePWD – you should not have to, but I would for testing include it.

如果您正在使用/允许 ODBC 驱动程序提示用户登录 - 那么您必须消除这种情况并确保您的代码进行登录.

If you are using/allowing the ODBC driver to prompt the user for a logon – then you have to eliminate this and ensure your code does the logon.

这篇关于访问缓存 ODBC 连接设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！