现有的 DAO 代码是否适用于 SQL Server?

2022-01-23 00:00:00 ms-access migration sql-server dao

如果我将数据从 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. 如果您将 ADP 用于 SQL Server 的前端，那么您将不会使用 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.

但是，在过去 5 年左右的时间里，MS 一直在弃用 ADP，转而支持使用 ODBC 的 MDB/ACCDB(某些报告方案除外).A2007 和 A2010 中的 ADP 没有任何变化，这可能表明 MS 计划完全放弃它们(就像他们在 A2002 和 A2003 没有变化后对 DAP 所做的那样).但也可能是 MS 计划在下一版本的 Access 中恢复 ADP，因为 Access 团队一直在积极寻求使用 SQL Server 的人的意见.

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.

使用推荐的技术 (MDB/ACCDB) 和 ODBC(可能还有链接表)，您使用的是 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 Profiler 确定哪些部分是性能瓶颈并应进行重组，以便在服务器端进行适当的处​​理.

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 上的所有表中放置一个时间戳字段，即使我从未明确使用它.这(结合#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.

相关文章