Docmd.RunSQL vs Me.Recordsource 1

[mac318]

Fellow Programmers. Does anyone know, from experience or reading, which is more efficient?

strSQL = "Select * from ..."

Docmd.RunSQL strSQL
or
Me.Recordsource = strSQL

1. Single user.
2. Network.

Thanks,

mac318

 

[JimHorton]

You are comparing apples to oranges, it seems. One statment just runs an Action query, not a select, the other set's a form or report's recordsource, so no comparison is truly valid here.
--Jim

 

[mac318]

Jim, you're almost correct in your analysis. I'm comparing apples and elephants. I was attempting to ask the question while fending off aligators. Here is the appropriate question.

Is it more efficient to set the recordsource with a select statement versus setting the recordsource with a stored SQL procedure, especially over a network.

Thanks,
mac318

 

[JimHorton]

mac,
OK, when you say 'stored sql procedure', you're now saying it's a backend that's not access, such as sql7 or Oracle? I guess that's moot anyway. It would be faster with a stored procedure (in the case of an odbc backend) and also faster with a stored Access query (with access backend) than a select statement. This is because the sql is compiled in both cases, whereas when you pass a select statement to create a recordset, it must be compiled first, which is the time difference.

In Access, the difference will be minimal. However, in an odbc linked table, the difference can be huge, since a straight 'select...' statement sent to an odbc backend *may* require JET to process it completely, this depends on the comlexity of the query. For example, a simple select will be processed on the server and the correct records returned--essentially the same as a passthru query. But the more complex you get, and this also depends on the odbc driver and sql backend, JET may need the entire table(s) to be schlepped to the local frontend prior to processing. If you use a local access function, for example, that's not translatable to the native backend, then there is no way the server can do anything with your select statement--it just gives up and sends the entire table to Access and says 'you deal with it'.
--Jim

 

[mac318]

That's what I was looking for Jim. At this time, I am using Access as the backend; however, I want to be prepared to move to SQL server. I have some very heady SQL statements which may fall into your latter category of being too complex to process on the server. Thanks for your help and understanding. I've been a programmer since 1983. Now, an old man must learn to deal with networks and the internet. Every now and then, I have a 'senior moment' and get totally confused. Your help is definitely worthy of a kudoo.

mac318

 

[StarScream]

I'm working a similar case...I think (but not nearly as smart as you all seem B-)).

I have several forms and reports based on a query that asks you to enter your branch (work organization). Instead, I want the computer to use the Environ("Username&quot, lookup the branch associated (from a table) with that name and sort only those records that have the same branch.

In the query criteria, I can't seem to get it to work. Might be better to try what I think you were doing in code:

Some form of:

Dim strSQL As String (?)

strSQL = SELECT * FROM [Personnel Table] WHERE [LoginName] = Environ("Username")

DoCmd.RunSQL strSQL

This code is obviously not correct, can you help point out the error? Also, is this placed on the OnOpen or OnLoad events ? Any help is appreciated. Thanks.

PJP

 

[mac318]

PJP,
The first thing that stands out is the use of "Docmd.RunSQL" which is reserved for action queries. Try this:

Me.Recordsource = strSQL

p.s. read carefully the above responses to my question. They present a great tutorial for this situation.
mac318

 

[StarScream]

Well I tried something totally different. I used the DLookUp function on a query and it came out with exactly what I wanted. Thanks, you guys got me thinking... (Uh-Oh, Look out now...) LOL

PJ