现有的 DAO 代码可以在 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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这里有很多问题。
如果您使用 ADP 作为 SQL Server 的前端，您将不会使用 DAO，因为 ADP 不使用 Jet/ACE。然后您将拥有与 SQL Server 的直接 ADO 连接。
但是，在过去 5 年左右的时间里，MS 一直在弃用 ADP，转而使用使用 ODBC 的 MDB/ACCDB（某些报告方案除外）。 A2007 和 A2010 中的 ADP 没有任何变化，这可能表明 MS 计划完全放弃它们（就像在 A2002 和 A2003 中没有变化后他们对 DAP 所做的那样）。但微软也可能计划在下一版本的 Access 中恢复 ADP，因为 Access 团队一直在积极寻求 SQL Server 用户的意见。
如果采用推荐的技术 (MDB/ACCDB) 和 ODBC（以及可能的链接表），您将使用 Jet/ACE，逻辑数据接口是 DAO，即 Jet/ACE 的本机数据接口。
Jet/ACE 在处理服务器数据库方面实际上非常聪明，但它确实会犯错误，并且缺乏经验的 Access 开发人员可能会编写某些类型的查询，这些查询将成为服务器数据库的性能猪（因为它们迫使 Jet/ACE从服务器中提取整个表并在客户端工作站上完成所有工作 - 请参阅上面 @Philippe Grondier 的答案）。
从 MDB/ACCDB 通过 ODBC 使用 SQL Server 的常用方法是尝试 Access 方式，使用绑定表单和整个九码（与设计与 Jet/ACE 后端一起使用的应用程序没有什么不同） ），然后使用 SQL Profiler 来确定哪些部分是性能瓶颈并且应该进行重组，以便在服务器端进行适当的处理。
明智地使用 ADO 通常是有必要的，因为在某些事情上 ADO 做得非常出色，而 DAO 却做得很差或根本不做。
但基本思想是使用与 Jet/ACE 后端相同的方法，因为 Jet/ACE 正在管理您与服务器的接口。这意味着您不必担心 Jet/ACE 的 SQL 方言和服务器数据库方言之间的差异，因为 Jet/ACE 和 ODBC 完全消除了这些差异。
一些随机问题：
对于 DAO 记录集，您需要添加 dbSeeChanges 选项。
所有表都有一个主键至关重要，否则您可能会出现奇怪的屏幕更新。但是所有的表都有 PK，对吧？
我发现建议在 SQL Server 上的所有表中放置时间戳字段，即使我从未明确使用过它。这（与 #2 结合）确保刷新尽可能高效（ODBC 可以检查时间戳，而不需要将所有客户端字段与服务器端值一一比较）。
如果您使用直通查询或 ODBCDirect，则需要担心服务器数据库的 SQL 方言，并清楚哪些 SQL 正在由 Jet/ACE 处理（并为您解释为后端方言）以及哪些 SQL 正在由 Jet/ACE 处理（并为您解释为后端方言）直接连接到服务器。
Jet/ACE没有与bigint相对应的数据类型，因此如果您在SQL Server表中将其用作PK，则需要以非标准方式处理它。 MS 知识库有关于解决此问题的文章。
如果您使用 ADO，请记住 ADO 使用 Access 所谓的“SQL 92 兼容模式”，这意味着 SQL Server 通配符和派生表语法。
如果
A number of issues here.
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.
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.
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 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).
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.
Judicious use of ADO is often warranted because there are certain things that ADO does brilliantly that DAO does poorly or not at all.
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.
A few random issues:
for DAO recordsets, you need to add the dbSeeChanges option.
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?
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).
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 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.
if you use ADO, remember that ADO uses what Access calls "SQL 92 compatibility mode," which means SQL Server wildcards and derived table syntax.
这完全取决于您如何与数据库进行交换：
It all depends on how you are exchanging with the database:
直接没有。但是您可以将当前访问表替换为 sql server 的链接表。
更新：如何为 Access 中的链接表创建与 SQL Server 的无 DSN 连接
Directly no. But you can replace current access tables with linked tables to sql server.
Update: How to create a DSN-less connection to SQL Server for linked tables in Access
不确定这是否适用于您的情况，但 Access 中的 SQL 语法（至少是旧版本）与 SQL Server 的语法略有不同，例如，旧版本的 Access 中的 LIKE 通配符使用 * 而不是使用的 %通过 SQL Server。
如果您在 VB 应用程序中使用 SQL 查询，则可能需要确保所有 SQL 语句均符合 SQL Server 语法。
我提到这一点主要是因为您提到了 DAO 和 VB（我假设是 VB 5/6 而不是 VB.NET），它们是相当旧的技术，并且 Access MDB 可能采用类似的旧格式。
Not sure if this applies to your scenario, but the SQL syntax in Access (at least the older versions) have slightly different syntax from SQL Server e.g the wildcard characters for LIKE in the old, old versions of Access use * instead of the % used by SQL Server.
If you use SQL queries in your VB app, you might need to ensure that all SQL statements are SQL Server syntax-compliant.
I mentioned this mainly because you mentioned DAO and VB (I'm assuming VB 5/6 rather than VB.NET) which are rather old technologies and the Access MDB might be in a similiarly old format.