Database variables and recordsets

[SirDrinksalot]

Hi all,

Quick question, and one that i think should have a simple answer. I want to run a SQL string within my VBA and analyse the resulting recordset, but I'm having problems. I can access the recordset in the following way:

intCounter = CurrentDb.OpenRecordSet(strSQL).RecordCount
strSourceID = strSourceID & Chr$(34) & CurrentDb.OpenRecordset(strSQL).Fields(0).Value & Chr$(34)

However, I then need to loop through each record within the recordset, but using .movenext isn't working. My whole problem starts when i try to instantiate a database variable, problem being that I can't, it's not available. Also, if i try and create a recordset variable like so:

set rsRecordSet = CurrentDb.OpenRecordset (strSQL)

this returns a datatype mismatch!

Can anyone help me, this should be fairly starightforward (as in ADO)...

 

[RuralGuy]

Have you checked your references? Do you have Option Explicit at the top of your code page?

 

[Bat17]

fairly starightforward (as in ADO)...

CurrentDB is a DAO method. You will need to set a reference to DAO in Tools>references... in any module

Welcome to the forum SirDrinksalot

I take it that you are a member of the Pistol Club?
Drink all night, Pistol dawn

Peter

 

[SirDrinksalot]

Thanks guys, seems the problem i had before is that i simply couldn't find the references menu item! All working great now. One last question though, if you use currentdb.openrecordset without assigning it to a recorset variable, does it drop the recordset directly after it is used?

 

[RuralGuy]

Good question and probably qualifies as a *bad* programming practice!