Will existing DAO code work with SQL Server?

Question

If I transfer data from MDB Access to SQL Server, will the DAO code in the VB application work with SQL Server.

I understand that there must be changes for the initial connection calls, but something else needs to be changed?

Answer 1

A few problems here.

• If you use ADP for your front-end SQL Server, you won’t use DAO as you cannot, because ADPs do not use Jet / ACE. After that, you will get a direct ADO connection to SQL Server.

• However, over the past 5 years, MS has rejected ADP in favor of MDB / ACCDB using ODBC (with the exception of some reporting scenarios). Changes in ADP in A2007 and A2010 have not changed, which may indicate that MS plans to completely abandon them (as was the case with DAP after changes in A2002 and A2003). But it can also happen that MS plans to revitalize ADP in the next version of Access, as the Access team is actively seeking information from those using SQL Server.

• According to the recommended technology (MDB / ACCDB) with ODBC (and presumably related tables), you are using Jet / ACE, and the logical data interface is the DAO, Jet / ACE data interface.

Jet / ACE is actually pretty damn smart at working with the server database, but it makes mistakes, and there are certain types of queries that inexperienced Access developers can write, these will be performance pigs with the server database (because they force Jet / ACE to pull the entire table from the server and do all the work on the client workstation - see @Philippe Grondier answer above).

The usual approach to working with SQL Server through ODBC from MDB / ACCDB is to try the access path with the associated forms and as many as nine yards (nothing except that you developed the application for use with Jet / ACE), and then use SQL Profiler to determine which parts are performance bottlenecks and should be rebuilt so that the appropriate processing is done on the server side.

The wise use of ADO is often justified, because there are certain things that ADO does brilliantly, that the DAO does poorly or does not work at all.

But the main idea is to use the same approach as with the back of Jet / ACE, because Jet / ACE controls your server interface. This means that you don’t have to worry about the differences between the Jet / ACE SQL dialect and the server database dialect, since Jet / ACE and ODBC completely reject these differences.

A few random issues:

• for DAO record sets, you need to add the dbSeeChanges option.

• It is important that all your tables have a primary key, or you may have strange screen updates. But all of you tables have PK, right?

• I find it appropriate to put the timestamp field in all tables on SQL Server, even if I will never use it explicitly. This (combined with C # 2) ensures that updates are as efficient as possible (ODBC can check the timestamp, rather than comparing all client fields one by one with the values ​​on the server side).

• if you use end-to-end queries or ODBCDirect, you will need to worry about the SQL dialog box on the database server and support direct SQL management using Jet / ACE (and interpreted for you in the internal dialect) and which goes directly to the server.

• Jet / ACE does not have a data type that matches bigint, so if you use it as a PC in a SQL Server table, you will have to process it in a non-standard way. The MS Knowledge Base has articles on working on this issue.

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

Answer 2

It all depends on how you communicate with the database:

• You use SQL statements such as "INSERT INTO bla (...)", either in your code or in access requests: you need to check if your code is SQL compatible. There are many Access functions (or should I say Jet?), Such as isnul (), that need to be reinterpreted in SQL
• You manage DAO record sets for updates, inserts, and deletes. After the recordset is opened using the correct SELECT statement (see Previous ...), the correct connection string and the correct authorizations on the server, you should be fine.

Answer 3

Not directly. But you can replace the current access tables with linked tables on the sql server.

Update: How to create a DSN-less connection to SQL Server for linked tables in Access

Answer 4

Not sure if this applies to your scenario, but the SQL syntax in Access (at least in older versions) has slightly different SQL Server syntax, like wildcards for LIKE in older older versions of Access use * instead of%, used by SQL Server

If you use SQL queries in your VB application, you may need to make sure that all SQL statements are syntactically compatible with SQL Server.

I mentioned this mainly because you mentioned DAO and VB (I assume VB 5/6, not VB.NET), which are pretty old technologies, and Access MDB might be in a similar old format.