现有的DAO代码是否可以在SQL Server上运行? [英] Will existing DAO code work against a SQL Server?

问题描述

如果我将数据从Access MDB传输到SQL Server，则VB应用程序中的DAO代码是否可以在SQL Server上使用.

If I transfer data from a Access MDB into a SQL Server, will DAO code in a VB app work against the SQL Server.

我意识到初始连接调用将需要更改，但是还有其他需要更改吗?

I realise there will need to be changes to the initial connection calls but will anything else need to change?

推荐答案

此处有许多问题.

1. 如果您在SQL Server的前端使用ADP，则您将不会使用DAO，因为ADP不使用Jet/ACE.然后，您将具有与SQL Server的直接ADO连接.

1. if you're using an ADP for your front end to SQL Server, you won't be using DAO, as you can't, since ADPs don't use Jet/ACE. You'll then have a direct ADO connection to the SQL Server.

但是，在过去的五年左右的时间里，MS一直使用ODBC而不是ADB推荐使用MDB/ACCDB(某些报告场景除外). A2007和A2010中的ADP没有更改，这可能表明MS正计划完全放弃它们(就像在A2002和A2003中没有更改之后对DAP所做的那样).但是，由于Access团队一直在积极寻求使用SQL Server的用户的输入，因此MS也可能计划在下一个版本的Access中恢复ADP.

However, for the last 5 years or so MS has been deprecating ADPs in favor of MDBs/ACCDBs using ODBC (except for some reporting scenarios). There have been no changes to ADPs in A2007 and A2010, which may indicate that MS is planning to abandon them entirely (as they did with DAPs after no changes in A2002 and A2003). But it may also be that MS is planning to revive ADPs in the next version of Access, since the Access team has been actively seeking input from those using SQL Server.

使用带有ODBC的推荐技术(MDB/ACCDB)(可能还有链接表)，您正在使用Jet/ACE，而逻辑数据接口是DAO(Jet/ACE的本机数据接口).

Going with the recommended technology (MDB/ACCDB) with ODBC (and, presumably, linked tables), you're using Jet/ACE, and the logical data interface is DAO, Jet/ACE's native data interface.

Jet/ACE实际上在处理服务器数据库时非常聪明，但是它确实会犯错误，并且某些经验不足的Access开发人员可能会编写某些类型的查询，这些查询将成为服务器数据库的性能指标(因为它们强制执行) Jet/ACE从服务器中提取整个表并在客户端工作站上完成所有工作-请参阅上面的@Philippe Grondier的答案.

Jet/ACE is actually pretty darned smart in dealing with a server database, but it does make mistakes, and there are certain types of queries that inexperienced Access developers might write that will be performance pigs with a server database (because they force Jet/ACE to pull the whole table from the server and do all the work on the client workstation -- see @Philippe Grondier's answer above).

从MDB/ACCDB通过ODBC使用SQL Server的通常方法是尝试使用Access方式，使用绑定表格和整个九码(与设计要与Jet/ACE后端)，然后使用SQL事件探查器确定哪些部分是性能瓶颈，应该对其进行重组，以便在服务器端进行适当的处​​理.

The usual approach to working with SQL Server via ODBC from an MDB/ACCDB is to try it the Access way, with bound forms and the whole nine yards (nothing different than if you were designing your app for use with a Jet/ACE back end), and then use SQL Profiler to determine what parts are performance bottlenecks and should be restructured so that appropriate processing takes place server-side.

经常需要明智地使用ADO，因为ADO在某些方面做得很出色，而DAO却做得不好或根本没有.

Judicious use of ADO is often warranted because there are certain things that ADO does brilliantly that DAO does poorly or not at all.

但是基本思想是使用与Jet/ACE后端相同的方法，因为Jet/ACE正在管理与服务器的接口.这意味着您不必担心Jet/ACE的SQL方言和服务器数据库的方言之间的差异，因为Jet/ACE和ODBC完全消除了这些差异.

But the basic idea is to use the same approach as you would with a Jet/ACE back end because Jet/ACE is managing your interface with the server. This means you don't have to worry about the differences between Jet/ACE's SQL dialect and your server database's dialect, because Jet/ACE and ODBC abstract those differences entirely away.

一些随机问题:

1. 对于DAO记录集，您需要添加dbSeeChanges选项.

1. for DAO recordsets, you need to add the dbSeeChanges option.

所有表都具有主键至关重要，否则您可能会有奇怪的屏幕更新.但是你们所有人的桌子都有PK，对吧?

it's crucial that all your tables have a primary key, or you may have weird screen updates. But all of you tables have PKs, right?

我建议最好在SQL Server的所有表中都放置一个timestamp字段，即使我从未明确使用它也是如此.这(与#2结合使用)确保刷新尽可能高效(ODBC可以检查时间戳，而无需将所有客户端字段与服务器端值一一比较).

I find it advisable to put a timestamp field in all tables on SQL Server, even if I never use it explicitly. This (in combination with #2) insures that refreshes are as efficient as possible (ODBC can check the timestamp instead of needing to compare all the client-side fields one by one to the server-side values).

如果使用直通查询或ODBCDirect，则需要担心服务器数据库的SQL方言，并保持Jet/ACE处理哪个SQL(并为您解释为后端方言).并直接进入服务器.

if you use passthrough queries or ODBCDirect, you'll need to worry about your server database's SQL dialect and keep straight which SQL is being handled by Jet/ACE (and interpreted for you into the back-end dialect) and which is going directly to the server.

Jet/ACE没有与bigint对应的数据类型，因此，如果在SQL Server表中将其用作PK，则需要以非标准方式进行处理. MS知识库中有解决此问题的文章.

Jet/ACE has no data type corresponding to bigint so if you use that as PK in a SQL Server table, you'll need to handle it in a non-standard way. The MS Knowledge Base has articles on working around this problem.

如果使用ADO，请记住ADO使用Access所说的"SQL 92兼容模式"，这表示SQL Server通配符和派生表语法.

if you use ADO, remember that ADO uses what Access calls "SQL 92 compatibility mode," which means SQL Server wildcards and derived table syntax.

这篇关于现有的DAO代码是否可以在SQL Server上运行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！