Object invalid when closing database with DB.Close 1

[Navel12]

I am using VB6 with Access 2000. I am opening as DAO and reading records from a file. As I find the student record it performs the following lookup to locate records in a bound data control recordset. What is happening though is the code as is produces and error 3420 (Object invalid or not set) with the DB.Close. Without the close it appears to leave the database open and I get and error 3048 (Can't open any more databases) after a number of records have been read.

Dim ParmQD As QueryDef
Dim ParmRS As Recordset
Dim DB As Database
Dim SQL1 As String

On Error GoTo errorhandler

strDB = stdDisp.student.DatabaseName

If stdDisp.stud_id.Text <> "" Then

Set DB = Workspaces(0).OpenDatabase(strDB, , , "MS Access;pwd=windjammer")

SQL1 = "PARAMETERS [Student Id] Double; " _
& " SELECT * FROM cogat_scores " _
& " WHERE (ID = [Student Id]) " _
& " order by test_year ASC; "

Set ParmQD = DB.CreateQueryDef("", SQL1)
ParmQD.Parameters![Student ID] = stdDisp.stud_id.Text
Set ParmRS = ParmQD.OpenRecordset(dbOpenDynaset)
Set stdDisp.cogat_scrs.Recordset = ParmRS

DB.Close
Set DB = Nothing
Set ParmQD = Nothing
Set ParmRS = Nothing

' Set record slide to be invisible
stdDisp.cogat_scrs.Visible = False

' Check to see how many records were found
stdDisp.cogat_scrs.Recordset.MoveLast

rcdcnt = stdDisp.cogat_scrs.Recordset.RecordCount
If rcdcnt > 1 Then
stdDisp.cogat_scrs.Visible = True
End If

End If

exitsub:

Exit Sub

errorhandler:

Select Case Err.Number ' Evaluate error number.
Case 3021 ' No Current Record
Resume exitsub
Case Else
errprmpt = MsgBox(Err.Description, vbOKOnly, Err.Number)
End Select

 

[Golom]

Try it in this order

' Set record slide to be invisible
stdDisp.cogat_scrs.Visible = False
                
' Check to see how many records were found
stdDisp.cogat_scrs.Recordset.MoveLast
        
rcdcnt = stdDisp.cogat_scrs.Recordset.RecordCount
If rcdcnt > 1 Then
    stdDisp.cogat_scrs.Visible = True
End If


Set ParmQD = Nothing
Set ParmRS = Nothing
DB.Close
Set DB = Nothing

You are attempting to use a recordset (stdDisp.cogat_scrs.Recordset) after the database is closed. Closing the database will destroy the objects that reference it (i.e. they are no longer set).

 

[Navel12]

Golom,

Thank you... is there a way to keep the recordset usable? I want to see if a record exists in the record set. If so edit the record if not, then add to the recordset. As soon as I close it I lose that ability.

 

[Golom]

Make the database a module-level (or possibly Global) object so that you are not needing to open and close it. Open the database when the module opens and close it only when you have done everything that you need to do.

The other option is to use ADO and retrieve the recordset then set the recordset's connection property to Nothing. That creates a disconnected recordset that you can manipulate independently of the database (or connection in ADO parlance.) You would however need to reconnect to the database if you want to save the changes you have made. Be very careful with this however. Mixing DAO and ADO to access the same database ... particularly if both are making changes ... is fraught with peril as in database corruption on the grand scale.

Unfortunately, DAO doesn't support disconnected recordsets.

 

[Navel12]

Golom-- thanks again. I made DB a global variable but I am still getting an error 3048. Here is the revised code. I don't see where a database is being opened and not closed. Thanks for the help!!

Dim ParmQD As QueryDef
Dim ParmRS As Recordset
Dim SQL1 As String

On Error GoTo errorhandler

If stdDisp.stud_id.Text <> "" Then

SQL1 = "PARAMETERS [Student Id] Double; " _
& " SELECT * FROM cogat_scores " _
& " WHERE (ID = [Student Id]) " _
& " order by test_year ASC; "

Set ParmQD = DB.CreateQueryDef("", SQL1)
ParmQD.Parameters![Student ID] = stdDisp.stud_id.Text
Set ParmRS = ParmQD.OpenRecordset(dbOpenDynaset)
Set stdDisp.cogat_scrs.Recordset = ParmRS

' Set record slide to be invisible
stdDisp.cogat_scrs.Visible = False

' Check to see how many records were found
stdDisp.cogat_scrs.Recordset.MoveLast

rcdcnt = stdDisp.cogat_scrs.Recordset.RecordCount
If rcdcnt > 1 Then
stdDisp.cogat_scrs.Visible = True
End If

Set ParmQD = Nothing
Set ParmRS = Nothing

End If

 

[Raffles666]

You missed:

Set ParmQD = Nothing
Set ParmRS = Nothing
DB.Close
Set DB = Nothing




Old school Alchemy engineer - trying to keep up with the times