Connection problem??

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. :p

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

Why do you want to open and close it so much?

The only thing I can think of is just close it before you call your sub or check to see if your connection is already open before you open it in every sub.

There is no need for u to close and open the recordset as well as the connection, base on your code, if u open the recordset in Sub1 you can call Sub2 w/o closing the recordset coz in Sub2 u issue a command Set rs=New ADODB.Recordset so it will automatically close the previous recordset not unless u call Sub2 w/o closing the recordset and in Sub2 you issue a command rs.Open... it will prompt u an error, because your trying to open a recordset that is currently open.



try this one:


'paste this in a module(.bas)
Public cnn as ADODB.Connection

Public Sub OpenConnection
set cnn=new ADODB.connection
cnn.open connectionstr
End Sub


'in General Declaration of the Form
Dim rs as ADODB.Recordset

Sub1

set rs = new ADODB.recordset

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

rs!field = True
rs.update
CALL Sub2

End Sub1


Sub2

set rs = new ADODB.recordset

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

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

Call CloseConnection

End Sub2

'procedure that will close the connection
Private Sub CloseConnection
set rs = Nothing
rs.Close
cnn.Close
End Sub

I was not able to try this one, but hope it works!!! :)
Anyway, the idea is there, coz if u do the same in the future, u'll probably find your self in a world of @#$%$# :D

Thanks a lot venom8. I'll try this on the piece of code I've already written, but from now on I'll change the strategy in my programs and open the connection at the start of the program and close it at its end.

I hope that stategy isn't going to get me into trouble (or won't lead me in a world of @#$%$#) ;)

That stratergy of opening the connection at the beginning of the program and closing it at the end is fine for smaller programs not intended for multiple users. However, when you get to developing more complex programs that may have many users connecting to remote databases, that stratergy is not good.

One of the fundemental considerations when coding n-tier applications is to Open connections late and close them early, the opposite of the above stratergy. Doing this will help minimize resource use.

Yes, I know there are things like connection pooling to help with those matters, but it is something to consider.