The visual basic for Applications project in the database is corrupt

Hi,
Access 2003. Running over terminal server on a Windows Server 2003 box.
The error occurs on every machine that I've tried copying the file to.
Latest Jet SP & Office SP. Database hasn't been converted. A mixture of
modules, forms, linking tables & temp. tables at the access end.
Exporting to a new database doesn't work (same error). Importing from a
new database doesn't work (same error). Trying to open any form or module in
code view is impossible (same error). Permission are set at
HKLM/Soft.../Micros.../Office/XX [version] for everyone to full control.

Can anyone help? I'm not fussed about the data - but we spent two full
days and nights work on code modules- and made no record of the development
process. I'd love to get that work back if possible.

- Good "de-corruption" tool?
- Any way of getting to the code (splitting the vb parts out of the
mdb?)

We haven't backed up the database recently enough to merely roll
back. Yes - it is our own fault. We'll be backing up daily rather than twice
weekly now.

Thanks,

Nick Stansbury
Analyst, Sage Partners

On Jul 15 2004, 05:57 am, "Nick Stansbury" <nickDOTstansbury@sage-
partners.com> wrote in news:cd5k3h$3gk$2@phys-news-1.nl.colt.net:
[color=blue]
> - Any way of getting to the code (splitting the vb parts out of the
> mdb?)
>[/color]

Did you try SaveAsText? If that fails, which I think is likely, try
pksolutions.com.

--
remove a 9 to reply by email

In message <cd5k3h$3gk$2@phys-news-1.nl.colt.net>, Nick Stansbury
<nickDOTstansbury@sage-partners.com> writes[color=blue]
>Hi,
> Access 2003. Running over terminal server on a Windows Server 2003 box.
>The error occurs on every machine that I've tried copying the file to.[/color]

That could be a bogus error message. I remember seeing it when I was
moving A2000 databases around a few years ago. IIRC it meant that one of
the linked tables referenced in a query couldn't be opened because there
was no DSN on the machine the file had been moved to. It was nothing at
all to do with VBA.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[color=blue]
> Did you try SaveAsText? If that fails, which I think is likely, try
> pksolutions.com.[/color]

SaveAsText? Not being an access guy I'm a bit lost - how can I run any vb
routines without being able to get into the VB editor?

In message <zmbhB5AmSn9AFwm9@shrdlu.com>, Bernard Peek <bap@shrdlu.com>
writes[color=blue]
>In message <cd5k3h$3gk$2@phys-news-1.nl.colt.net>, Nick Stansbury
><nickDOTstansbury@sage-partners.com> writes[color=green]
>>Hi,
>> Access 2003. Running over terminal server on a Windows Server 2003 box.
>>The error occurs on every machine that I've tried copying the file to.[/color]
>
>That could be a bogus error message. I remember seeing it when I was
>moving A2000 databases around a few years ago. IIRC it meant that one
>of the linked tables referenced in a query couldn't be opened because
>there was no DSN on the machine the file had been moved to. It was
>nothing at all to do with VBA.[/color]

I'm not working for that company any more so I can't refer to my notes
at the time. but more of the details are coming back to me.

The problem was that there was a form in the database that couldn't open
its data source because the source used a linked database that was not
available.

I would start by creating a clean database and importing or re-creating
the tables and queries in it. Verify that each table can be opened, fix
any that can't. If you can open all of the tables and queries then the
problem is something else.

Once all of the tables are fixed you should be able to import the forms
and open them, unless there's a completely different reason for the
error message.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

"Nick Stansbury" <nickDOTstansbury@sage-partners.com> wrote:
[color=blue]
> - Good "de-corruption" tool?[/color]

Decompile is the only thing I know of.

Decompile or how to reduce Microsoft Access MDB/MDE size and decrease start-up times
http://www.granite.ab.ca/access/decompile.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

"Nick Stansbury" <nickDOTstansbury@sage-partners.com> wrote:
[color=blue]
>[color=green]
>> Did you try SaveAsText? If that fails, which I think is likely, try
>> pksolutions.com.[/color]
>
>SaveAsText? Not being an access guy I'm a bit lost - how can I run any vb
>routines without being able to get into the VB editor?[/color]

Good point. Actually excellent point. You can't reference a utilities MDB as
that's buried in the VBA editor.

I was going to post the following but ignore it now.

The undocumented SaveAsText and LoadFromText may help if an object is corrupted or
otherwise behaving weirdly.
At the debug/immediate window type:
Application.SaveAsText acForm,"MyForm","c:\form.txt
You can load the file into a new MDB.
Application.LoadFromText acForm,"MyForm","c:\from.txt"

Sample code at http://www.datastrat.com/Code/DocDatabase.txt

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

"Nick Stansbury" <nickDOTstansbury@sage-partners.com> wrote:
[color=blue]
> Access 2003. Running over terminal server on a Windows Server 2003 box.
>The error occurs on every machine that I've tried copying the file to.
>Latest Jet SP & Office SP. Database hasn't been converted. A mixture of
>modules, forms, linking tables & temp. tables at the access end.[/color]

This may sound odd but the same message is displayed if it's an MDE in A2003 format
and you try to run it in A2002. Now I doubt very much it's an MDE and you are trying
to open it in A2002 as your posting sounds reasonably competent.

I'm not quite sure why I'm mentioning this even but oh well.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Tony Toews <ttoews@telusplanet.net> wrote in
news:814df0t1i10fq8j3r57o3ee288u1uidb04@4ax.com:
[color=blue]
> "Nick Stansbury" <nickDOTstansbury@sage-partners.com> wrote:
>[color=green]
>> Access 2003. Running over terminal server on a Windows Server
>> 2003 box.
>>The error occurs on every machine that I've tried copying the file
>>to. Latest Jet SP & Office SP. Database hasn't been converted. A
>>mixture of modules, forms, linking tables & temp. tables at the
>>access end.[/color]
>
> This may sound odd but the same message is displayed if it's an
> MDE in A2003 format and you try to run it in A2002. Now I doubt
> very much it's an MDE and you are trying to open it in A2002 as
> your posting sounds reasonably competent.
>
> I'm not quite sure why I'm mentioning this even but oh well.[/color]

It's an erroneous error message, and you have just documented for
the Google Groups archive that it is, in fact, an error. Your post
could someday save someone some work.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

On Jul 15 2004, 08:31 am, "Nick Stansbury" <nickDOTstansbury@sage-
partners.com> wrote in news:cd5t47$e4u$1@phys-news-1.nl.colt.net:
[color=blue]
>[color=green]
>> Did you try SaveAsText? If that fails, which I think is likely, try
>> pksolutions.com.[/color]
>
> SaveAsText? Not being an access guy I'm a bit lost - how can I run any vb
> routines without being able to get into the VB editor?[/color]

Can you get into the Debug window? Try Ctrl+G. If that works, in the Debug
window type

Application.SaveAsText acForm, "formname", "path_to_file"

If that works, repeat for all modules in the database, then load them into
a new database using LoadFromText with similar syntax.

In fact, you may try the Objects as Text add-in from
http://www.users.cloud9.net/~dfurman/code.htm, which provides a GUI for the
above.

If you cannot get into the Debug window, then another option would be to
open the database in an instance of Access via automation and use
SaveAsText on that instance, to avoid loading the corrupt project in the
VBE. Post back if you need details on how to do that.

As Tony said, decompile is worth a try too.

--
remove a 9 to reply by email

For purposes of completeness & for any poor soul that tries to track this
one down through google groups at a later date I thought I'd post a follow
up report.

Having tried all of the suggested solutions posted by the various kind souls
who suggested them, we were unable to make anything work. The corruption ran
so deep that absolutely nothing would work - we couldn't even open an
immediate debug window - it just produced the same error.

None of the commercial data recovery firms that we approached could help -
they couldn't get to the vb code. None of the commercial tools tried worked
either.

Lessons learnt - twofold - 1) Don't hit C+Alt+Del when an access form is
running. Ever! especially not when it is in the middle of a for each loop!
2) Backup. Backup. Backup. Then, er; backup.

If anyone ever discovers a solution to this do post it up here - I'd love to
know how it got fixed.

Nick

"Nick Stansbury" <nickDOTstansbury@sage-partners.com> wrote in
news:cd83sh$a8q$1@phys-news-1.nl.colt.net:
[color=blue]
> None of the commercial data recovery firms that we approached
> could help - they couldn't get to the vb code. None of the
> commercial tools tried worked either.[/color]

Did you try Peter Miller of PKSolutions?
[color=blue]
> Lessons learnt - twofold - 1) Don't hit C+Alt+Del when an access
> form is running. Ever! especially not when it is in the middle of
> a for each loop![/color]

Why not Ctrl-Break? In nearly all cases, that stops the code (though
it may take time to show up).
[color=blue]
> 2) Backup. Backup. Backup. Then, er; backup.[/color]

Multiple redundant backups is a given. I have seen way too many
situations where two different backup methods failed at the same
time.

And, of course, be sure to test regularly that your backups can be
restored from.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Dimitri Furman <dfurman@cloud99.net> wrote:
[color=blue]
>In fact, you may try the Objects as Text add-in from
>http://www.users.cloud9.net/~dfurman/code.htm, which provides a GUI for the
>above.[/color]

Added to my forms corruption page.

Thanks, Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm