I remember reading somewhere that "IN" subqueries were very close to joins in speed, UNLESS the IN operater references a list of comma separated values.  If you are running tests,...

Paul,

I don't think that this approach is an advantage.  It just is a manual substitute for what Access ADPs do automatically, and also winds up adding a connection needlessly.  It...

"That's the down side of the ADP. You have 1 global connection (good) but it's always opened. So you should expect to have 50-100 connections at all times on...

"Will I experience any problems with a MDB FE linked to a SQL Server 2000 BE using ODBC?"

Please see the previous threads, as this is discussed in some detail, and...

Off the top of my head (I'll try to be accurate), try this:

For forms, you can use the InputParameters property of the form, and include question marks for the parameters...

This is a great thread, although we are deviating from the main topic.

I think the results return exactly as we would predict: a reasonable advantage for multiple loops or a heavily...

Results with a single INT parameter in an empty SP:

Loops : 1   RPC : 20    SQLBat : 0  -100 %

Loops : 10  RPC : 20    SQLBat : 30 50 %

Loops...

I slightly modified your code to use

MyCn = CurrentProject.ActiveConnection

I created an Empty SP with no parameteres and only a RETURN statement

Performed code and SQL Server on the same server:

Loops...

"Actually I'm thinking that my test doesn't really test the different between RPC and SQL batch... I'll remove the select in the proc and the parameter and try again....

Remi,

Thanks again!  This is very interesting.  From my point of view, it's nice to know that the batch method is only slightly slower than RPC in the long haul.  For...

Remi,

This is really helpful.  I love real data!  Since you have it all coded up in VBA, could you put both types of calls into a loop of 1,000 or...

Basically, it's not a factor at all, IMO.  

First, these are not queries that are executed thousands of times.  We are talking about differences of microseconds here.

Second, what is the evidence that the claims...

"For example, when a form opens, it uses the sysuser name (from a linked SQL view) to show only the records assigned to that username.  So the form's record source...

Remi,

That's good! I like it!  That makes it even easier.

Thanks,

Rich

"Am I downloading the whole table - thus increasing network traffic, or just the few records for the individual user.  Maybe this may be a cause of a bottleneck??"

It all...