ADO, DAO vs RunSQL

[BillNorton]

After a hiatus of a few years I find myself back writing Access applications, so I need to get caught up a bit.

First of all help me out with the whole ADO, DAO RunSQL thing. In the past whenever I needed to do any database operations I almost always used straight SQL with DoCmd.RunSQL, e.g.:

lsSQL = "INSERT INTO tblUsers  CenterID, WorkerID..."
DoCmd.RunSQL (lsSQL)

Most other coders seem to use some recordset approach, e.g:

Set rst = dbs.OpenRecordset("tblUsers")
rst.AddNew
    rst("ClientID") = Me.ClientID.Value
    rst("WorkerID") = Me.WorkerID.Value
...
rst.Update

The only time I ever used recordsets was when I needed to loop through each record and apply some logic that was too convoluted for SQL or at least too convoluted for me to write in SQL.

So, what's the advantage of using recordsets - whether ADO or DAO - over RunSQL?

 

[boblarson]

The most efficient, if you can do what you need to do with it, is just the straight SQL. But, you should know that you are actually using DAO when you use "Docmd.RunSQL strSQL" as the Access engine - JET, uses DAO for it's operations.

 

[The_Doc_Man]

From a conceptual thing, the most efficient is to have predefined SQL in a query and run the query. Because Access pre-parses a stored query.

As pointed out, JET uses DAO, so when executing a non-predefined SQL, it is almost a wash as to efficiency.

ADO, because it involves the ability to deal with Active data objects, is more complex. More complex usually means BIGGER, which in turn means, uses more memory-related resources. Also, I'm told that ADO doesn't do some things that DAO will do. However, DAO won't work correctly for certain types of data pages. So I guess there are reasons to use either.

Myself, I'm a firm believer in using VBA and recordsets most of the time, whenever and whereever possible. But if the SQL is truly static, RunSQL is not a wrong choice.

 

[BillNorton]

But, you should know that you are actually using DAO when you use "Docmd.RunSQL strSQL" as the Access engine - JET, uses DAO for it's operations.

What are the implications of that? In particular, if I am designing an application using Access as the front end but with an as yet unknown DBMS as the back end will I have problems with either DAO or RunSQL? I would think that pure SQL would be the more "open" approach.

 

[boblarson]

Don't know what you mean by PURE SQL in an Access database because there really isn't such a thing. Access uses the JET engine to do it's "Database" work and if you use Access for a front end, you will be using JET. JET uses DAO, so if you use Access, you are going to be using DAO, whether you see it, code it, or neither. You can choose to use ADO by coding for it, but in the absence of ADO, you will use DAO.

 

[BillNorton]

JET uses DAO, so if you use Access, you are going to be using DAO, whether you see it, code it, or neither. You can choose to use ADO by coding for it, but in the absence of ADO, you will use DAO.

OK. So if I do something like this:

DoCmd.RunSQL ("INSERT INTO tblUsers  CenterID, WorkerID...")

Jet will essentially take my "pure SQL", convert it into something quite similar to the DAO code I could have written and send that to the backend. Is that right?

Does it do this regardless of the backend? Does Oracle understand DAO and is it the same DAO as Access or SqlServer?

 

[boblarson]

Anything that the Access JET uses (including DoCmd.RunSQL ("INSERT ...") is done via DAO in the background. To "connect" Access to an external database, you basically build an ODBC connection which connects a driver on your system to the JET Engine. The driver allows commands to flow back and forth between different databases. You can use a predefined ODBC connection (setting up a DSN, either system DSN or file DSN for the parameters by which to connect), or you can dynamically build an ODBC connection by supplying the connection string to either the DAO or ADO object you've chosen.

If you use the DoCmd.RunSQL command, you must be connected to the datasource at the time you use that command (can be by a linked table - which is connected via an ODBC connection or by a dynamic link that you create by DAO. If you want to use something other than DoCmd.RunSQL, then you will need to create the ADO objects (connection and recordset) to do so.

 

[BillNorton]

Bob -

First of all let me thank you from the bottom of my heart for your lucid and prompt replies. I'm afraid I have a few more questions though.

Let's see if I understand how it all works so far - one fact at a time. Let's say I have an Access front end and an Oracle back end.

1. Typically - though not necessarily - the Oracle tables would be linked to Acces via an ODBC connection.
2. Within my Access code somewhere I have something like DoCmd.RunSQL("INSERT INTO Clients ClientID, CityID...")
3. When that code gets run the SQL is first sent to the JET engine.
4. JET then converts the SQL into DAO.
5. Jet sends the DAO code to Oracle via the ODBC connection.
6. Oracle processes the DAO code and returns (via ODBC and then JET???) a record set (for SELECT queries) or a return code for action queries.
7. The Access code picks up from there.

I'm especially uncertain about steps 5 & 6.

 

[boblarson]

I would say that essentially what you've stated is correct. The main thing to remember is that you will be using an ODBC (Open Database Connectivity) connection when connecting to Oracle, or SQL Server, regardless of whether you set it up in advance or if you do it by code. The ODBC driver is the part that "translates" between database platforms.

 

[BillNorton]

OK. One final thing. I know that one of the limitations of Access and the Jet engine was that it had a certain "file server" approach to things. For example, if your data .mdb was on a server and you ran a query like SELECT * from Employees WHERE Employees.LastName="Jones" the JET engine would bring the entire Employees table across to the client where it would process the query. Obviously there was potential for a significant performance hit.

Is that the way it still works? What if the backend is a "true" RDBMS like Oracle? Will the actual processing through the table be done on the server side by Oracle or by JET on the client side?