Problem with error 3709

[John Melbourne]

Hi All,
I have a table with a single record that contains the dates and
information for the jobs processed during a quarters processing, I want to
create a form so that users can update the table.
The table "tblQuarterDates" contains the following fields.
fldPreQuarterStart
fldPreQuarterEnd
fldCurQuarterStart
fldCurQuarterEnd

I have created a form with 4 fields Unbounded and I have the following code.

Option Compare Database
Option Explicit
'----------------------------------------------------------------------------------------
Private Sub Command0_DblClick(Cancel As Integer)
' Close the form
DoCmd.Close

End Sub
'
----------------------------------------------------------------------------------------



Private Sub Form_Load()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.CursorLocation = adUseClient
'rst.LockType = adLockReadOnly
rst.Open "Select * from tblQuarterDates", Options:=adCmdText
' Set the form's recordset to the recordset just created
Set Me.Recordset = rst
' Move to the first record
' If at bof, move to the next record
If Me.Recordset.BOF Then
Me.Recordset.MoveNext
End If
Me.txtPrevoiusQuarterStartDate =
rsUpdate.Fields.Item("fldPreQuarterStart")
Me.txtPreviousQuarterEndDate =
rsUpdate.Fields.Item("fldPreQuarterEnd")
Me.txtCurrentQuarterDateText =
rsUpdate.Fields.Item("fldCurQuarterStart")

End Sub


'
-----------------------------------------------------------------------------------------


Private Sub txtExit_Click()
DoCmd.Close
End Sub


Private Sub txtUpdate_DblClick(Cancel As Integer)
' Update the End of quarter control file
' written by JM
'
Dim sql As String
Dim rsUpdate As ADODB.Recordset
Set rsUpdate = New ADODB.Recordset

On Error GoTo DbError

'Assign updatable cursor and lock type properties.
rsUpdate.CursorType = adOpenDynamic
rsUpdate.LockType = adLockOptimistic

'Open the Recordset object.
rsUpdate.Open "Select * from tblQuarterDates", Options:=adCmdText

'Don't try to update the record, if the recordset
' did not find a row.
If rsUpdate.EOF = False Then
' Update the record based on the input.
With rsUpdate
Me.txtPrevoiusQuarterStartDate =
rsUpdate.Fields.Item("fldPreQuarterStart")
Me.txtPreviousQuarterEndDate =
rsUpdate.Fields.Item("fldPreQuarterEnd")
Me.txtCurrentQuarterDateText =
rsUpdate.Fields.Item("fldCurQuarterStart")

.Update
.Close
End With
End If
MsgBox "Record update.", vbInformation

' Close the form-level Recordset object and
' refresh it to include the newly update row.
rsUpdate.Close

Exit Sub
DbError:
MsgBox "There was an error updating the database. " _
& Err.Number & ", " & Err.Description
End Sub

When I run the Update I get the following message:
First sentence come from my error routine.
There was an error updating the database. 3709, The connection cannot be
used to perform this operation. It is either closed or invalid in this
context.


Any help welcome.
Thanks in advance.

 

[J_Goddard via AccessMonster.com]

Hi -

I'm not strong in ADODB, but I see two problems -

First, your code does not update the recordset - it updates the form fields
again, or tries to;

Me.txtPrevoiusQuarterStartDate = rsUpdate.Fields.Item("fldPreQuarterStart")
should be
rsUpdate.Fields.Item("fldPreQuarterStart") = Me.txtPrevoiusQuarterStartDate

etc.

Second, you are trying to close the recordset twice, once in the With block,
and then again below it. Try taking the .Close out of the With block.

HTH

John

 

[John Melbourne]

Thanks John,
You pointed me in the right direction I took notice of
both your points and then found I was missing the line,
rs.ActiveConnection = CurrentProject.Connection
it now works OK I believe all three item were needed to fix my code but not
opening the connection was probable the cause of the error message.

 

[Robert Morley]

One point to note is that while "rs.ActiveConnection =
CurrentProject.Connection" will work, it's actually opening a separate
connection behind the scenes. You should actually make it
"rs.ActiveConnection = CurrentProject.Connection".

The reason for this is VB/VBA's use of default properties. While useful,
they can also lead to bugs/misfires like the now-infamous example above.
What actually happens is that because you're not using Set to tell VB that
you're assigning an object, "CurrentProject.Connection" returns the default
property, which is ConnectionString. So it's like you typed
"rs.ActiveConnection = CurrentProject.Connection.ConnectionString". Since
it sees an incoming string, rather than a connection object, it parses that
string and creates a new connection using the information provided.


Rob

 

[Robert Morley]

One point to note is that while "rs.ActiveConnection =

CurrentProject.Connection" will work, it's actually opening a separate
connection behind the scenes. You should actually make it
"rs.ActiveConnection = CurrentProject.Connection".

Oh crap...that last line was supposed to read:

Set rs.ActiveConnection = CurrentProject.Connection


Rob