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?
A:
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
volody
2010-05-18 14:54:30
What is wrong with using DAO to connect to the SQL Server directly?
Craig Johnston
2010-05-18 15:01:15
Dao is not optimized for multiuser/remote database environment
volody
2010-05-18 15:26:50
Can you elaborate?
Craig Johnston
2010-05-18 16:06:09
There are not so many information on how to resolve issues when use DAO with SQL Server, as well DAO support only 'always on' connections. If you will use only select and inserts then probably is ok, otherwise i would sujest to switch to ADO.Net. I have seen convertors for MSAcess to .net application
volody
2010-05-18 16:42:20
If you think DAO is not optimized for SQL server, just wait till you have multiple users connecting to the same mdb file with tables linked to SQL Server.
Jeff O
2010-05-18 21:32:48
You can't use SQL Server with DAO directly (as you can with ADO), but only via ODBC. And Jet (i.e., the engine underlying DAO) understands ODBC quite well, so there's no real danger in using DAO with ODBC linked tables (or specifying an ODBC connect string in your SQL statement).
David-W-Fenton
2010-05-18 22:04:06
DAO does not control multi-user issues -- Jet/ACE does, and Jet/ACE is actually quite a good "citizen" when working with server databases, as long as you don't ask it to do something it can't do (see @Philippe Grondier's answer above). For instance, if you write a bulk INSERT statement, Jet breaks it down and sends it one statement at a time. This is a performance dog on the client side, but allows the server to interleave the individual commands without requests from other users, instead of tying up the server for a bulk insert.
David-W-Fenton
2010-05-18 22:06:03
A:
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.
Mr Roys
2010-05-18 15:05:54
DAO is old in terms of being long-lived, but it's still in active development, getting significant improvements in A2007 and A2010.
David-W-Fenton
2010-05-18 22:06:54
Thanks for the heads-up, David. Wasn't aware of that really, ported a VB6 app accessing MDB via DAO/JET 3.5 to ADO and then finally to SQL Server maybe five years back. Didn't know it still exists, thought it was deprecated in favour of ADO back then.
Mr Roys
2010-05-19 01:55:48
DAO may have been deprecated back in Access 2000 days but now ADO has been depreceated by MS and DAO is back in. Dizzy yet?
Tony Toews
2010-05-19 03:35:47
No, DAO!=ACE. ACE is the new name for Jet 5. DAO is the nicely organized data interface library that allows you to control Jet/ACE programatically.
David-W-Fenton
2010-05-20 23:29:36
Many of us who understood the issues involved ignored MS's deprecation of DAO from the beginning, as it was obvious that ADO was not the right choice when you were working with Jet data. The result of that was that a whole lot of us avoiding going down the blind alley of converting code to ADO. It's important not to trust MS's recommendations and ask yourself whether they serve MS's purposes more than they serve your own immediate programming needs. In the case of ADO and Access, it was clearly driven entirely by MS's larger agenda, and not be the needs of Access developers and users.
David-W-Fenton
2010-05-20 23:30:27
The one place where ADO has been an advantage in Access (and likely in VB6, as well) is when working with SQL Server, seems to me. But all of that is superseded today by the .NET technologies, and ADO.NET is the replacement for classic ADO, which is, effectively, dead, except for niche uses.
David-W-Fenton
2010-05-20 23:32:48
+1
A:
It all depends on how you are exchanging with the database:
- You are using "SQL type" instructions, like "INSERT INTO bla(...)", either in your code or in access queries: you'll have to check that your code is SQL-Compliant. There are many Access (or shall I say Jet?) functions like isnul() that have to be reinterpreted in SQL
- You are manipulating DAO recordsets for updates, inserts and deletes. Once the recordset is open with the right SELECT instruction (see previous ...), the right connection string and the right authorisations on the server, you should be ok.
Philippe Grondier
2010-05-18 15:51:55
Your #1 depends on where the Access/Jet function is called. IsNull() on a column used as one side of a comparison in a WHERE clause will only cause a big problem if there are no other criteria. That is, it will pull the whole table clientside. If there are other criteria, the number of records pulled could be (but won't necessarily be) much smaller, since the Jet query optimizer will apply the expression criterion last after all the others have been satisfied. Not good, but not disastrous, either (not much more so than in Jet/ACE itself).
David-W-Fenton
2010-05-18 22:01:47
incredible! Do you have a secret/private 'Jet' tag to track answers that mention this word? I was just thinking about it when I typed the magic word...
Philippe Grondier
2010-05-18 22:34:33
+3
A:
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.
David-W-Fenton
2010-05-18 22:25:50
Why do you need to add the dbSeeChanges option? Why do you need to have PKs on all tables? What are the risks with these two issues? I intend only to use DAO.
Craig Johnston
2010-05-19 04:38:25
I'm not sure about the reason for the dbSeeChanges, but it was something that I believe I was told by error messages when I ran the code. Or perhaps I got it from Baron/Chipman. I don't actually see it anywhere in there, so I'm pretty sure the error messages recommend it.
David-W-Fenton
2010-05-20 00:41:01
For PKs it makes a difference with bound forms in terms of updating data. Without a PK, ODBC has to take the values of all the fields and make a fake key, and if that's not unique, it has to make a synthetic key in order to tell which record is being updated (I'm a bit foggy on this, and it may be a SQL Server issue, too). How else could ODBC tell the server which record to update? In the absence of a real PK, there has to be something functioning as a PK to tie the record displayed in Access to the corresponding record in the SQL Server table.
David-W-Fenton
2010-05-20 00:43:36
...and of course, a table without a PK is not really part of a database, by the basic rules of relational databases and SQL. I never ran onto that problem because I've never created a table without a PK (natural or surrogate).
David-W-Fenton
2010-05-20 00:44:27