TSISOON90 question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AC - 24 Jan 2006 00:36 GMT

I am running a VBA subroutine in a database which executes a series of
queries in a loop. After a time, the db size becomes a bit unwieldy. I am
trying to use Tsisoon90 to programmically compact the database when it hits a
size limit and then relaunch the data base with a function call to continue
the loop after compacting (this way the user doesn't have to babysit the
application as it's doing its thing). The utility works the first time but
after completing the second pass and call to tsisoon90, it closes the db,
does not compact it, and does not reopen it. I was wondering if anyone has
enough experience with tsisoon to have any suggestions or alternative methods
of accomplishing what I am trying to do?

Thanks!

Reply to this Message

Arvin Meyer [MVP] - 24 Jan 2006 03:58 GMT

Have you seen this code? If your database is Access 2000 or later you can
compact the currebt database:

http://www.mvps.org/access/general/gen0041.htm
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

> I am running a VBA subroutine in a database which executes a series of
> queries in a loop. After a time, the db size becomes a bit unwieldy. I am
[quoted text clipped - 8 lines]
>
> Thanks!

Reply to this Message

AC - 24 Jan 2006 13:52 GMT

Thanks. My understanding though, is that in Access 2000, this method would
work if connected to a button on a form but would not work if called from
another subroutine. Something about not allowing the user to compact from
VBA. The tsisoon90 is the only way I have found that will work without user
intervention? Is this right?

FYI - I ran the looping process last night and it took 7 hours - too long to
have someone babysit the application to hit a button to compact then start it
up again.

> Have you seen this code? If your database is Access 2000 or later you can
> compact the currebt database:
[quoted text clipped - 25 lines]
> >
> > Thanks!

Reply to this Message

Douglas J Steele - 24 Jan 2006 15:59 GMT

I don't believe that's correct. That's VBA code that invokes the command
that the menu option points to. Why not try it?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks. My understanding though, is that in Access 2000, this method would
> work if connected to a button on a form but would not work if called from
[quoted text clipped - 35 lines]
> > >
> > > Thanks!

Reply to this Message

AC - 24 Jan 2006 17:12 GMT

Doug:

Thanks for the suggestion. I did try the code snippet from Arvin's post.
Unfortunately, I got the following message:

"You can't compact the open database" while running a macro or visusal basic
code

Any thoughts?

AC

> I don't believe that's correct. That's VBA code that invokes the command
> that the menu option points to. Why not try it?
[quoted text clipped - 48 lines]
> > > >
> > > > Thanks!

Reply to this Message

Douglas J. Steele - 24 Jan 2006 23:51 GMT

Is your application split into a front-end (containing the queries, forms,
reports, macros and modules) and back-end (containing the tables and
relations)? Front-ends can compact back-ends, as long as no connections are
currently open between them.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Doug:
>
[quoted text clipped - 71 lines]
>> > > >
>> > > > Thanks!

Reply to this Message

AC - 27 Jan 2006 14:02 GMT

Doug -

Thanks for the response (and sorry for the delay getting back to you - I
was losing hope). No, the application is all contained within one database.
If I split it into two as you ask in your email, does the "bloat" hit the
front end where the queries/forms/macros are or the back end with the tables?
I always assumed that the work would be done in the front end and therefore
that is where the size of the database would increase. I'd hate to go
through the process of splitting it and then not have luck with the
compacting!

Thanks,

AC

> Is your application split into a front-end (containing the queries, forms,
> reports, macros and modules) and back-end (containing the tables and
[quoted text clipped - 76 lines]
> >> > > >
> >> > > > Thanks!

Reply to this Message

Douglas J. Steele - 27 Jan 2006 21:34 GMT

Unfortunately, the answer is "it depends", but I'd expect most of the bloat
to be in the back-end. Even if it isn't, though, since the front-end will
presumably be substantially smaller than the non-split database, you'll have
more room to grow before the size becomes an issue.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Doug -
>
[quoted text clipped - 107 lines]
>> >> > > >
>> >> > > > Thanks!

Reply to this Message