Re: db repair
From: '69 Camaro (ForwardZERO_SPAM.To.69Camaro_at_Spameater.orgZERO_SPAM)
Date: 09/10/04
- Next message: '69 Camaro: "Re: db repair"
- Previous message: Brian: "Re: Access 97 under Windows XP"
- In reply to: Tcs: "Re: db repair"
- Next in thread: Tcs: "Re: db repair"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 9 Sep 2004 18:28:09 -0700
> 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.)
"Tcs" <tsmith@eastpointcity.org> wrote in message
news:ca21k0l5gbqg50fqqk4vrtgeav0i0ek040@4ax.com...
> 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
>
> On Thu, 9 Sep 2004 09:35:50 -0700, "'69 Camaro"
> <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SPAM> wrote:
>
> >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" <tsmith@eastpointcity.org> wrote in message
> >news:9g8uj0dihf39810l78k7b4qqqcjthhl8n8@4ax.com...
> >> 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
> >>
> >> On Fri, 3 Sep 2004 17:10:09 -0700, "'69 Camaro"
> >> <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SPAM> wrote:
> >>
> >> >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.
> >> >
> >> >> > I have a .mdb that's about 500mb. It grew to just over 2gb
> >> >
> >> >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.)
> >> >
> >> >
> >> >"Tom" <tcsmith@mindspring.com> wrote in message
> >> >news:0mqhj01nqen66t8ukqoc3scgn9h6md3b40@4ax.com...
> >> >> 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...
> >> >>
> >> >>
> >> >>
> >> >
> >>
> >
>
- Next message: '69 Camaro: "Re: db repair"
- Previous message: Brian: "Re: Access 97 under Windows XP"
- In reply to: Tcs: "Re: db repair"
- Next in thread: Tcs: "Re: db repair"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
