"connection cannot be used" error on production but not development

SiobhanP

New member
Local time
Today, 09:05
Joined
Jun 21, 2011
Messages
7
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:
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
This is the code for the function that it calls:
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
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?
 
Are you using the latest ODBC Driver? That would be

SQL Native Client 11

but I believe that you would need that driver on each machine in order to use it. Not sure if it gets pushed out by Windows Updates.
 
I'm not sure how that could be an issue. The development copy on my machine works. When I use the production copy on my machine, it doesn't. I built the DSN files that both are using on this machine.
 
I'm not sure how that could be an issue. The development copy on my machine works. When I use the production copy on my machine, it doesn't. I built the DSN files that both are using on this machine.

Questions:

1. the development machine AND production both have SQL Server 2012 on it?

2. What version of Windows are you using?

3. The copy of the database is the same and the install options for SQL Server 2012 were all the same in both environments?

4. You have a separate File DSN for Production and Development?

5. And you have used the Linked Table Manager to change to the new DSN when you want to move from Dev to Prod? This is key.
 
Questions:

1. the development machine AND production both have SQL Server 2012 on it?

2. What version of Windows are you using?

3. The copy of the database is the same and the install options for SQL Server 2012 were all the same in both environments?

4. You have a separate File DSN for Production and Development?

5. And you have used the Linked Table Manager to change to the new DSN when you want to move from Dev to Prod? This is key.

1. In this instance, development and production are two different .mdb files. Both files point to the same database on the same SQL 2012 server. (the reasons doing it this way are beyond the scope of this problem)

2. Windows 7

3. I thought that I'd moved a copy of the production .mdb to my development folder and renamed it, looks like that's not what happened. Apparently this is a leftover development copy from when the original guy built it. I just double checked to make sure that the same code existed both copies, and found that the Function GetGenStat is NOT in the development copy. So the reason it works in one and not the other is because they're not exactly the same (glad to have worked that out).

This is the function that's failing:
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
4 & 5. The way I built the DSNs was this: in the development database I deleted all the linked tables (*because of code that the previous developer had put in place that made just updating with the linked table manager unwieldy). Then I relinked all the tables, creating a DSN file, in my development folder, that pointed to the new server. I dragged a copy of that DSN file to the production folder. Then I went into the production .mdb file and deleted all the linked tables, and relinked to them using the copied DSN file.

So the reason for the difference is I have different code in the databases, at least that problem is solve.

Still, I need to figure out why this particular code is failing now. I'm thinking it must be something about how connections are handled when communicating with SQL Server 2012. I had this show up in another part of this database with a different function, and rather than trying to fix that code, I just rebuilt around it so I wasn't relying on it. Not sure if I can do this here, but even if I can, what about the NEXT time this error pops up. The previous developer used this sort of thing all over the place. So I really need to figure out the problem and an actual fix for it.

Any thoughts on that bit?

This is the code I commented out and rebuilt to not use:
Code:
'Public Function WasteCodeString(wid As Long) As String
'    On Error GoTo Handle_err
'    Dim cur As ADODB.Recordset
'    Dim result As String
'
'    result = ""
'    Set cur = New ADODB.Recordset
'    Set cur.ActiveConnection = MAINDB
'    cur.Source = "SELECT WasteCode FROM ManifestWasteCode WHERE MWasteID=" & quote(wid)
'    cur.Open
'
'    Do While Not cur.EOF
'        result = result & cur.Fields("WasteCode").Value
'        cur.MoveNext
'        If Not cur.EOF Then result = result & ";"
'    Loop
'
'ExitHere:
'    WasteCodeString = result
'    If cur.State = adStateOpen Then cur.Close
'    Set cur = Nothing
'    Exit Function
'Handle_err:
'    MsgBox Err.Description
'    Resume ExitHere
'End Function


*If you're interested in why that was here's the story behind that: The previous developer had put in code that searched for specific DSNs on individual computers, and if they didn't exist, then it created registry entries on the computer (all this on start up). This has been a problem for us for years, but we've mostly just dealt with it. Since we were moving to a new server and I was going to have to muck around with all the DSNs in some way, I made the decision to move to file DSNs located inside the same folder with the .mdb files. I figured I could use the linked table manager to point to the new DSN file. However, when I started moving databases to SQL Server 2012, something about how this code was making the table connections meant that it was asking me for the file location for every individual table I was relinking, instead of taking that location for all of them at once. These databases (there wasn't just this one, there were four of them, and that meant four production and four development copies I was relinking) all have over 40 linked tables, each. This was unworkable so I chose to delete then recreate the links as more efficient way of accomplishing the task.
 
I'm wondering why he has it making this connection at all. The table it's getting the generator status from is in the database. What does the connection provide here??
 
Can anyone help with this at all? I'm afraid even if I manage a work around for THIS bit, it's going to keep failing. I just don't understand all this connection code he has throughout this database. Can you maybe help me understand this stuff?

Code:
Public MAINDB As ADODB.Connection


Public Sub InitializeMAINDB(dsn As String)
' "server='notreal'; user id=not; password=notreal; database='HazWasteDev'"
    On Error GoTo Handle_err

    If MAINDB Is Nothing Then
        Set MAINDB = New ADODB.Connection
    End If
    If dsn = "DEVPubHaz" Then
        MAINDB.ConnectionString = "Data Source='nonexistingdatasource';" & _
        "User ID='not';Password='notrealpwd';"
        MAINDB.ConnectionTimeout = 30
        MAINDB.Open
    Else
        MAINDB.Open ("DSN=" & dsn)
    End If

ExitHere:
    Exit Sub
Handle_err:
    MsgBox Err.Description
    Resume ExitHere
End Sub
And THIS is confusing me as well:

Code:
Public Function Spawn()
'Invoked at startup (using Autoexec macro)

    On Error GoTo Handle_err
    Dim dbstatus As String
    Dim mdacverstring As String, mdacvermajor As String, mdacverminor As String, mdacver As Single

    mdacverstring = GetRegValue(HKEY_LOCAL_MACHINE, "SOFTWARE\Microsoft\DataAccess", "Version")
    mdacvermajor = Split(mdacverstring, ".")(0)
    mdacverminor = Split(mdacverstring, ".")(1)
    mdacver = Val(mdacvermajor & "." & mdacverminor)
    If mdacver < 2.8 Then
        Call MsgBox("Some software on your machine is not current, and this application will not function properly." & vbCr & _
            "Please contact the ANR Helpdesk and request an MDAC upgrade.", vbCritical, "MDAC Version Check")
        Call Despawn
    End If

    On Error GoTo ConnectFail1
    dbstatus = DLookup("[SettingValue]", "db_Settings", "SettingName = 'STATUS'")
    On Error GoTo Handle_err
    If dbstatus <> "UP" Then
        If dbstatus = "TESTING" Then
            Call MsgBox("Database is being tested. Database features may not work properly or your work may be lost. Proceed with caution.", vbOKOnly, "Caution: Testing with Sample Data")
        ElseIf dbstatus = "READONLY" Then
            Call MsgBox("Database is in Read-Only mode. You will not be able to edit or add data.", vbOKOnly, "Caution: Read-Only")
        Else
            Call MsgBox("Database status is: " & dbstatus, vbOKOnly, "Connect Failed")
            Call Despawn
        End If
    End If

    gUserName = UCase(Environ("USERNAME"))
    'gUserName = "SQLpublic"
    gUserFullName = DLookup("[UserFullName]", "db_User", "UserName=" & quote(gUserName))
    If IsNull(gUserFullName) Then
        gUserFullName = "Staff"
'        Call MsgBox("Your username is not known, database features may not work properly.", vbOKOnly, "Unknown User")
    End If

    Call InitializeMAINDB(gDSN)

    DoCmd.OpenForm "Root"

    ' Hide the database window
    DoCmd.SelectObject acForm, "Root", True
    DoCmd.RunCommand acCmdWindowHide
   
ExitHere:
    Exit Function
    
ConnectFail1:
    On Error GoTo ConnectFail2
    Select Case gDSN
    Case "HazWaste"
        Call ANR_MakeDSNSQL("HazWaste", "SATURN", "HazWaste", "Hazardous Waste")
    Case "HazWasteTEST"
        Call ANR_MakeDSNSQL("HazWasteTEST", "SQLDEV", "HazWaste", "Hazardous Waste Testing")
    Case "HazWasteDEV"
        Call ANR_MakeDSNSQL("HazWasteDEV", "SQLDEV", "HazWasteDev", "Hazardous Waste Development")
    Case Else
        Call MsgBox("Unknown DSN, Please contact technical support.", vbOKOnly, "Unknown DSN")
        Call Despawn
    End Select
    Resume

ConnectFail2:
    On Error GoTo Handle_err
    Call MsgBox("Could not connect to database server.  Please contact technical support.", vbOKOnly, "Connect Failed")
    Call Despawn
    
Handle_err:
    MsgBox "ERROR!" & " " & Err.Description
    Resume ExitHere
End Function
 

Users who are viewing this thread

Back
Top Bottom