Trying to recover corrupt VBA code

[kjcmas]

Hello all,

My db crashed and corrupted. I've managed to recover my data, sort of. I created a new db and copied the tables. I had to change the autonumber fields of the copied tables to a regular number field so I could retain my primary keys until I can figure out what to do. I'll take any advice you may have reagrding that. Although unlikely, is there a way to force access to accept MY values into the autonumber field... maybe through some magical VBA code? I'll keep my fingers crossed.

I believe it was a form or some VBA code that corrupted the db. I can recover alot of it from a backup... but not all. The corrupted db won't let me oped the VBA window so I can't see the code. A friend of mine had a corrupted db once. He's a MS certified partner and so he called MS and they had him open the DB in notepad, make some very specific character changes and that fixed it. Lucky for him I guess.

Out of curiosity, I opened (a copy of) my corrupted db in notepad. as i scrolled through, I'd recognize something here and there, but what I did notice is that my VBA code is embedded in all the extended characters. I'm thinking that my code is still in there. How can I get it? Is there any merit to this notepad magic?

Kevin

 

[RoyVidar]

For the Autonumber thingie, no problem

1 create a new blank copy of the table (copy/paste in the database window - choose structure only)
2 make your pk field (in the new empty table) autonumber
3 create an append query, and append all the records from the other table

Resetting the seed/increment (before you create relationship/referential integrity), just run

ALTER TABLE myTable ALTER COLUMN myAutoNumField COUNTER(Start, Incr)

Replace Start with the number from which you wish the next autonumber to start with, and Incr with the increment you wish to use (1?)

I've never heard of the Notepad thingie, what I will try to do, is first /decompile, then, if that doesn't work, I'll use saveastext/loadfromtext (hidden/undocumented methods of the application object).

For /decompile, check out the detailed step by step instructions here http://www.granite.ab.ca/access/decompile.htm Note - it is important that you exit Access between 2 and 3! Compact/repair in another instance of Access than you've decompiled with.

saveastext, check out Arvin Meyers document here http://www.datastrat.com/Code/DocDatabase.txt
to load it again, use LoadFromText

I'm not sure this will work with your degree of corruption, but why not at least try on a copy?

I've heard that sometimes, you might get lucky when opening with a newer version of Access, but I've never tried.

 

[kjcmas]

I'm trying to use the saveAsText thing. I got it to work on a good non-corrupt database. made a copy pasted the module and ran it. it was awesome. just what i need for making quick copies of all my VBA code.

i'd like to try to run it on the corrupt db that i have. the corrupt db will not let me open the VBA editor. so i'll need to run it from a new db. i've created the new db and i've got the module ready to go. i'm trying to figure out how to run it from the new db on a known GOOD copy of another db as a baseline comparison to find out if it'll even work on a db other than the current one... if i can get that to work then i'l try it on the corrupt one.

i'm trying to modify
Set dbs = CurrentDb()

to reference the other db. So i'm trying to use
Set dbs = DBEngine.Workspaces(0).OpenDatabase("C:\Database\mydb.mdb")

Am i even close? because when i try to run the module i get a message that says, 'You canceled the previous operation'.

What am i doing wrong?

 

[RoyVidar]

Whether it will work or not, I don't know, but it might be worth a try

dim a as access.application ' or object
set a = getobject(<path and name of the db>)
a.saveastext ac<objecttype>, "name", "path and name.txt"

If you want to loop some collections, you could probably use the a.currentdb, but it might perhaps be safer taking it one object at a time, typing it's name, in case looping the collections might make it worse?

 

[kjcmas]

.saveAsText & .loadFromText Confusion

I'm trying to learn how to use the 'loadFromText' feature. i've got Arvin Meyers code. do i just change the .saveAsText to .loadFromText?

that doesn't seem to be working. it appears to be reloading a copy of the original module that uses the .saveAsText stuff.

i'm confused.

Kevin

 

[RoyVidar]

saveastext is supposed to dump the objects from the database to text files. The beauty of it, is that it'll grab the "textual representation" of these objects, if the corruption isn't gone to far, and not the compiled bits, which is what is usually corrupted. So, if successful, this is able to strip the objects of any code corruption.

The loadfromtext, as the name indicates, is supposed to load these objects from their textual representation (text files), and import as forms, reports, queries, modules, macros into a (new?) database - when doing this to resolve corruption, it would/should be a new database.

So yes, loadfromtext is supposed to take the textual representation (the text file), and import as the object type you're specifying it should be.

You would probably loop the files, and dependent on the prefixes (if you're using regular naming conventions), "frm", you'll do acform, "rpt" acreport - or you could rewrite Arvins code to dump with different extensions (<name>.rpt for reports...)

A simplistic start of a such routine, could look something like this (typed off the top of the head)

dim s as string, l as long, d as string
d = <mydirectory>
s = dir(d)
do while s<>""
select case left$(s,3)
case "frm"
l = acform
case "rpt"
l = acreport
...
end select
application.loadfromtext l, left$(s, len(s)-4), d & s
s=dir
loop

the interesting, though, is - did you get your objects out of the corrupted db? If so, you probably do have uncorrupted representations of all these objects on your hard drive, ready to import into a fresh new db.

 

[Carlos Urbina]

Whether it will work or not, I don't know, but it might be worth a try

dim a as access.application ' or object
set a = getobject(<path and name of the db>)
a.saveastext ac<objecttype>, "name", "path and name.txt"

If you want to loop some collections, you could probably use the a.currentdb, but it might perhaps be safer taking it one object at a time, typing it's name, in case looping the collections might make it worse?

I went to the trouble of registering just so I could thank you for the simple a.saveastext snippet. It was a great help to me. Thanks, Roy. Guys like you make the internet great!

 

[annieee]

Well..,, If all the above resolutions fail to recover VBA codes, then I would suggest you to use a third-party tool.I have read that these tools also recover password-protected VBA codes. Go to "datarecovery-info" website for detailed information of such products.