Hello out there!
I'm having a problem with a connection. I'm using ADO 2.1 to connect to an access database through ODBC. My strategy is to open the connection at the start of every sub and close it at the end.

The problem I'm facing is that if I change data in a sub (and update the recordet) then call another sub (which reopens the connection before the first one is closed), and take data in the recordset I don't get the updated data! If I close the connection before calling the second one I get the correct data!

Any help would be appreciated.

I have include a sample of code that doesn't work.

Sub1

Dim cnn as ADODB.connection
Dim rs as ADODB.recordset

set cnn=new ADODB.connection

cnn.open connectionstr

set rs = new ADODB.recordset

rs.open SQLStatement, cnn, adOpenKeyset, adLockOptimistic, adCmdText

rs!field = True
rs.update
rs.close

CALL Sub2

cnn.close
End Sub



Sub2

Dim cnn as ADODB.connection
Dim rs1 as ADODB.recordset

set cnn=new ADODB.connection

cnn.open connectionstr

set rs1 = new ADODB.recordset

rs1.open SQLStatement, cnn, adOpenKeyset, adLockOptimistic, adCmdText

msgbox rs!field 'Returns the value before the change has been made and not the correct one
rs1.close

cnn.close
End SUB2