Return a value from a RunSQL to vba

[hpicken]

I have a quick question (I hope). I'm try to return the number of records different between two tables. I thought of running a RunSQL like below but how would I get the result back to a variable to use within vba (I'm running ACC2K)

DoCmd.RunSQL ("SELECT Count(tblTempProducts.PLUid) AS CountOfPLUid FROM tblTempProducts LEFT JOIN tblProducts ON tblTempProducts.PLUid = tblProducts.PLUid WHERE (((tblProducts.PLUid) Is Null));")

Howard

 

[RoyVidar]

Nope - the runsql method of the docmd object is only good for action queries (delete, insert, upddate...), not returning records. To do that, either one of domain aggragate functions might do, or open a recordset. In this case, with an outer join, I think recordset is the only viable option. If your sql string is correct, then perhaps something like this:

[tt]dim rs as adodb.recordset
dim strSQL as string
strSQL="SELECT Count(tblTempProducts.PLUid) AS CountOfPLUid " & _
"FROM tblTempProducts LEFT JOIN tblProducts ON " & _
"tblTempProducts.PLUid = tblProducts.PLUid " & _
"WHERE (((tblProducts.PLUid) Is Null));"
set rs=currentproject.connection.execute(strSQL)
myVar = rs.fields("CountOfPLUid").value
rs.close
set rs=nothing[/tt]

- typed not tested

Roy-Vidar

 

[LittleSmudge]

DoCmd.RunSQL is mean for 'Action Queries" that do something TO the data not SELECT queries that extract FROM.


You need to

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "SELECT Count(tblTempProducts.PLUid) AS CountOfPLUid FROM tblTempProducts LEFT JOIN tblProducts ON tblTempProducts.PLUid = tblProducts.PLUid WHERE tblProducts.PLUid Is Null;"


Msgbox "Result = " & rst!CountOfPLUid

rst.Close
Set rst=Nothing





'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[hpicken]

Thanks guys

Just what I needed. I ended up using your code LittleSmudge. Finished off my application nicely. Now to pack it up ready for install.

Cheers

Howard

 

[RickSpr]

Using a DCount() function would be much simpler:

    myvar = DCount("tblTempProducts.PLUid", _
        & "tblTempProducts LEFT JOIN tblProducts " _
        & "ON tblTempProducts.PLUid = tblProducts.PLUid", _
        "tblProducts.PLUid Is Null")

However, you should avoid using DCount() or any other domain aggregate function within a loop. If you need to do something like this inside a loop, you should use the recordset code that RoyVidar and LittleSmudge offered, but opening the recordset before the loop and [/i]closing[/i] it after the loop ends.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[hpicken]

Thanks for that Rick. I got it all going how I want it to.

The procedure gets used twice from different functions so I went with LittleSmudges idea. It worked a treat and fairly quick on 15,000 records.

Cheers

Howard