db repair

[Tom]

I'm not sure that this is the correct group for this, so if not,
please tell me which one is.

Is there any...rule of thumb, how often one should repair the db?
(I'm using Access 2k). Does it depend on size? Activity? Time? Or
just the old familiar, your mileage may vary? (In other words, IT
DEPENDS.) And can this be set up programatically?

I have a .mdb that's about 500mb. It grew to just over 2gb, due to my
pulling data from our AS/400, for a specific job. This was yesterday.
Today, all of a sudden, it started to give me an error. I chased THIS
for a couple hours, until I discovered that code which HAD been
working, and is almost identical to the code that was giving me the
error, was NOW giving me the same error. So I repaired the db. And
my problem went away. (And the size went back down to about 500mb.)

For what I need to do, I need to pull a bunch of data. But it will
remain static once retrieved, and massaged only SLIGHTLY. At one
point, my table had 2.8 million records. But since I'm not finished
debugging my code, I'm not sure that this would be the average number
of trans I need to return, or that I retrieved what I need to without
flushing the table in between. (Off the top of my head, I REALLY think
that this is WAY more than I would normally retrieve.)

Also, I HAVE used Task Manager, on occasion, to kill Access, when it
just sits and takes too long to do its job. I have not, to my
knowledge, had any problem with my .mdb, after doing such a thing.
But perhaps something subtle has happened of which I am unaware. Is
there any, BETTER way to kill it?

Anythoughts, ideas, suggestions would be welcomed. Thanks in
advance...

 

['69 Camaro]

Hi, Tom.

It's not _just_ the database repair that you should be concerned about, but
the compaction also. Fortunately, Access 2K combines the two processes into
one command and Jet will only repair your database when it needs to. You
should compact and repair the database on a regular basis, especially if the
data is updated and deleted frequently. For a better idea of the process
and effects on your Access 2K database when you compact and repair, see the
tip "Compacting the Database" on this Web page:

http://www.Access.QBuilt.com/html/gem_tips.html#Compacting

can this be set up programatically?

Of course. You can even set your database up so that it compacts and
repairs the database each time the database closes.

While you are developing, you should probably work with a much smaller
subset of the data, especially when you are modifying VBA code frequently.
(2-3 MB or less works well for me, but YMMV.) Not only will "test runs" of
the procedures you are writing run much faster, but the smaller the database
file is, the less likelihood of database corruption. Make sure that you
have test runs with the full data set before release to production, though,
to ensure that everything works as it should.

(Off the top of my head, I REALLY think
that this is WAY more than I would normally retrieve.)

Well, you've done some "stress testing" then, and found out that your
database application works even with a much larger amount of data than
originally specified. When your boss eventually asks you, "Will it still
work if we were to double the amount of data it holds?" you already know the
answer.

Also, I HAVE used Task Manager, on occasion, to kill Access, when it
just sits and takes too long to do its job. I have not, to my
knowledge, had any problem with my .mdb, after doing such a thing.

Use less data for your testing and you won't get impatient when the process
takes too long -- because it won't.

But perhaps something subtle has happened of which I am unaware. Is
there any, BETTER way to kill it?

If the processing is being done within a VBA procedure, then you could add
code for user-intervention within a loop and gracefully break out of the
loop to stop the processing when the user presses a key combination, for
example. If the processing is being done with queries (as it should be)
then the only graceful way to halt the processing is with a <CTRL><BREAK>
key combination. Even this takes a very long time to actually stop the
processing, so you may get impatient with this method as well.

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

 

[Tcs]

Thank you!!!. (Sorry I didn't reply sooner. I would have yesterday, except our
Internet conection was down all day. We're about 10 miles south of Atlanta, and
we got hammered with a lot of rain and wind. Trees down all over the metro
area, taking out electric and telephone lines, and clobbering many buildings.)

I know I didn't mention it, but I did know that it also compacts.

I'm trying to use the code from the Compacting gem tips, but I'm missing
something. I created a macro to RunCode, and could only select the Function
"CompactDB", not Sub "getFileInfo". (Sorry, I'm still fuzzy on the difference
between a Sub and Function.) And I had to comment out "Call getFileInfo", so I
could get it to compile.

Smaller subset. I've looked, but can't find anything that might help. I used
to use a report writer on Data General that had a feature (command) where I
could set the limit of transactions that it would find before running the
report. I could test my reports without having to read thru the entire
database. I usually set the limit to 100, so I'd get at least one full page.
Does SQL have anything similar? I couldn't find anything for 'count' or
'limit'.

Each time it closes. How would I implement this?

Could you, perhaps give me a little "nudge" in th right direction?

Thanks so much.

Tom

 

['69 Camaro]

Hi, Tom.

I'm trying to use the code from the Compacting gem tips, but I'm missing
something. I created a macro to RunCode, and could only select the Function
"CompactDB", not Sub "getFileInfo". (Sorry, I'm still fuzzy on the difference
between a Sub and Function.) And I had to comment out "Call getFileInfo", so I
could get it to compile.

The CompactDB.VBS code is a VB script intended to be pasted into a file that
will run on a Windows server that doesn't have Microsoft Access installed.
If you are pasting this script into the VB Editor and compiling, then you
_have_ Access installed! You don't need this script to compact an Access
database on your computer, since you can use menu commands or VBA within
Access to do so.

could set the limit of transactions that it would find before running the
report. I could test my reports without having to read thru the entire
database. I usually set the limit to 100, so I'd get at least one full page.
Does SQL have anything similar? I couldn't find anything for 'count' or
'limit'.

If you base your report on a query, not the table itself, then you can limit
the number of records displayed. Open the query in Design View.
Right-click in the upper pane and select "Properties" from the pop-up menu.
Select "100" from the "Top Values" combo box. Save the query.

Now when you run your report, only 100 records will be displayed.

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

 

[Tcs]

Hi, Gunny.

Access installed. Server. Hmm... Guess I screwed that one up. Would you know
of any VBA code that might help get me started in automating this? You also
mentioned setting it up so that it could run when I close Access. How would I
do that?

Top Values to limit the number of rows returned. EXCELLENT.

Thanks a lot,

Tom

 

[Tcs]

I found the "Compact on Close" checkbox. Is this the "best" way? Also, the
help file didn't mention it, but I assume it repairs (if necessary) as well,
correct?

Again, MANY thanks for your help.

Tom

 

['69 Camaro]

Access installed. Server. Hmm... Guess I screwed that one up.

Don't feel bad. Access has a fairly steep learning curve. Truth be told,
VB Script looks amazingly like VBA, since they're both based upon the same
language, Visual Basic. As you've noticed, there are some subtle
differences, so they aren't always interchangeable.

Would you know
of any VBA code that might help get me started in automating this?

See http://www.mvps.org/access/general/gen0041.htm for the VBA code to
compact the current database. However, this won't compact the database
application objects. For Access 2K, use syntax such as the following
command-line switch in a Windows shortcut after the current database has
been closed:

"C:\Program Files\Microsoft Office\Office\MSAccess.exe" "C:\MyDB.mdb"
/repair

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

 

['69 Camaro]

Hi, Tom.

I found the "Compact on Close" checkbox. Is this the "best" way?

It's certainly the easiest way. However, you won't be able to compact and
repair the database when you close it if other users still have it open.
I'll post an answer to your other question requesting the code for the
compaction.

I assume it repairs (if necessary) as well

Yes.

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