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*********@hot mail.com
http://amazecreations.com/datafast/

"MLH" <CR**@NorthStat e.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**@NorthStat e.net> wrote in
news:to******** *************** *********@4ax.c om:

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.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.

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

[David W. Fenton]

Salad <oi*@vinegar.co m> wrote in
news:MV******** ********@newsre ad3.news.pas.ea rthlink.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*********@ho tmail.com> wrote in
news:zj******** *******@newssvr 13.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*********@ho tmail.com> wrote in
news:xc******** ************@co mcast.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.