VB6+DAO+MDB_OnSharedFolder -> Error 3420: Object invalid or no longer set

Hello,

I have a vb6+dao app, mdb database on a shared folder, after a few weeks on a LAN (8 PC's network), the MDB that is about 40 MB's.

I get error 3420.
I never set rs = nothing, I never use rs.close

I suspect that this could be a corrupted database. So my question is, could error 3420 be a symptom of a corrupted database? Or just a lost connection (maybe over a wireless network?).

If it is a corruption problem, is there any tool to analyze for errors? I use compact-repair but it never tells what kind of problems it found. So I would like a better tool/alternative.


Any help would be great!!
Thank you!!
LVL 1
jag_devAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Davidshc76Connect With a Mentor Commented:
1) Declare your variables explicitly ... use Dim rs As DAO.Recordset, Dim dbs As DAO.Database etc etc

2) Try different variations of the the "get the subform" syntax: Set rs2 = Me.sfrmReviewQuote.Form.RecordsetClone    

3) Set a breakpoint BEFORE the trouble line and ensure that the subform's Recordset is indeed available ... type something like this in the Immediate window:  ?Me.sfrmReviewQuote.Form.RecordsetClone.Recordcount        

try this
Tools>Database Utilities>Compact and Repair ...

then do a decompile

http://www.granite.ab.ca/access/decompile.htm
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I never set rs = nothing, I never use rs.close
Bad programmer.  Always destroy your objects after you're done using them to prevent database bloat.

>If it is a corruption problem
Access often does not give you an exact error message when requiring a repair-compact, as the process is a catch-all for all errors, and sadly there is no alternative.
You can, however, write VB6 code that automatically does a repair-compact on your mdb every x vb app startups.  

0
 
jag_devAuthor Commented:
jimhorn,

Well I do destroy my objects, but there is no destroy before the 3420 error.

So, is error 3420 an indication for corruption, or is it common for other things?

thanks
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not sure.  What's the description of the error message you are receiving?
0
 
jag_devAuthor Commented:

The problem is that it is on an exe, not even at my office. And I haven't been able to reproduce the error at IDE, or in my computer.

Because of the logger I have, I know what executes before, and there is no =nothing. So I do not know why I get an error indicating that the recordset is invalid or no longer set!!!!

I always feel creepy when asking MDB questions... I wish it didn't exist.

0
 
jag_devAuthor Commented:
the error source is DAO.Recordset
0
 
jag_devAuthor Commented:
This page contanis corruption symptoms:
http://www.granite.ab.ca/access/corruption/symptoms.htm


but... what about 3420?
Could it be a corruption symptom?

thnks,
jag
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Corruption can be a number of issues, and unfortunately Access does not have any documentation on it.

My guess though is that if you can prove that the error occurs at a line of code that has DAO.Recordset, then it's probably either a coding error, or an uninstall-reinstall version(ing) of DAO error.
0
 
jag_devAuthor Commented:
well, i would really appreciate if somebody has an extra cule about 3420 & corruption.

I will install jet 4.0 sp8, and get back to this forum...  maybe you are right jimhorn. maybe it needs a reinstall.


(in the meantime, if somebody has a code or vbp usefull to prove or check for a corrupted database... please let me know.  [this is so i can send an exe to the client that returns true or false if corrupted or not.])


htnks..
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
There is no VB or Access VBA code that checks for a corrupt database, sorry.

However, the below VB6 code will perform a compact/repair every bRepairCompactFrequency out of bRepairCompactCounter times (e.g. 1 out of 10)

(Modify all cVariables.GetValue() calls to suit your needs.)

Hope this helps.
-Jim
________________

Public Function sb_database_repair_compact() As Boolean

'Repair and Compact the database.
'Returns TRUE if successful, FALSE if not.
'http://support.microsoft.com/default.aspx?scid=kb;en-us;230501&Product=ado
'Requires a reference to Microsoft Jet Replication Object (a.k.a. JRO) Library

On Error GoTo error_handler

Dim bRepairCompactFrequency As Byte, bRepairCompactCounter As Byte, sPath As String

'Grab every variable you need from the db before you break the reference.
bRepairCompactFrequency = CByte(cVariables.GetValue(CONST_REPAIR_COMPACT_FREQUENCY))
bRepairCompactCounter = CByte(cVariables.GetValue(CONST_REPAIR_COMPACT_COUNTER))

sPath = cVariables.GetValue(CONST_PATH_DATABASE)

If bRepairCompactCounter + 1 <= bRepairCompactFrequency Then
    'Not time to repair and compact.  Just incriment the counter and get out.
    If cVariables.SetValue(CONST_REPAIR_COMPACT_COUNTER, bRepairCompactCounter + 1) Then
        sb_database_repair_compact = False
        GoTo exit_function
    Else
        GoTo error_handler
    End If
   
End If

'All code from here down is repair-compact.
'Kill the connection to the existing database
cn.Close

Dim JRO As JRO.JetEngine
Set JRO = New JRO.JetEngine

'Close the connection
Call sb_forms_close_all("frm_splash")

'Repair and compact from data_mdb to data_backupxx.mdb"
Dim sFileBackup As String

'Validate the backup folder
sFileBackup = fn_get_path_parent(sPath) & "Backup\"
Call fn_validate_directory(sFileBackup, faCreate)

sFileBackup = sFileBackup & "data_" & DatePart("m", Date) & "_" & DatePart("d", Date) & "_" & DatePart("yyyy", Date) & ".mdb"

'If there's already a backup file for today, kill it.
If fn_validate_file(sFileBackup) Then
    Kill sFileBackup
End If

Dim sSourceConnection As String, sDestinationConnection As String
sSourceConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath & ";Jet OLEDB:Engine Type=4"
sDestinationConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFileBackup

JRO.CompactDatabase sSourceConnection, sDestinationConnection
DoEvents

'Switch the files...
Kill sPath
FileCopy sFileBackup, sPath

'(Returning a TRUE will automatically reset the counter back to zero.)

sb_database_repair_compact = True

exit_function:
    On Error Resume Next
    Set JRO = Nothing
    Exit Function
   
error_handler:
    sb_database_repair_compact = False
    Resume exit_function
   
End Function
0
All Courses

From novice to tech pro — start learning today.