DAO vs ADO and SQL Server

[Guest]

Hi there,

I've "upsized" my backend .mdb and linked my frontend .mdb (via ODBC) to the
new SQL Server backend. Now I'm going through all the forms and modules to
make sure the VBA code is working in this environment.

I read in the Upsize help screens and it indicates there that I'll want to
change all DAO recordset code to ADO. Which I've started.

Question: Some of the DAO code continues to work fine. Some of the DAO
produced SQL type error messages, and after changing to ADO it works fine. Is
it necessary to change the DAO code that continues to work fine? Why does
some work and others not?

Thanks in advance!
Marty

 

[Larry Linson]

Hi there,

I've "upsized" my backend .mdb and linked my frontend .mdb (via ODBC) to
the
new SQL Server backend. Now I'm going through all the forms and modules
to
make sure the VBA code is working in this environment.

I read in the Upsize help screens and it indicates there that I'll want to
change all DAO recordset code to ADO. Which I've started.

Question: Some of the DAO code continues to work fine. Some of the DAO
produced SQL type error messages, and after changing to ADO it works fine.
Is
it necessary to change the DAO code that continues to work fine? Why does
some work and others not?

That's old advice, over-influenced by marketing. The Access development team
now suggests that the "old standard" -- MDB <-> DAO <-> ODBC <-> SQL Server
is preferrable.

Unless you were using "Seek," which can't be used on linked tables (whether
they are llinked Jet tables or linked ODBC tables), all your DAO should
continue to work fine. In the Client-Server environment, you should review
your desing and alter it to minimize the amount of data passed back and
forth on the network to improve performance.

Larry Linson
Microsoft Access MVP