setting ADODB variables

Hi :wave:

I want to know if my coding is advisable or it will consume space in memory. my code is this:
let's assume that active data object is already added in references and we have set connection using adocn variable

dim adocmd as adodb.command
dim adors as adodb.recordset
dim adocn as adodb.connection

1. first, i connect this to get tr_custCode value

set adocmd = new adocmd.command
set adocmd.activeconnection = adocn
adocmd.commandtext = "SELECT tr_custCode FROM trans"
adocmd.commandtype = adcmdStoredProc
set adors = adocmd.execute

intcustCode = adors!tr_custCode

2. Now that i got the value i want, i want to query more data from other table

set adocmd = new adocmd.command
set adocmd.activeconnection = adocn
adocmd.commandtext = "SELECT * FROM customer"
adocmd.commandtype = adcmdStoredProc
set adors = adocmd.execute

Now you see that I used the adocmd and adors twice and set it, does this affects consuming of memory? or i have to set adocmd and adors to nothing to free some memory?? thanks a lot guys. Im a newbie and I want to learn professional coding. :)

Have you read the FAQ's at the top of this Forum ?

http://www.vbforums.com/showthread.php?t=337051

They will give you lots of good information about how to work with ADO and databases. It would probably benefit you to read that first !

Generally it should be a good idea to Declare ADODB objects in Module.
As connection is needed all throug the program, this could also be SET and opened in the Module itself.
Similarly the adoRS could be Declared & SET in the Module.
But the adoRS would be prefarably be Opened whenever reqd.
Here Iam using an illustration declaring at Form level

Code:

---

'Declarations
Dim adoRS As ADODB.Recordset
Dim adoCONN As ADODB.Connection

Private Sub Form_Load()
  Set adoCONN = New ADODB.Connection
  ' Have code to initialize connection string
 
  Set adoRS = New ADODB.Recordset
  Call DoSomething
End Sub
Private Sub DoSomething()
  ' Have some code to open & close recordset
End Sub

---

by Opening the adoRS whenever reqd we have an oppurtunity to reuse the adoRS.
At form unload the adoRS should be checked if open and closed. It should be SET to nothing when the prog closes.
The connection should necessarily be closed & SET to nothing when prog ends.

But remember, everything depends on the way you programmed to work i.e. the task before it.
There could be situation where in between the program you want to adoRS available continually (like across forms, then you need a diff approach) (even here you could work out a alternatives, with lil extra work)
I'd go for dynamic usage of adoRS for better code/memory management.
Whatever you do you must ensure to CLOSE and SET the objs to NOTHING :wave:

Duplicates Merged