Will existing DAO code work against a SQL Server?

Question

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?

Answer 1

A number of issues here.

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.

2. 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.

3. 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:

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

2. 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?

3. 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).

4. 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.

5. 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.

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