Compact database thru VBA

[Ron Carr]

From what I have read, I can't compress my back-end DB from the front-end
because the back-end would be open(wouldn't it open as soon as the front-end
linked to it?).

The solution I see is to set up another database to do nothing but run the
compact, then have it start up my front-end.
The thinking is to hide as much as possible from the user.
Does this approach make sense or is there a better way?
(Access 2003)
Thanks for your thoughts!
Ron

 

[Douglas J. Steele]

No, the back-end won't be open until you actually need data from it. Make
sure that all of your bound forms are closed, and you should see that the
locking file (.ldb) is deleted: that's the cue that it's no longer open.

 

[Dirk Goldgar]

From what I have read, I can't compress my back-end DB from the front-end
because the back-end would be open(wouldn't it open as soon as the
front-end
linked to it?).

No, the back-end is only opened when one of the linked tables is accessed,
as by an open form, query, recordset, or table datasheet. You can compact
the back-end from the front-end, so long as you close all open connections
to it. That is, you have to close all objects that connect to the back
end -- bound forms, for the most part.

If you have multiple users connecting to the back-end, though, that's more
problematic -- you can close your own front-end's connections, but but not
those of other users.

 

[Jack Leach]

you can close your own front-end's connections, but but not

those of other users.

I thought there was a way to do this utilizing the timer on a form and
checking against an entered value in a table. I forget the code to actually
force the user off (haven't tried it before), but I thought I've come across
a few solutions to designate a specified time to force all user off for
backend maintanence.

Not necessarily from your particular frontend maybe, but I think it is
possible from a development standpoint.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Ron Carr]

Thanks, that takes care of the problem.
Ron

No, the back-end won't be open until you actually need data from it. Make
sure that all of your bound forms are closed, and you should see that the
locking file (.ldb) is deleted: that's the cue that it's no longer open.

 

[Dirk Goldgar]

I thought there was a way to do this utilizing the timer on a form and
checking against an entered value in a table. I forget the code to
actually
force the user off (haven't tried it before), but I thought I've come
across
a few solutions to designate a specified time to force all user off for
backend maintanence.

Not necessarily from your particular frontend maybe, but I think it is
possible from a development standpoint.

Yes, you can do that quite easily if you design the application that way. I
wasn't considering that level of re-architecting.

 

[Tony Toews [MVP]]

No, the back-end is only opened when one of the linked tables is accessed,
as by an open form, query, recordset, or table datasheet.

It would be more precise to say an open bound form must be closed.
Also an unbound form with a combo or list box bound to a row source.
Also an open recordset or database variable such as those used for
performance persistent connections.

You can compact
the back-end from the front-end, so long as you close all open connections
to it. That is, you have to close all objects that connect to the back
end -- bound forms, for the most part.

Also reports if the user has left any open.

Tony

 

[Dirk Goldgar]

It would be more precise to say an open bound form must be closed.
Also an unbound form with a combo or list box bound to a row source.
Also an open recordset or database variable such as those used for
performance persistent connections.

Yes, of course. I didn't think it was necessary to list all examples.

 

[Tony Toews [MVP]]

Yes, of course. I didn't think it was necessary to list all examples.

Yeah, I know I'm a bit nit picky but the combo box on an unbound form
actually caused someone some grief a few weeks or a month ago.

Tony