Modules lost

  • Thread starter Thread starter pirotz
  • Start date Start date
P

pirotz

Hello to everybody!

Something went wrong when I decided to initialize the database data
using a loop like this:

For t in CurrentDB.TableDefs
CurrentDB.Execute "DELETE * FROM " & t
Next

I am afraid I deleted some important data stored into the system tables...

I'm not worried about data (I have backup for them, as you can imagine)
but - don't know why - the module object that contained all the code has
disappeared and so did the queries and the forms.

I'm sure that it is still inside the .mdb file (by the filesize, you
know) and I could even list its presence using some recovery tools that
however were only designed to recover data from tables. Alas!

Does anybody know how to extract/recover the module from the .mdb file?

Thanks for help.
 
Hopefully you have a backup of your front-end too. Since the system tables
keep track of all of the objects in the database, you deleted the reference
to the object. The fact that the size hasn't changed doesn't really prove
anything: space isn't reclaimed until a compact is done.

If you don't have a back up, Tony Toews lists some recovery services in his
Corrupt Microsoft Access MDBs FAQ at
http://www.granite.ab.ca/access/corruptmdbs.htm

In the future, to skip system tables, you can use:

For t in CurrentDB.TableDefs
If (t..Attributes And dbSystemObject) = 0 Then
CurrentDB.Execute "DELETE * FROM [" & t.Name & "]"
End If
Next t
 
Hi, Pirotz.
I am afraid I deleted some important data stored into the system tables...
Click to expand...

Specifically, you deleted the rows in the MSysAccessObjects system table.
This is not recommended.
but - don't know why - the module object that contained all the code has
disappeared
Click to expand...

Perhaps this is a good thing. You wouldn't want someone to accidentally
execute that "initialize database" code ever again.
and so did the queries and the forms.
Click to expand...

The rows in the MSysAccessObjects table used to point to where those objects
are in the data pages within the database file. Now that they don't, Jet
can't retrieve them for display in the Database Window. But since the
MSysObjects and MSysQueries tables should still be intact (because you don't
have delete permissions on those system tables), your queries should still
exist, even after you reopen your database. The forms, reports, modules,
and macros will be gone once you close the database, because Jet marked them
for deletion upon the record deletion of the objects in the
MSysAccessObjects table, and Jet will continue with its housekeeping chores
which overwrite the data in those data pages no longer needed. If you
hadn't closed the file, a file recovery service may have been able to
recover at least some of the deleted objects for you if you'd copied your
file while it was still open and sent it to them.
I'm sure that it is still inside the .mdb file (by the filesize, you know)
Click to expand...

Objects and rows marked for deletion remain in the file until the next
compaction, so the file size remains the same. However, Jet is free to
reuse that file space for its housekeeping chores, so it overwrites the data
marked for deletion. Attempting to recover data marked for deletion mostly
results in recovering garbage. Typically, up to 5% of deleted rows in a
table may be recovered, but recovery of deleted tables and queries can
usually be 100% successful if the recovery is immediate.
I could even list its presence using some recovery tools that however were
only designed to recover data from tables.
Click to expand...

Those recovery tools were reading the name of the module in the MSysObjects
table. That doesn't mean that the module still exists within the file. And
even if it does, it doesn't mean that it isn't already mostly garbage data
as a result of Jet's housekeeping chores.
Does anybody know how to extract/recover the module from the .mdb file?
Click to expand...

Your best bet is restoring from a recent backup. The next alternative is an
Access database recovery service as Doug recommended, but that prospect is
likely to be very expensive and very disappointing.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
Back
Top