ADO index/seek methods to linked AccDB tables

[lovesCalifornia2]

I'm converting Access 2003 app/forms, etc. to Access 2007. It's a split
database. Accdb data back end and Accdb front end. Am changing DAO recordset
calls to ADO. The Index method doesn't seem to work since I'm calling the
linked table in the back-end database. Do you know the syntax I would use to
perform my index and then seek methods?

Thank you.

 

[Douglas J. Steele]

Why are you changing from DAO to ADO? DAO is still alive and well in Access
2007 (it's been renamed to ACE), and is still the better method with dealing
with Jet databases. ADO is actually dead: it's been replaced by ADO.Net,
which is a different animal again.

 

[lovesCalifornia2]

Why are you changing from DAO to ADO? DAO is still alive and well in Access
2007 (it's been renamed to ACE), and is still the better method with dealing
with Jet databases. ADO is actually dead: it's been replaced by ADO.Net,
which is a different animal again.

My project leader suggested using ADO saying it was more efficient. I will
also be linking SQL Server 2008 tables into the AccDb back-end database.
Would you still recommend using DAO for this type of structure?

 

[Douglas J. Steele]

Since DAO was designed specifically for Jet, it's actually more efficient
than ADO, which is a generic approach, and therefore has more levels of
abstraction.

ADO is irrelevant when talking about linked tables: Access links tables
however it wants to (and you have no choice but to use ODBC, not OleDB).
Where the difference occurs is in VBA code, and there's no real issue mixing
DAO and ADO in the same application. Just make sure you disambiguate all
your declarations: there are a number of objects with the same names between
the two models (Connection, Error, Errors, Field, Fields, Parameter,
Parameters, Property, Properties and Recordset), so you need to use Dim rs
As DAO.Recordset or Dim rs As ADODB.Recordset

 

[lovesCalifornia2]

Great. Thanks for the good information.

Since DAO was designed specifically for Jet, it's actually more efficient
than ADO, which is a generic approach, and therefore has more levels of
abstraction.

ADO is irrelevant when talking about linked tables: Access links tables
however it wants to (and you have no choice but to use ODBC, not OleDB).
Where the difference occurs is in VBA code, and there's no real issue mixing
DAO and ADO in the same application. Just make sure you disambiguate all
your declarations: there are a number of objects with the same names between
the two models (Connection, Error, Errors, Field, Fields, Parameter,
Parameters, Property, Properties and Recordset), so you need to use Dim rs
As DAO.Recordset or Dim rs As ADODB.Recordset

 

[David W. Fenton]

Where the difference occurs is in VBA code, and there's no real
issue mixing DAO and ADO in the same application.

If you have ODBC linked tables, why not use DAO and let Jet sort out
the details of how it does it's job? If you then encounter something
in DAO that is inefficient, then you could try the ADO approach to
see if it works better (without complicating the code).

Isn't that similar to the basic advice that everyone gives for
upsizing from a Jet back end to SQL Server? That is, don't change
anything until the simplest conversion demonstrates performance
issues?

 

[lovesCalifornia2]

Thank you for the input. I'm leaning toward starting fresh and trying this
approach since I was initially changing connections, etc. and encountering
several issues.

 

[Ray]

Hi,

Any idea if there is a way to convert adp's to the new accdb? You can go
from an MDB to an accdb, but I've seen nothing on going from an adp to an
accdb. Since the accdb is supposed to include the capabilities of the adp,
it seems odd that there would be no ability to convert. I've seen comments
where some people have manually copied all the forms, reports, etc, but that
seems absurd. Any ideas?

Ray