What's the fastest way to perform SQL functions (select, insert, update) in VBA on an SQL server database?

Question

I don't know between ADO, DAO and DLookUps and such. Does anyone know?

Answer 1

Regarding DAO vs ADO, I'm not sure about a difference in performance but there is a difference in available functionality.

There is a microsoft article showing the differences in a nice table. Choosing ADO or DAO.

It also states:

"In particular, ADO is a good choice if you are developing an Access database solution that will later be upgraded to SQL Server — you can write your ADO code to minimize the number of changes that will be required to work against a SQL Server database. In addition, ADO is a good choice for developing new data access components that work with SQL Server, multidimensional data, and Web applications."

Seems like ADO might be the way to go.

I don't know anything about DLookUps though.

Answer 2

I find that the application bottleneck is usually the actual SELECT/INSERT/UPDATE on the DB and not how the application calls it. if you are worrying about speed make sure you have well designed tables and indexes.