Is DoCmd.RunSQL efficient enough?

[thromada]

I quickly wrote an applet that works. But I'm questioning if it's as efficient as it could be. Using Access 2000 as a front-end and SQL 2000 as a back-end, I use the "DoCmd.RunSQL sqltxt" command to SELECT, INSERT, and UPDATE data. The Access 2000 front-end is an Access project file .adp. I made the SQL database connection by clicking File|Connection. The form uses the VB command buttons, lists, combo boxes, etc. I've seen talk of ADO and DAO, but I don't know how to use them or if I have the files needed to implement them. The DoCmd.RunSQL just happens to be what I recently crammed to learn to write the applet.

Thanks,
Tom.

 

[cmmrfrds]

The Access ADP uses ADO in the connection to sql server, so you already have everything you need for ADO.

Example of ADO.
Dim cn As New ADODB.Connection
Dim connString As String
Dim sql As String
'-- Some SQL - assume a numeric field to update
sql = "Update atable set afield = 1 where afield = 2;"
Set cn = CurrentProject.Connection
cn.Execute sql

The disadvantage of the Macro DoCmd.RunSQL is that it is a macro and there is additional overhead. Also, the error handling in the macro is not as robust as using the ADO error collection.