Use RunSQL or something to read into a variable 2

[biot023]

Hallo. If quite often find myself using code like:

Dim qry as QueryDef
Dim rc as Recordset
Dim sqlstr as string
Dim Count as Integer

sqlstr="SELECT COUNT([My_Table].*) AS Count FROM [My_Table] WHERE [My_Table].[Flag]=True;"
set qry=CurrentDb.CreateQueryDef("",sqlstr)
set rc=qry.OpenRecordset
rc.MoveFirst
Count=rc!Count

This is fine, but would there be a way of running the query or SQL (like DoCmd.RunSQL) in a way that just reads the result into a variable (in this case the integer Count)?

Thanks,
Douglas
Common sense is what tells you the world is flat.

 

[RobertT687]

Try this:
Count = DCount("*","[My_Table]","[Flag]=True&quot

 

[biot023]

Cheers, man - just what I was looking for - you don't know of any other similar functions offhand, do you?

Douglas JL Common sense is what tells you the world is flat.

 

[RobertT687]

Check out on-line help in Access. At least in Access97 help was very good. Haven't heard such good things for A2K and above.

 

[mp9]

Is it worth pointing out that DCount can be very slow, and that your recordset equivalent, although apparently more long-winded, might actually run faster?

http://www22.brinkster.com/accessory/

 

[biot023]

Yeah - if you knew that for sure - I need the Count working out pretty quickly, as I use it in an iteration through another recordset.
Where would I be able to find this out?

Cheers,
Douglas JL Common sense is what tells you the world is flat.

 

[mp9]

Well, there isn't really a hard and fast answer to that - measurable differences in performance would depend on the complexity of the tables involved and the number of records in the table.

Having said that, I think your original post is a little lengthy and could be streamlined as follows:

Dim rc as Recordset
Dim Count as Integer
set rc=DBEngine(0)(0).OpenRecordset("SELECT COUNT([My_Table].*) AS Count FROM [My_Table] WHERE [My_Table].[Flag]=True;")
rc.MoveFirst
Count=rc!Count

I feel this would generally be faster but the only way to find out for sure is to write the code for each approach, put that code in a For..Next loop and loop through it a couple of thousand times - this will let you compare performance in a measurable way (as one iteration of each approach may not show a measurable difference).

Hope this helps.

http://www22.brinkster.com/accessory/

 

[biot023]

Cheers, man - that look alot more efficient - I have to admit, I'm prone to getting quite lazy with VBscript.

Douglas JL Common sense is what tells you the world is flat.