Cannot Delete Report - Access shuts down

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, Guys!

I have a fairly large Access 2002 database containing some 350 queries,
forms, reports and tables. (I say this because I'm not sure of any
limitations relating to this in Access.) As part of my VB logic in one of
the forms, I copy a report to a different name, run the report, then delete
it. (This is done so that the file name will be unique when the .pdf output
is created.)

Yesterday, after adding a new field and a couple of queries to the system, I
performed this copy/print/delete routine and received the "MS Access has
encountered an error and will close" message. After doing a little research,
the error is generated each time I try to delete a specific report (one of
the copied ones). The report name shows in the list in the database window.
If I manually select it, the error occurs. If I try to use VB code to delete
it, the error occurs.

In addition, the selection process for reports is very slow. For example,
when I manually select (highlight) one report, then manually select another
one, it takes the system 3-5 seconds to highlight the second entry. There
are only 27 reports (including the "bad" one) and I do not see this slow
selection in the tables, queries or forms window even though there are far
more queries and tables.

I've tried doing a compact and repair, and have even copied the database to
a new name. Have the same result in the copied database. The new field
added yesterday was not related to anything on the "problem" report.

Any way to fix this?

Thanks,
BruceS
 
Hi, Bruce.

You likely have some amount of corruption. I'd recommend starting fresh by
creating a brand new database file, then importing the objects, one group at
a time, from the the old database into the new database. First import the
tables, then import the queries, then the modules, et cetera, but skip the
report that seems to be causing problems. When finished, compile the code
and compact the new database.
In addition, the selection process for reports is very slow.
Click to expand...

You have some 350 objects and you're wondering why it's slow to pick one of
the objects that's been saved in the monolithic record? Jet is digging
through a lot of paraphernalia to find and display the item you selected.
The larger and more fragmented the file, the longer this will take. And if
you have this file stored on a network server instead of your own hard drive,
this will take quite a bit of time to transfer data across the network, too.

Is this database split? If it's in a multiuser environment, then it should
be, but if you are the only one using the file, then you may want to split it
anyway to increase some of the performance because the data will reside in
another file, and your front end (with the queries, forms, reports, et
cetera) would be much smaller and faster on your hard drive since Jet doesn't
need to weed through all of the data in the same file, too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


BruceS said:
Hi, Guys!

I have a fairly large Access 2002 database containing some 350 queries,
forms, reports and tables. (I say this because I'm not sure of any
limitations relating to this in Access.) As part of my VB logic in one of
the forms, I copy a report to a different name, run the report, then delete
it. (This is done so that the file name will be unique when the .pdf output
is created.)

Yesterday, after adding a new field and a couple of queries to the system, I
performed this copy/print/delete routine and received the "MS Access has
encountered an error and will close" message. After doing a little research,
the error is generated each time I try to delete a specific report (one of
the copied ones). The report name shows in the list in the database window.
If I manually select it, the error occurs. If I try to use VB code to delete
it, the error occurs.

In addition, the selection process for reports is very slow. For example,
when I manually select (highlight) one report, then manually select another
one, it takes the system 3-5 seconds to highlight the second entry. There
are only 27 reports (including the "bad" one) and I do not see this slow
selection in the tables, queries or forms window even though there are far
more queries and tables.

I've tried doing a compact an
Click to expand...
1205
d repair, and have even copied the database to
 
I have a fairly large Access 2002 database containing some 350
queries, forms, reports and tables. (I say this because I'm not sure
of any limitations relating to this in Access.)
Click to expand...

AFAIR, the limit is 32K objects, so on that scale you have room to
expand. I would seriously consider chopping this thing up into bits
though, even if only to spare your poor users from having to navigate
through this monolith. One MDB per function (and one to hold the
data...).
As part of my VB
logic in one of the forms, I copy a report to a different name, run
the report, then delete it.
Click to expand...

Sounds bloaty...
Yesterday, after adding a new field and a couple of queries to the
system, I performed this copy/print/delete routine and received the
"MS Access has encountered an error and will close" message.
Click to expand...

Sounds like corruption. Do you have a good backup routine?
After
doing a little research, the error is generated each time I try to
delete a specific report (one of the copied ones). The report name
shows in the list in the database window. If I manually select it,
the error occurs. If I try to use VB code to delete it, the error
occurs.
Click to expand...

Might be time to rethink your filing strategy. In the meantime, try
creating a new mdb, and import all the objects into it. FWIW, I'd import
all the other obects first, backup the file, and then attempt to import
the broken one.

Best wishes


Tim F
 
Gunny,

Thanks for the response! Creating new database, then importing all but bad
report corrected problem.

Incidentally, the delayed "select" for reports is no longer there. Will
look at splitting data away from program. Not critical, though, because it
was designed for single user environment.

Best,
Bruce
 
Tim,

Thanks for the response! Creating new database, then importing all but bad
report corrected problem.

All of the functionality in the program is related - it produces our billing
reports, invoices, etc. Users don't get a chance to navigate through it - I
block access to the database window and require them to use the forms.

Thanks again!
Bruce
 
Back
Top