Connection problem

[Guest]

In my reading, I came to believe that once you opened a connection (in this
case in an access 2000 form) the connection remained open until the form was
closed. The code below gives me an error in the click event indicating that
there is no open connection. Thank you.

Option Compare Database

Private Sub Form_Load()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset

End Sub

Private Sub cmdTest_Click()
'after entering data and clicking button, update the table
With rs
.Source = "Select * from tblTest"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

With rs
If Not (rs.BOF And rs.EOF) Then
.AddNew
.Fields("TestLine1").Value = Me.txtLine1.Value
.Fields("TestLine2").Value = Me.txtLine2.Value
.Update

Else
.AddNew
.Fields("TestLine1").Value = Me.txtLine1.Value
.Fields("TestLine2").Value = Me.txtLine2.Value
.Update
End If

End With
Set rs = Nothing
set cn = nothing
Me.txtLine1.Value = ""
Me.txtLine2.Value = ""
End Sub

 

[Douglas J. Steele]

It's a question of variable scope. Because you declared cn and rs inside of
the Form_Load sub, they're only known inside that sub.

To do what you want (which I don't actually recommend unless you need to be
able to access the recordset from other routines), declare the variables at
the beginning of the module, before the definition of any routines:

Option Compare Database

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
End Sub

Private Sub cmdTest_Click()
'after entering data and clicking button, update the table
With rs
.Source = "Select * from tblTest"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

With rs
If Not (rs.BOF And rs.EOF) Then
.AddNew
.Fields("TestLine1").Value = Me.txtLine1.Value
.Fields("TestLine2").Value = Me.txtLine2.Value
.Update

Else
.AddNew
.Fields("TestLine1").Value = Me.txtLine1.Value
.Fields("TestLine2").Value = Me.txtLine2.Value
.Update
End If
End With

Set rs = Nothing
set cn = nothing
Me.txtLine1.Value = ""
Me.txtLine2.Value = ""
End Sub

 

[Guest]

Thank you Douglas. I assume then that I would need to declare the connection
in each module that I want to use or create a recordset. Is that right? and
also, should I close the connection at the end of each module?

Thanks again.

 

[Douglas J. Steele]

You could declare the variables as Public in a stand-alone module (not a
class module nor a module associated with a form or report), and they'd be
available everywhere, but that's really not recommended. If you want to be
able to do this on a module-by-module basis, yes, you'd be best off
declaring in each module. You still haven't told me why you think it's
necessary, though.

There's a lot of debate whether you actually need to close the connection
yourself, or just let Access handle it. Since it doesn't cause any harm, I
always explicitly close them myself.