How to Compact a Split Database?

[Larry L]

Access is noted for bloating a database when you add and delete records
frequently. I have always had mine set to compact on close, and that works
great. Now after everyone's advice I split my database, so the data is in
a second (back-end) database with all the tables linked. However, now when
I close the database, it compacts the front end, since that's what's open,
and the back-end grows.

I now have to manually open and close the back-end seperately just to
compact it.

Surely there is a better way?

Thanks,
Larry L

[Steve Jorgensen]

On Wed, 07 Jan 2004 09:33:59 GMT, la***@somewhereinhawaii.net (Larry L) wrote:

Access is noted for bloating a database when you add and delete records
frequently. I have always had mine set to compact on close, and that works
great. Now after everyone's advice I split my database, so the data is in
a second (back-end) database with all the tables linked. However, now when
I close the database, it compacts the front end, since that's what's open,
and the back-end grows.

I now have to manually open and close the back-end seperately just to
compact it.

Surely there is a better way?

Thanks,
Larry L

Well, it's not just the bloating. You need to compact the database from time
to time to optimize the table indexes, and clean up nascent corruption before
it presents symptoms.

Yes, with a split front-back end database, you need a way to periodically
compact the database, and you can automate that by running Access with the
/compact parameter. See "Startup command-line options" in the Access help for
details.

[Roberto Spier]

"Steve Jorgensen" <no****@nospam.nospam> escreveu na mensagem
news:do********************************@4ax.com...

On Wed, 07 Jan 2004 09:33:59 GMT, la***@somewhereinhawaii.net (Larry L) wrote:

Access is noted for bloating a database when you add and delete records
frequently. I have always had mine set to compact on close, and that worksgreat. Now after everyone's advice I split my database, so the data is in
a second (back-end) database with all the tables linked. However, now whenI close the database, it compacts the front end, since that's what's open,and the back-end grows.

I now have to manually open and close the back-end seperately just to
compact it.

Surely there is a better way?

Thanks,
Larry L
Well, it's not just the bloating. You need to compact the database from

time to time to optimize the table indexes, and clean up nascent corruption before it presents symptoms.

Yes, with a split front-back end database, you need a way to periodically
compact the database, and you can automate that by running Access with the
/compact parameter. See "Startup command-line options" in the Access help for details.

another nice "CDMA" solution to compact BE from te Front End:

(notice word wrap)
http://groups.google.com/groups?hl=p...254024.9.0.131

Roberto

[David W. Fenton]

fo*****@invalid.com (Roberto Spier) wrote in
<bt************@ID-66191.news.uni-berlin.de>:

another nice "CDMA" solution to compact BE from te Front End:

(notice word wrap)
http://groups.google.com/groups?hl=p...fe=off&threadm
=Xns907570C9Alylefairyahoocom%4024.9.0.131&rnum=1 &prev=/groups%3Fhl
%3Dpt%26lr%3D%26ie%3DUTF-8%26safe%3Doff%26selm%3DXns907570C9Alylefa
iryahoocom%254024.9.0.131

An off-topic observation:

These Google URLs really only need one argument, the selm argument.
So, this URL gets you to the same article:

http://groups.google.com/groups?selm...airyahoocom%40
24.9.0.131

The simplest way to get this URL is to view the article in original
format and remove the &output=gplain argument.

Also, this will get you Google in your own language, whereas your
URL was language-specific.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Larry L]

In article <do********************************@4ax.com>, Steve Jorgensen <no****@nospam.nospam> wrote:

On Wed, 07 Jan 2004 09:33:59 GMT, la***@somewhereinhawaii.net (Larry L) wrote:

Access is noted for bloating a database when you add and delete records
frequently. I have always had mine set to compact on close, and that works
great. Now after everyone's advice I split my database, so the data is in
a second (back-end) database with all the tables linked. However, now when
I close the database, it compacts the front end, since that's what's open,
and the back-end grows.

I now have to manually open and close the back-end seperately just to
compact it.

Surely there is a better way?

Thanks,
Larry L

Well, it's not just the bloating. You need to compact the database from time
to time to optimize the table indexes, and clean up nascent corruption before
it presents symptoms.

Yes, with a split front-back end database, you need a way to periodically
compact the database, and you can automate that by running Access with the
/compact parameter. See "Startup command-line options" in the Access help for
details.

Steve,

Thanks, but I tried adding that switch, using this syntax:

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
"District Database.mdb" "/compact"

and got an error message that it wasn't supported. So I looked it up, and
it says that it opens the specified database, compacts it, and then closes
it. Even if it works, that's not what I need. I just want to compact the
Back-End, in some automated way, without running some other command. Any
other ideas?

Larry L

[Larry L]

In article <bt************@ID-66191.news.uni-berlin.de>, "Roberto Spier" <fo*****@invalid.com> wrote:

another nice "CDMA" solution to compact BE from te Front End:

(notice word wrap)
http://groups.google.com/groups?hl=p...dm=Xns907570C9
Alylefairyahoocom%4024.9.0.131&rnum=1&prev=/groups%3Fhl%3Dpt%26lr%3D%26ie%3DUTF
-8%26safe%3Doff%26selm%3DXns907570C9Alylefairyahooc om%254024.9.0.131

Roberto

Roberto,

Thanks, but while that looks nice, it doesn't work fo me. It goes to the
error that the "Database seems to be opened by another user". I don't know
how to "close" it without unlinking the tables, and that just creates
other problems.

Has anyone ever actually tried this code? Am I just missing something?

Thanks,
Larry L

[Rick Brandt]

"Larry L" <la***@somewhereinhawaii.net> wrote in message
news:Sd*******************@twister.socal.rr.com...

In article <bt************@ID-66191.news.uni-berlin.de>, "Roberto Spier" <fo*****@invalid.com> wrote:
Thanks, but while that looks nice, it doesn't work fo me. It goes to the
error that the "Database seems to be opened by another user". I don't know
how to "close" it without unlinking the tables, and that just creates
other problems.

Has anyone ever actually tried this code? Am I just missing something?

I've never tried the code, but the links by themselves don't cause the back end
file to be "in use" unless your front end is actually doing something with them.
As long as you close all forms and recordsets that use the links then you
should be able to compact the back end from the front end.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Pat]

This may seem convoluted, but I haven't built too many databases. That
said, it works quite well for my needs.

I have a separate maintenance database, originally created to run backups of
the tables in my backend on interval. I created a table in this db to also
manage a compact routine that stores planned start and stop dates/times for
"scheduled maintenance." I have an always-open form in the front end that
periodically compares Now with the planned start time of the next possible
maintenance window. 10 minutes before the planned start time, current users
will be forced to shut down and no users will be allowed to log in.

When the maintenance database is opened, a module runs that checks for a
passed command line. If it's told to compact, it will:
- check for no LDB
- create a backup copy of the backend, and save it in a nice place
- open the backend exclusively and compact to a temp location
- check for the temp, compacted backend
- if it's there, copy it over the original backend

An error along the way triggers an email to me using COM. Once complete,
regardless of outcome, a new record is created recording the next planned
outage, at the interval of my choice - I do this weekly...maybe that's
overkill. Knowing the planned start, the module then schedules a task
within Windows Task Scheduler that will open the maintenance database at
that time, passing the compact argument. You can get the dll that makes
writing a task a breeze at:
http://www.mtogden.com/~tom/files/Sc...ngAgentVB6.zip

I probably couldn't have accomplished it without the help of this group.
Hope this gives you some ideas.
"Larry L" <la***@somewhereinhawaii.net> wrote in message
news:W9*******************@twister.socal.rr.com...

In article <do********************************@4ax.com>, Steve Jorgensen

<no****@nospam.nospam> wrote:

On Wed, 07 Jan 2004 09:33:59 GMT, la***@somewhereinhawaii.net (Larry L) wrote:

Access is noted for bloating a database when you add and delete records
frequently. I have always had mine set to compact on close, and that worksgreat. Now after everyone's advice I split my database, so the data is ina second (back-end) database with all the tables linked. However, now whenI close the database, it compacts the front end, since that's what's open,and the back-end grows.

I now have to manually open and close the back-end seperately just to
compact it.

Surely there is a better way?

Thanks,
Larry L

Well, it's not just the bloating. You need to compact the database from timeto time to optimize the table indexes, and clean up nascent corruption beforeit presents symptoms.

Yes, with a split front-back end database, you need a way to periodically
compact the database, and you can automate that by running Access with the/compact parameter. See "Startup command-line options" in the Access help fordetails.

Steve,

Thanks, but I tried adding that switch, using this syntax:

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
"District Database.mdb" "/compact"

and got an error message that it wasn't supported. So I looked it up, and
it says that it opens the specified database, compacts it, and then closes
it. Even if it works, that's not what I need. I just want to compact the
Back-End, in some automated way, without running some other command. Any
other ideas?

Larry L

[Roberto Spier]

"David W. Fenton" <dX********@bway.net.invalid> escreveu na mensagem
news:94***************************@24.168.128.78.. .

fo*****@invalid.com (Roberto Spier) wrote in
<bt************@ID-66191.news.uni-berlin.de>:

another nice "CDMA" solution to compact BE from te Front End:

(notice word wrap)
http://groups.google.com/groups?hl=p...fe=off&threadm
=Xns907570C9Alylefairyahoocom%4024.9.0.131&rnum=1 &prev=/groups%3Fhl
%3Dpt%26lr%3D%26ie%3DUTF-8%26safe%3Doff%26selm%3DXns907570C9Alylefa
iryahoocom%254024.9.0.131

An off-topic observation:

These Google URLs really only need one argument, the selm argument.
So, this URL gets you to the same article:

http://groups.google.com/groups?selm...airyahoocom%40
24.9.0.131

The simplest way to get this URL is to view the article in original
format and remove the &output=gplain argument.

Also, this will get you Google in your own language, whereas your
URL was language-specific.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

It seemed messy, indeed! As a regular reader, I'd have notice it.

Got it. Thanks for the tip.

Roberto

[Roberto Spier]

"Rick Brandt" <ri*********@hotmail.com> escreveu na mensagem
news:bt************@ID-98015.news.uni-berlin.de...

I've never tried the code, but the links by themselves don't cause the back end file to be "in use" unless your front end is actually doing something with them. As long as you close all forms and recordsets that use the links then you
should be able to compact the back end from the front end.

Right, but there is a typo in the original code that causes
CanBeOpenedExclusively() to always return false:

You need to correct the OpenDatabase argument list, adding an extra comma as
bellow:

Set d = P(0).OpenDatabase(FullPath, , True)

By the way, if this code will be run from an unbound form, this form needs
to be excluded from the code test "Forms.Count Or Reports.Count". Maybe
something like (air code)

Dim frm as Form
Dim openedForms as Integer
for each frm in Forms
openedForms = opendForms - ( frm.Name <> "our Unbound Form")
Next
If opendeForms > 0 Then
MsgBox "Please, close all forms and reports, and retry.", vbExclamation,
"FFDBA"
Else
...
End If

Hope my English makes sense!
Roberto

[Lyle Fairfield]

"Roberto Spier" <fo*****@invalid.com> wrote in
news:bt************@ID-66191.news.uni-berlin.de:

"Rick Brandt" <ri*********@hotmail.com> escreveu na mensagem
news:bt************@ID-98015.news.uni-berlin.de...

I've never tried the code, but the links by themselves don't cause the

back end

file to be "in use" unless your front end is actually doing something
with

them.

As long as you close all forms and recordsets that use the links then
you should be able to compact the back end from the front end.

Right, but there is a typo in the original code that causes
CanBeOpenedExclusively() to always return false:

You need to correct the OpenDatabase argument list, adding an extra
comma as bellow:

Set d = P(0).OpenDatabase(FullPath, , True)

form the help file

**** begin quote ****
OpenDatabase Method

Opens a specified database in a Workspace object and returns a reference
to the Database object that represents it.

Syntax

Set database = workspace.OpenDatabase (dbname, options, read-only,
connect)

The OpenDatabase method syntax has these parts.

Part Description
database An object variable that represents the Database object that you
want to open. workspace Optional. An object variable that represents the
existing Workspace object that will contain the database. If you don't
include a value for workspace, OpenDatabase uses the default workspace.
dbname A String that is the name of an existing Microsoft Jet database
file, or the data source name (DSN) of an ODBC data source. See the Name
property for more information about setting this value. options Optional.
A Variant that sets various options for the database, as specified in
Settings. read-only Optional. A Variant (Boolean subtype) value that is
True if you want to open the database with read-only access, or False
(default) if you want to open the database with read/write access.
connect Optional. A Variant (String subtype) that specifies various
connection information, including passwords.

Settings

For Microsoft Jet workspaces, you can use the following values for the
options argument.

Setting Description
True Opens the database in exclusive mode.
False (Default) Opens the database in shared mode.

**** end quote ****

Which/who is right, you or the help file?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Roberto Spier]

"Lyle Fairfield" <Mi************@Invalid.Com> escreveu na mensagem
news:Xn*******************@130.133.1.4...

"Roberto Spier" <fo*****@invalid.com> wrote in
news:bt************@ID-66191.news.uni-berlin.de:

"Rick Brandt" <ri*********@hotmail.com> escreveu na mensagem
news:bt************@ID-98015.news.uni-berlin.de...

I've never tried the code, but the links by themselves don't cause the

back end

file to be "in use" unless your front end is actually doing something
with

them.

As long as you close all forms and recordsets that use the links then
you should be able to compact the back end from the front end.

Right, but there is a typo in the original code that causes
CanBeOpenedExclusively() to always return false:

You need to correct the OpenDatabase argument list, adding an extra
comma as bellow:

Set d = P(0).OpenDatabase(FullPath, , True)

form the help file

**** begin quote ****
OpenDatabase Method

Opens a specified database in a Workspace object and returns a reference
to the Database object that represents it.

Syntax

Set database = workspace.OpenDatabase (dbname, options, read-only,
connect)

The OpenDatabase method syntax has these parts.

Part Description
database An object variable that represents the Database object that you
want to open. workspace Optional. An object variable that represents the
existing Workspace object that will contain the database. If you don't
include a value for workspace, OpenDatabase uses the default workspace.
dbname A String that is the name of an existing Microsoft Jet database
file, or the data source name (DSN) of an ODBC data source. See the Name
property for more information about setting this value. options Optional.
A Variant that sets various options for the database, as specified in
Settings. read-only Optional. A Variant (Boolean subtype) value that is
True if you want to open the database with read-only access, or False
(default) if you want to open the database with read/write access.
connect Optional. A Variant (String subtype) that specifies various
connection information, including passwords.

Settings

For Microsoft Jet workspaces, you can use the following values for the
options argument.

Setting Description
True Opens the database in exclusive mode.
False (Default) Opens the database in shared mode.

**** end quote ****

Which/who is right, you or the help file?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

Both! But if it's a bet, then I'll put my money on Help File!

Quoted from the original post:

http://groups.google.com/groups?selm...m%4024.9.0.131

Private Function CanBeOpenedExclusively(ByVal FullPath As String) As Boolean
Dim d As Database
Dim p As PrivDBEngine
Set p = New PrivDBEngine
On Error Resume Next
Set d = p(0).OpenDatabase(FullPath, True)
CanBeOpenedExclusively = Not (d Is Nothing)
Set d = Nothing
Set p = Nothing
End Function

This manner, CanBeOpenedExclusively(myPath) always return false, since there
is no read-only argument.

Or maybe (probably!) I'm missing something?!

Regards, Roberto

[Larry L]

In article <bt************@ID-66191.news.uni-berlin.de>, "Roberto Spier" <fo*****@invalid.com> wrote:

Regards, Roberto

OK, Guys, I love all the discussion, even though much of it is over this
novice programmers head, but I'm still trying to compact a back-end
database! :-)

I found some simpler (and safer) code at
http://www.rogersaccesslibrary.com/T...Contents2k.asp
but I still get the same result. It errors out saying the database is in
use.

Here's what I have. I have a single form open, with a button marked Exit.
When I click Exit, it runs a macro. The Macro closes the form, then runs a
function in a module that executes the code from the above source. As I
understand it, when I close the form, nothing else is looking at the data,
but indeed it is still linked. I really don't want to write code to kill
all the links, then compact it, then re-link it all, but the database will
be used by novices, so it needs to work for someone who wouldn't know what
to do if he found the links broken.

Suggestions? (With my thanks.)

Larry L

[TC]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:do********************************@4ax.com...

On Wed, 07 Jan 2004 09:33:59 GMT, la***@somewhereinhawaii.net (Larry L) wrote:

Access is noted for bloating a database when you add and delete records
frequently. I have always had mine set to compact on close, and that worksgreat. Now after everyone's advice I split my database, so the data is in
a second (back-end) database with all the tables linked. However, now whenI close the database, it compacts the front end, since that's what's open,and the back-end grows.

I now have to manually open and close the back-end seperately just to
compact it.

Surely there is a better way?

Thanks,
Larry L
Well, it's not just the bloating. You need to compact the database from

time to time to optimize the table indexes, and clean up nascent corruption before it presents symptoms.

Yes, with a split front-back end database, you need a way to periodically
compact the database, and you can automate that by running Access with the
/compact parameter. See "Startup command-line options" in the Access help for details.

Urk! If no-one else has said this already: get to where the BE is not open
(eg. an unbound main menu form), temprarily drop any performance-enhancing
persistent connection, then do the compact from code within the FE. No need
to run a seperate copy of Access.

TC