Short history: old Access database that's been upgraded through to Access 2000 or 2003 (probably 2000), but not beyond. Was rebuilt in Access 2003 format around 2006/2007 (by someone else who no longer works here). Being used now in Access 2007 and 2010. Uses DSN to connect to SQL Server backend. Last week it was SQL Server 2005, moved the back end on Saturday to SQL Server 2012. Changed the DSN when moving to the new server. But I don't think this has to do with the DSN (it's getting data just fine). Problems started immediately post move.
There's a data entry form. User enters a Generator ID, event triggers it to look for the details for the generator and load them into a generator subform. After it loads the information into the subform, and before the user does anything else, it throws "This connection cannot be used to perform this operation. It is either closed or invalid in this context."
This is the code I think is the problem:
It triggers post update of the Generator ID:
This is the code for the function that it calls:
Here's the funky thing. It doesn't throw this error in the development copy of the database, dev copy works just fine. It only throws it from the production version. They are located on the same network, just in different folders. They are pointing to the same database on the same SQL Server using a DSN file located inside the folder where the .mdb file is located (this is a change in the front end, prior to this it was using a DSN on each individual machine, but I changed it to stop that).
Any thoughts or avenues for me to pursue on this one?
There's a data entry form. User enters a Generator ID, event triggers it to look for the details for the generator and load them into a generator subform. After it loads the information into the subform, and before the user does anything else, it throws "This connection cannot be used to perform this operation. It is either closed or invalid in this context."
This is the code I think is the problem:
It triggers post update of the Generator ID:
Code:
Private Sub txtGeneratorID_AfterUpdate()
On Error GoTo Handle_err
Me.txtGeneratorID = UCase(Me.txtGeneratorID)
Call FillHandlerSubform(Me.subGeneratorInfo, Me.txtGeneratorID.Value)
If GetGenStat(Me.txtGeneratorID.Value) = "N" Or GetGenStat(Me.txtGeneratorID.Value) = "OB" Then
MsgBox "Warning Generator Status! " & UCase(Me.txtGeneratorID.Value) & vbCrLf & _
"This Generator has a status of N or OB!", vbCritical, "Bad Generator Status!"
Cancel = True
End If
ExitHere:
Exit Sub
Handle_err:
MsgBox Err.Description
Resume ExitHere
End Sub
Code:
Public Function GetGenStat(GeneratorID As String) As String
On Error GoTo Handle_err
Dim cur As ADODB.Recordset
Set cur = New ADODB.Recordset
Set cur.ActiveConnection = MAINDB
cur.Source = "SELECT GenStatus FROM Handler WHERE EPAID=" & quote(GeneratorID)
cur.Open
ExitHere:
If IsNull(cur.Fields("GenStatus").Value) Then
GetGenStat = "Empty"
Else
GetGenStat = cur.Fields("GenStatus").Value
If cur.State = adStateOpen Then cur.Close
Set cur = Nothing
End If
Exit Function
Handle_err:
MsgBox Err.Description
Resume ExitHere
End Function
Any thoughts or avenues for me to pursue on this one?