I wish to rebuild all A97 tables from scratch - whew! Prefer to automate task.

[MLH]

In an earlier post entitled...

"A97 closes down each time I open a particular report"

it has been suggested that I rebuild problematic table - one in which
some corruption has occurred. I don't know which table is the problem,
so I would like to rebuild them all in a new database. I'm sure most
of you would cringe at the thought of having to do this manually for
most of your applications.

So, I'm writing to ask if any of you have written code to print the
vital elements required to construct your own tables from within code.
Something to automate the process, if you know what I mean. Manually
building a table isn't such a big deal. But 51 of them? That's another
story altogether.

So many individual field properties to keep up with! Wow, there's lots
of room for oversights and outright mistakes. Would like to create a
list of STUFF for each table that I could parse & import into code to
allow me to recreate the tables automatically - inside a VBA class
module.

I'm afraid of simply EXPORTING any existing table (structure only) for
fear of the unknown - I simply don't know whether any corruption that
might be present in any of my existing tables would be transferred to
the copies created.

[MLH]

In the absence of being able to put my finger right on the culprit,
I was going to roll the dice and see what came of it. Do you suspect
anything in particular? What approach would you recommend, if you
yourself were unsure where the problem lay?

But I think table corruption is extremely unlikely as the cause of a
report crashing.

[MLH]

That's a bummer.

But is it not the case that Jet DDL cannot create all the
Access-specific properties?

You'd have to use DAO, and walk the propoerties collection of each
field, and my memory of attempting this is that for some reason,
this doesn't actually work.

[MLH]

I'll give "b" a shot first. I've not experimented with
Application.Sav eAsText or LoadFromText methods. Where can
I read more about it in A97 HELP or on the forum?
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxx

1. something wrong with the report's recordsource. If it's a stored
query, try recreating it by pasting the SQL into a new query, saving
it, then executing and saving it again.

2. something corrupt in the report's VBA module. This can be
addressed one of two ways:

a. the easy, but incomplete way: copy the module into Notepad,
then change the report's HasModule property to FALSE. Then save
it, compact the front end, then open the report, go to the code
window for it and past your module back in. Compile it and save
the report and see if the problem goes away.

b. the complete way: use Application.Sav eAsText to write the
report definition out to a text file, then use
Application.Loa dFromText to rebuild it.

It could also be that the problem is *data-specific* -- it could be
that a particular record, or records with particular types of data
in them are waht is causing the report to crash. That would likely
be a Jet error that is somehow not bubbling up to Access's error
handler.

And if the problem is in any of the areas outlined above, rebuilding
the tables won't have any effect whatsoever. That's the *last* thing
I'd investigate, after doing all of the above.

[MLH]

David, that's an impressive tool. The SaveAsText and LoadFromText
tools, it seems, are largely undocumented - from at least one post
I've read on the forum.

After experimenting with them, they seem indispensable for debugging
a problem like the one I'm having. Would you suggest I process objects
one-at-a-time, writing them to text, then rebuilding them from text to
identify the problematic item?

I sure hope this works. It looks quite promising. I never knew it was
there. Thank-you for the heads-up.

[MLH]

Looking at a post by Tony Toews back in 2002, in which he gave an
example syntax as follows...

Application.Sav eAsText acForm,"MyForm" ,"c:\form.tx t
You can load the file into a new MDB.
Application.Loa dFromText acForm,"MyForm" ,"c:\from.tx t"

.... I'm wondering if all objects in an mdb can be written off to text
files in an automated fashion, using their object names preceeded by
leading text strings like "tbl" "qry" "frm" "rpt" "mac" "bas".
I believe this would prove to be quite useful. ==> Sort of a database
object documenter / ODL.

Anybody know of work that's been done along those lines?

[MLH]

I'm inclined to agree with you. The hit 'n miss nature of my
problem made it hard to troubleshoot. Today, for instance,
I opened the report a few times - no problem whatsoever.

Then, in the last 15 minutes, I opened the report twice.
The first time, A97 instantly closed - no warning, no nothing,
just disappeared and there I was, looking at the desktop.
The second time, I got the familiar "Access has encountered
an error and has gotta close..." thing.

When I restarted & reopened the app, the first thing I did
was use the SaveAsText and LoadFromText tools this way:

Application.Sav eAsText
acReport,"rptPe ndingOutboundCo rrespDetails"," c:\rptPendingOu tboundCorrespDe tails.txt

Application.Loa dFromText
acReport,"rptPe ndingOutboundCo rrespDetails"," C:\DB\TimNall\T owPakObjectText \rptPendingOutb oundCorrespDeta ils.txt"

I renamed rptPendingOutbo undCorrespDetai ls to
rptPendingOutbo undCorrespDetai ls_OLD in between the
SaveAsText and LoadFromText. First time running the newly
created report - perfect - no hint of an error. However, running
the "old" one immediately afterward, it ran fine too. The insidious
nature of this anomoly has been somewhat scary. So, I'm crossing
my fingers.

But, might I ask, is it somehow possible that the actual corruption
itself might be a disk read error that sometimes succeeds & sometimes
does not? Win XP probably has a robust DOS and could be working
real hard to read a bad spot, sometimes succeeding and sometimes not.

[Bob]

Try
http://www.access-programmers.co.uk/...ad.php?t=66320
and
http://www.datastrat.com/Code/DocDatabase.txt

HTH - Bob

[MLH]

Two very nice solutions there, Bob. Can't tell you
how much I appreciate it.

Michael H.

[David W. Fenton]

MLH <CR**@NorthStat e.net> wrote in
news:41******** *************** *********@4ax.c om:

But, might I ask, is it somehow possible that the actual
corruption itself might be a disk read error that sometimes
succeeds & sometimes does not? Win XP probably has a robust DOS
and could be working real hard to read a bad spot, sometimes
succeeding and sometimes not.

If you compact your database, it will end up stored in completely
different sectors of your hard drive, so this is very, very unlikely
to be possible in the first place.

And even if it were, the likelihood that the read error would
produce data that couldn't be detected as corrupt until it's
processed by Access seems remarkably farfetched to me.

It really sounds like a Jet error.

Is the recordsource a stored query or a SQL string?

Try switching from one to the other.

ANd try setting the recordsource in the report's OnOpen event as
another way to test this kind of problem.

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

[MLH]

Well, if it breaks again, I'll try this. But right now, its fixed.
I have not seen the problem again since rebuilding the
report as you suggested. I will be more assured the prob
is gone if, after a few days, it still seems to be gone.

Is the recordsource a stored query or a SQL string? Yes, the recordsource is a stored query.
Try switching from one to the other. (in a few)
ANd try setting the recordsource in the report's OnOpen event as
another way to test this kind of problem.

(again, in a few)(