将currentproject.connection与连接字符串用于ADO对象之间有什么区别? [英] What is the difference between using currentproject.connection vs connection strings for ADO object?

问题描述

我们可以使用currentproject.connection或使用DSN(DSN=MyDSN;UID=MyID;PWD=MyPwd)或DSN-Less连接字符串来设置ADO连接的连接字符串(

We can set up the connection string for ADO connection using either currentproject.connection or using a DSN (DSN=MyDSN;UID=MyID;PWD=MyPwd) or DSN-Less connection string(

DRIVER=\{SQL Server\};SERVER=
MyServer;DATABASE=pubs;
UID=MyID;PWD=MyPwd)

我的问题特别是在链接表连接到SQL Server的情况下.

My question is specifically in the case of linked tables connected to SQL Server.

我有一个连接到SQL Server后端的无DSN链接表.我使用了

I have a DSN-less linked tables connected to SQL Server backend. I used this code provided by microsoft to create the DSN-less connection. If I do

debug.print currentproject.connection我得到类似

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\DAVE\Desktop\DATA.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\Users\DAVE\AppData\Roaming\Microsoft\Access\System3.mdw;Jet OLEDB:Registry Path=Software\Microsoft\Office\16.0\Access\Access Connectivity Engine;Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=True;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

我想，上面的字符串将被转换为无dsn的连接字符串，如下所示:

I guess, the above string is converted into a dsn-less connection string looking like this :

DRIVER=\{SQL Server\};SERVER=
MyServer;DATABASE=pubs;
UID=MyID;PWD=MyPwd)

考虑到这种转换速度，使用连接字符串比使用currentproject.connection更快吗?

Accounting for this conversion speed, is it faster to use a connection string rather than currentproject.connection?

推荐答案

CurrentProject.Connection是与当前Access数据库的预先初始化且永远存在的连接.

CurrentProject.Connection is a pre-initialized and ever present connection to the current Access database.

但是，使用ODBC连接字符串，OLEDB连接字符串或ODBC DSN的连接可以直接连接到外部数据源，从而减少开销并使用SQL方言，外部系统的选项和怪癖.

Connections using ODBC connection strings, OLEDB connection strings or ODBC DSNs, however, can connect directly to external data sources, reducing overhead and using the SQL dialect, options and quirks of the external system.

CurrentProject.Connection可以使用链接表，但是如果使用链接表，它实际上使用将Access连接到SQL Server的DAO连接(链接表始终使用DAO，因为Access在内部使用DAO)，然后使用ADO连接. Access数据库从Access检索数据，导致额外的开销.

CurrentProject.Connection can make use of linked tables, but if it does, it actually uses a DAO connection connecting Access to the SQL server (linked tables always use DAO since Access uses DAO internally), and then an ADO connection to the Access database to retrieve the data from Access, causing additional overhead.

这意味着在连接到外部源时，使用SQL Server ODBC驱动程序或OLEDB提供程序的连接在某些情况下比使用CurrentProject.Connection的连接要快得多.另外，使用ODBC驱动程序或OLEDB提供程序的直接连接可以公开其他功能，例如从SQL Server返回信息消息.

This means that connections using either the SQL server ODBC driver or OLEDB provider can be substantially faster for specific cases than those using CurrentProject.Connection when connecting to external sources. Also, direct connections using the ODBC driver or OLEDB provider can expose additional functionality, such as returning information messages from SQL server.

进一步详细说明发生的情况:

To elaborate further on what happens:

如果使用CurrentProject.Connection打开链接表，则会发生以下情况:

If you use CurrentProject.Connection to open a linked table, the following happens:

1. 您使用已经打开的与Access数据库的连接
2. 您使用SQL的方言将SQL命令发送到Access数据库(使用与SQL Server兼容的语法模式的JET/ACE SQL)
3. Access标识应查询的表，标识已链接的表，并将SQL语句转换为适当的语言
4. 访问要么打开与SQL Server的新(内部/DAO)连接，要么使用现有的打开连接(如果存在)
5. Access使用它决定的连接和翻译后的语句将数据从SQL Server提取到Access数据库中
6. 然后，Access将数据发送到ADODB连接.

但是，如果您使用直接ADODB连接，则该过程要简单得多:

If you use a direct ADODB connection, however, the process is quite a lot more simple:

1. 您打开与SQL Server的新ADODB连接
2. 您将SQL命令发送到SQL Server
3. SQL Server使用ADODB连接将数据发送回给您

Access数据库引擎100％不知道您在此处查询任何内容，因为它不涉及任何步骤.

The Access database engine is 100% unaware that you're querying anything here, since it's not involved in any of the steps.

更多说明:

• 如果使用CurrentProject.Connection和服务器端游标，则即使查询SQL Server，游标还是由Access数据库引擎而不是SQL Server管理.

• If you use CurrentProject.Connection and a server-side cursor, the cursor is managed by the Access Database Engine, not by SQL server, even though you're querying SQL server.

如果使用CurrentProject.Connection，则必须使用与SQL Server兼容的语法使用Access(JET/ACE)SQL.这意味着在LIKE语句中，您需要使用%作为通配符，并且具有与使用常规语法不同的功能集，但是在定界调用时仍然必须使用八叉索，并且Access仍会处理SQL语句，不是SQL Server.

If you use CurrentProject.Connection you have to use Access (JET/ACE) SQL using SQL server compatible syntax. This means that in LIKE statements, you need to use % as wildcard, and you have a different feature set than using the normal syntax, but you still have to use octothorpes when delimiting calls and the SQL statement is still processed by Access, not SQL server.

作为一般语句，与SQL Server的直接连接比使用CurrentProject.Connection通过Access进行连接要快，但可能会出现例外情况(例如，因为Access已经有一个开放的连接，而您只执行了一个小语句，因此建立新连接的相对开销很大)

While as a general statement, a direct connection to SQL server is faster than connecting through Access using CurrentProject.Connection, exceptions may apply (for example, because Access already has an open connection and you're only executing a small statement so the relative overhead of establishing a new connection is large)

这篇关于将currentproject.connection与连接字符串用于ADO对象之间有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！