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.

[Salad]

MLH wrote:

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.

Don't know. Maybe transfer the empty tables to a new mdb. If any keys
are Autonumber, change them to Long...and remember to change them back
to Autonumber after the update.

Then link to the tables in the old database. All of the linked tables
will have the same table name as the blank table...but with the number 1
at the end of it.

Then write some code to scan all the tables that end in 1 and write the
query code to append them into the blank table.

Then delete all tabledefs that end in 1.

Then change the Longs back to Autonumber.

[Rick Brandt]

MLH wrote:

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.

Sledge hammer approach IMO.

Just import all of the tables from the existing file into a new one. IME
corrupted tables will either refuse to import or will be fixed in the import
process. Alternatively you could import all of the tables using the
"Definition Only" option. If the corruption is merely *data* then you can
repopulate the new tables afterwards.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Danny J. Lesandrini]

I wrote an article for DBJ about using DDL to create tables.
http://www.databasejournal.com/featu...le.php/3460771

That's a lot of work, and I concluded by asking if anyone knew of a tool that
would reverse engineer an Access database into the requisite scripts to rebuild.
Someone provided this tool, which I played with and liked ...

http://www.synametrics.com/Synametri...rogName=WinSQL

They provide a free version called WinSQL Lite or you can purchase the
professional version.

If you just want to document your fields, there are lots of tools and free
code. The DataFast Utility at my site has such a documentor. Just follow
the link in my tag line and click on Downloads. Look for DataFast Utility.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast/

"MLH" <CR**@NorthState.net> wrote ...

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.

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:to********************************@4ax.com:

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 think that's bad advice.

I strongly doubt that the table is to blame for a report crashing.

Much more likely:

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.SaveAsText to write the
report definition out to a text file, then use
Application.LoadFromText 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.

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

[David W. Fenton]

Salad <oi*@vinegar.com> wrote in
news:MV****************@newsread3.news.pas.earthli nk.net:

Then change the Longs back to Autonumber.

Er, you *can't* do that. Once an Autonumber is changed to Long, it's
neer going to be an Autonumber again.

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

[David W. Fenton]

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:zj***************@newssvr13.news.prodigy.com:

MLH wrote:

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.

Sledge hammer approach IMO.

Just import all of the tables from the existing file into a new
one. IME corrupted tables will either refuse to import or will be
fixed in the import process. Alternatively you could import all
of the tables using the "Definition Only" option. If the
corruption is merely *data* then you can repopulate the new tables
afterwards.

Well, that's not necessarily true. Certain kinds of corruption can
be imported along with the data tables. The only certain way to
avoid carrying that corruption along into the new MDB is to rebuild
the tables from scratch.

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

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

[David W. Fenton]

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in
news:xc********************@comcast.com:

I wrote an article for DBJ about using DDL to create tables.
http://www.databasejournal.com/featu...cle.php/346077
1

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.

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

[MLH]

Thx, Salad. As always, very intuitive and resourceful.
I tend to like Danny Lesandrini's suggestion of an "air
product" that doesn't seem to exist, but like Danny, I
wish it did. In the absence of that, I think I'll give Rick
Brandt's suggestion a try. Did you read it? He pointed
out that IHE, corrupted tables were fixed during trans-
port. I didn't know Access was designed that way. But
hey, if it is, that may be my remedy.

Anyone else have substance to add to the notion that
MS Access fixes broken tables during import/export. It
would be quite helpful.

[MLH]

And its an awfully heavy sledgehammer too.

Just import all of the tables from the existing file into a new one. IME
corrupted tables will either refuse to import or will be fixed in the import
process. Alternatively you could import all of the tables using the
"Definition Only" option. If the corruption is merely *data* then you can
repopulate the new tables afterwards.

I certainly have nothing to lose by trying. I'll do just that and
cross my fingers. Any thoughts on Danny Lesandrini's comments?
I would love to get my hands on a nice add-in that methodically
analyzed all database objects manually created, wrote DDL scripts (or
ODL scripts - object definition language) and rebuilt database objects
from scratch upon running the script. Ever heard of anything like
that? I know how much coding goes into partially rebuilding or
restructuring a form to fit in an environment running a different
screen resolution. Its no cakewalk.

[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.SaveAsText or LoadFromText methods. Where can
I read more about it in A97 HELP or on the forum?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx x

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.SaveAsText to write the
report definition out to a text file, then use
Application.LoadFromText 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.SaveAsText acForm,"MyForm","c:\form.txt
You can load the file into a new MDB.
Application.LoadFromText acForm,"MyForm","c:\from.txt"

.... 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.SaveAsText
acReport,"rptPendingOutboundCorrespDetails","c:\rp tPendingOutboundCorrespDetails.txt

Application.LoadFromText
acReport,"rptPendingOutboundCorrespDetails","C:\DB \TimNall\TowPakObjectText\rptPendingOutboundCorres pDetails.txt"

I renamed rptPendingOutboundCorrespDetails to
rptPendingOutboundCorrespDetails_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**@NorthState.net> wrote in
news:41********************************@4ax.com:

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)(

[Salad]

MLH wrote:

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)(

One other option, if it continues to blow up, is to try a /decompile
switch on the icon to open Access. It will decompile the code. Then
recompile all/save.

[David W. Fenton]

Salad <oi*@vinegar.com> wrote in
news:5%******************@newsread2.news.pas.earth link.net:

MLH wrote:

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)(

One other option, if it continues to blow up, is to try a
/decompile switch on the icon to open Access. It will decompile
the code. Then recompile all/save.

Well, a decompile is a little more complex than that. Here are the
stops for the most successful decompile:

1. backup the MDB

2. compact it

3. decompile it

4. open the MDb in a new instance of Access (never use the one
opened to decompile for anything but the decompile) and compact it
again.

5. compile and save all.

6. compact the MDB.

It's important to not leave out any of these steps because you want
to make sure that all the discraded data pages within the MDB are
removed before you do the compile and save all.

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

[MLH]

I guess I'll have to give this a try. Just blew again on me. It is
frustrating, not knowing where the corruption lies. And even
moreso when the nature of the corruption is such that evidence
of the problem inconsistent.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxx

One other option, if it continues to blow up, is to try a
/decompile switch on the icon to open Access. It will decompile
the code. Then recompile all/save.

Well, a decompile is a little more complex than that. Here are the
stops for the most successful decompile:

1. backup the MDB

2. compact it

3. decompile it

4. open the MDb in a new instance of Access (never use the one
opened to decompile for anything but the decompile) and compact it
again.

5. compile and save all.

6. compact the MDB.

It's important to not leave out any of these steps because you want
to make sure that all the discraded data pages within the MDB are
removed before you do the compile and save all.

[Salad]

David W. Fenton wrote:

One other option, if it continues to blow up, is to try a
/decompile switch on the icon to open Access. It will decompile
the code. Then recompile all/save.
Well, a decompile is a little more complex than that. Here are the
stops for the most successful decompile:

1. backup the MDB

Check,
2. compact it
Check
3. decompile it
Check
4. open the MDb in a new instance of Access (never use the one
opened to decompile for anything but the decompile) and compact it
again.
Huh? If I add /excl /decompile it decompiles and opens the database.
So the database is open and it tells me I should now Compile the code.
Should the database be closed and reopened w/o the decompile switch and
then compacted? IS that what #4 means?

5. compile and save all.
Check
6. compact the MDB.
Check.

These are basically the steps I use...except for #4 which I don't
understand.
It's important to not leave out any of these steps because you want
to make sure that all the discraded data pages within the MDB are
removed before you do the compile and save all.

[David W. Fenton]

Salad <oi*@vinegar.com> wrote in
news:LO***************@newsread3.news.pas.earthlin k.net:

David W. Fenton wrote:

One other option, if it continues to blow up, is to try a
/decompile switch on the icon to open Access. It will decompile
the code. Then recompile all/save.

Well, a decompile is a little more complex than that. Here are
the stops for the most successful decompile:

1. backup the MDB

Check,

2. compact it

Check

3. decompile it

Check

4. open the MDb in a new instance of Access (never use the one
opened to decompile for anything but the decompile) and compact
it again.

Huh? If I add /excl /decompile it decompiles and opens the
database. So the database is open and it tells me I should now
Compile the code. Should the database be closed and reopened w/o
the decompile switch and then compacted? IS that what #4 means?

Yes, because MichKa advised us that the decompile switch puts Access
in a state that may not be good for doing anything else.

5. compile and save all.

Check

6. compact the MDB.

Check.

These are basically the steps I use...except for #4 which I don't
understand.

I don't know if it does cause problems, but MichKa very strongly
advised against using any instance of Access opened with the
decompile switch for any purpose other than the decompile. He said
to close it and do anything else in a fresh instance of Access.

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

[Salad]

David W. Fenton wrote:

Salad <oi*@vinegar.com> wrote in
news:LO***************@newsread3.news.pas.earthlin k.net:

David W. Fenton wrote:

One other option, if it continues to blow up, is to try a
/decompile switch on the icon to open Access. It will decompile
the code. Then recompile all/save.

Well, a decompile is a little more complex than that. Here are
the stops for the most successful decompile:

1. backup the MDB

Check,

2. compact it

Check

3. decompile it

Check

4. open the MDb in a new instance of Access (never use the one
opened to decompile for anything but the decompile) and compact
it again.

Huh? If I add /excl /decompile it decompiles and opens the
database. So the database is open and it tells me I should now
Compile the code. Should the database be closed and reopened w/o
the decompile switch and then compacted? IS that what #4 means?

Yes, because MichKa advised us that the decompile switch puts Access
in a state that may not be good for doing anything else.

5. compile and save all.

Check

6. compact the MDB.

Check.

These are basically the steps I use...except for #4 which I don't
understand.

I don't know if it does cause problems, but MichKa very strongly
advised against using any instance of Access opened with the
decompile switch for any purpose other than the decompile. He said
to close it and do anything else in a fresh instance of Access.

Gotcha. Thanks for the update. Like I said, that's pretty much what I
did...but closing after decompile and then compacting then recompiling.