Database - lost most forms upon conversions 1

[MakeItSo]

Hi friends,

I am having a bit of trouble with an old Access database:
Once upon a time, a customer had an Acc. 97 database (or rather two, a frontend holding the forms and stuff and a backend holding the data).
As the frontend required heavy extensions programming wise, I converted it to 2000. I took all safety precautions as I know of the problems that may occur when converting a database with forms containing VBA code.

All was well for quite some time, backups were regularly made a.s.f.

Now, more than three years after I (and the customer) last accessed the MDB, the customer cried for help:
He wanted to check certain data and naturally tried to do that with the frontend.
However: he meanwhile no longer has Acc.97, neither 2000, but 2003.
Result: the frontend opens - but almost all the forms are gone!!

I checked on my computer (yes, of course no older Office version available either) - same result.
No matter whether I "open" or "convert" the db, all vital forms are gone!
They are still in the "MSysObjects" table but else gone from the face of the earth and - naturally - inaccessible.


It still contains all queries, macros, reports (not all of the reports I think), table links.

Does anybody have a clue why this happens and what can be done to secure the frontend in its functional state?
(Still have backups)

Thanks!!

Cheers,
MakeItSo

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[lameid]

I'd check the backups to see when the mdb was last updated before it was opened after 3 years. This should give you some clue as to when the problem really occured.

The other question would be is the MDB in the Server's AV event log as being "cleaned". You may have to keep the AV software from scanning MDB's (temporarily or permanently). The AV product may have fixed this bug since it hosed your mdb or not. They may or may not admit to it on their website.

Once you have a copy from backup that you think is good and defintely not eaten by AV, try to open a copy. Hopefully all is well.

If not, frontend's don't change that often. If you are lucky you have notes of when changes were made. Try a really early backup and see if it works. Honestly it should work independent of version... I assume you are fully patched and have current AV software loaded yourself.

Then there is always the posibility of trying to use the restored file for the first time in a radically different environment and seeing if it works (like not your's or the company's computer).

Those are my first place to start ideas. Retoring backups for diagnostic purposes is fun

 

[alvechurchdata]

I've heard of macros and code modules being eaten by AV software but never forms so I'd suspect that somebody's meddled with the database. If it's been lying unused on the client's computer for three years then there's been any number of people who might have been tempted to try to "improve" it. What does [DateUpdate] hold in the MSysObjects table? Does it match the last date that you worked on it?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[MakeItSo]

It's definitely not the AV.
1) I've tried this on three different computers with different AVs now - three times no lady.
2) I've just decativated my AV and opened an original backup from 2006 - same shit.
3) I know 100% for sure that the 3-year-old version was fully functional when I last accessed it. I was also the only one doing any programming on it back then.

Thank you for the hint towards "dateUpdate" in the MSysObjects. it says:

Tadaa: Today.

Ergo: Access func.

Now what?
[flush]

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[alvechurchdata]

Tadaa: Today.

Sorry about that. It seemed like such a good idea earlier this morning...

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[lameid]

The next thing I'd try is importing a freshly restored files objects into a database file of another version.

After that, I would load up a copy of Access 2000 if it works, make sure everything is in Access 2000 format, compact and repair, verify it still works in Access 2000, backup the files (I'd hate to lose all those steps) and then open in Access 2003.

If that doesn't work take, my first recommendation of this post on the compacted restored copy.

If that doesn't work, within Access 2000, try importing everything into a fresh MDB, repeat paragraph two on this version.

If that doesn't work, try importing a form one at a time into Access 2003 and see which one doesn't work (obviously if the first one doesn't, start over and import the second). You may be able to import half of them at a time to determine that a particular set is good.

After that the only option I can think of... within Access 2000 you can programatically dump the form names and controls etc. to tables and reverse the process in Access 2003. I have a good start to the first half or dumping if you get this far post back.

Obviously that last option is a last ditch effort and a real pain. Hopefully one of the other ideas works or the one guy that has figured it out before posts.

Please keep us up to date.

 

[MakeItSo]

I need to know WHY and HOW this is happening.
I don't have a running copy of Access 2000 anymore. If I had i wouldn't need to convert and the whole jazz wouldn't happen to begin with.

Thanks for the import hint! Although importing into another file is generally a bad idea because the forms contain VBA code which will lead to database corruption upon import.
This is a known Access problem, I even wrote a FAQ upon that. (faq705-4683)
I've tried nonetheless. I can even select the old forms for import, so I know they are still there!!! (first success)

It then went as feared: Access crashed with a "The VBA Project in your file is damaged" error, the final result being the same as before.

The main problem seems to be: how can I keep my forms if I do not have an old Access at my disposal?

Or: can I somehow restore my forms after conversion even some or all of the contained VBA code would go to hell?


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[lameid]

Have you tried a compact and repair of the front end in Access 2003 on the 2000 file? You might do well to find the jetcomp utility and try that.

Can you get any of your forms by importing one at a time?
Is your backend in 2000 format? If not you might try converting it before converting the frontend.

 

[lameid]

The faq you reference also suggests a decompile... something I forgot to metntion but worth trying as well.

Actually that is what you should probably do first, then compact repair then Backup. If it doesn't open correctly try importing from the backup.

Ultimately, you may need to locate a copy of Access 2000. Is there a fulltime IT person at the company that you can nudge for an old version? Maybe another client?

 

[MakeItSo]

Is there a fulltime IT person at the company

Yeah! Me!
Alas: no (running) Access 2000 available.
Decompiling / Compact/Repair all lead to a conversion and hence back to the same error message.

I am currently trying to restore an Access 2000 copy that still lingers in a HD backup of an old computer I have here.
Given M$s harsh registry settings I doubt that I'll get it to run but I'll give it a try.

Will post back later.


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

Of course it won't run... :-//

But I've got a new idea:

I'll try via Visual Basic. Using the old Jet engine, I should be able to extract all VBA code from all forms without need for converting.
THEN the stuff should be able to decompile, compact etc. and finally open.

Will keep you updated.


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

Aargh!
!Can't access the VBProject of the database without using Access!


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[lameid]

If you are volume licensed, you have the right to install earlier versions, it is just a matter of aquiring a copy.

An alternative is e-bay... Or find someone who is selling a computer with office Pro 2000 on craigslist and arrange to borrow it.

Maybe an employee has it at home?

I wish I had a really good suggestion but I do not see away around messing with it in an earlier version.

 

[maxhugen]

Believe it or not, I still use 97 and 2000, as well as 2003! Some clients just won't upgrade (and I can't really blame them).

It does sound as though you may have some corruption in the A2000 forms &/or reports, since they should migrate to 2003 without probs.

If you like - and confidentiality is not crucial to you - you can send me the A2000 mdb, and I'll see if I can fix it, plus create an A2003 mdb. Your call.

My email is max AT hugen dot com dot au.

Max Hugen
Australia

 

[MakeItSo]

It does sound as though you may have some corruption in the A2000 forms &/or reports, since they should migrate to 2003 without probs.

Actually I would be more than surprised were there NO corruption.
I think I was the 4th or 5th programmer working on that frontend. My predecessors had been hired to implement new funtionality and pretty much screwed up the entire system with it!
I then had the honour to bend it all back to a correctly working state and implement the required new functioningality.

Confidentiality is no problem although I recommend not looking at the code used. You cannot (should not!!) learn from THAT and it may cause severe heart attacks...
The data is stored in the backend anyway.

Your offer is greatly appreciated!!!
I'll send it over.


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[lameid]

maxhugen, Awesome.

I forgot the option... wait for someone who has Access 2000 available to offer to fix it

This reminds me of someone's tagline... It's not what you know it is who is on tek-tips.

 

[maxhugen]

G'day MakeItSo.

Don't worry, I won't nit-pick the code... totally understand the issues of having to try to fix someone else's app!

I'll just do my best to get you uncorrupted versions in A2000 and A2003.

FWIW, I've had some pretty ugly crashes in A2003, so I've resorted to backing up using a function that exports all objects as text, using the undocumented functions Application.SaveAsText and Application.LoadFromText. If anyone wants the code, let me know and I'll post it.



Max Hugen
Australia

 

[maxhugen]

A number of coding issues causes probs, A97 would neither compile nor decompile, but all fixed now, and converted versions in A2000 and A2003 are on their way to you.

Cheers

Max Hugen
Australia

 

[MakeItSo]

You are a champ, Max!


Still some coding issues with data manipulation within forms.
That's completely irrelevant though. I simply commented out these parts.

All's superb!

Too bad I can only star you once for it.


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell