I was just reading ODBC Linking (http://www.access-programmers.co.uk/forums/showpost.php?p=999865&postcount=4). I followed the link to Beginners Guide To ODBC (http://www.utteraccess.com/wiki/index.php/Beginners_Guide_To_ODBC). The Guide had the following:The general statement has been "Use DAO for Jet, use ADO for anything else", though I should qualify the statement to be: "Use DAO for Jet's objects such as linked tables and saved queries, and ADO for anything else." To explain why, if we tried to set a recordset variable based on a form's recordset and the form is bound to a table or linked table, a DAO recordset is returned, signifying that Jet uses DAO by default. Also, DAO is heavily optimized for interaction with Jet while ADO's design consideration was to be able to work with any data source.So ......

In my situation the data resides on Microsoft's SQL Server as a back-end with an Access2007 front-end. The SQL tables are linked to the Access front-end. Based on the "Beginners Guide To ODBC", linked tables are OK with DAO. The Access front-end is DAO based. So far everything is working well.

In the event that the database is "upgraded" sometime in the future, should I switch it to ADO?

Exactly what is meant by "upgrading" the database?

Personally, I'm inclined to say that it's not really a exclusive either/or choice. I actually mix and use both DAO and ADO, at maybe 95 to 5 ratio. As the guide discusses, Jet is very good at handing off the queries back to the server for server-side processing as long it's not being coerced into doing evaluation that can only be done locally. Because of how form are designed, it's also the case that most queries written to support those objects are very easy to be "ODBC-friendly". For reports, it's easier because since it's read-only, passthrough query makes perfect sense here. Only in complex form with several calculations, custom business logic, or stringent requirements, may it be necessary to write queries that exceed Jet's flexibility and thus would have had required local evaluation. In such cases, I substitute this with ADO so I can then use native dialects, access backend functions, or invoke a stored procedures/functions and still have updatability for the form itself. In such forms, the added work required to deploy ADO recordset can be justified. For a large majority of forms we may design and use, DAO does the job just well enough.

Exactly what is meant by "upgrading" the database? Well, I was trying to be positive. Usually when I try "enhancing" it tends to become a nightmare. ;)

Anyway, it's a very simple database, a record of inspection histories. So far no real demands for upgrading. A couple of people did want "Num Lock" activated when in Access, I was able to find the solution here - but that seems to have been the extent of the enhancement requests. After all you are never finished, right?:D