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]

Re: How to Compact a Split Database?

On Wed, 07 Jan 2004 09:33:59 GMT, lands@somewhere inhawaii.net (Larry L) wrote:
[color=blue]
>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[/color]

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]

Re: How to Compact a Split Database?


"Steve Jorgensen" <nospam@nospam. nospam> escreveu na mensagem
news:dolnvvkcl9 evgd8otto6bpmtr ippsanaku@4ax.c om...[color=blue]
> On Wed, 07 Jan 2004 09:33:59 GMT, lands@somewhere inhawaii.net (Larry L)[/color]
wrote:[color=blue]
>[color=green]
> >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[/color][/color]
works[color=blue][color=green]
> >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[/color][/color]
when[color=blue][color=green]
> >I close the database, it compacts the front end, since that's what's[/color][/color]
open,[color=blue][color=green]
> >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[/color]
>
> Well, it's not just the bloating. You need to compact the database from[/color]
time[color=blue]
> to time to optimize the table indexes, and clean up nascent corruption[/color]
before[color=blue]
> 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[/color]
for[color=blue]
> details.[/color]

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

(notice word wrap)

http://groups.google.com/groups?hl=pt&lr=&ie=UTF-8&safe=off&threadm=Xns907570C9Alylefairyahoocom%4024.9.0.131&rnum=1&prev=/groups%3Fhl%3Dpt%26lr%3D%26ie%3DUTF-8%26safe%3Doff%26selm%3DXns907570C9Alylefairyahoocom%254024.9.0.131

Roberto

[David W. Fenton]

Re: How to Compact a Split Database?

forspam@invalid .com (Roberto Spier) wrote in
<btgun5$70uv1$1 @ID-66191.news.uni-berlin.de>:
[color=blue]
>another nice "CDMA" solution to compact BE from te Front End:
>
>(notice word wrap)
>http://groups.google.com/groups?hl=p...fe=off&threadm
>=Xns907570C9Al ylefairyahoocom %4024.9.0.131&r num=1&prev=/groups%3Fhl
>%3Dpt%26lr%3D% 26ie%3DUTF-8%26safe%3Doff% 26selm%3DXns907 570C9Alylefa
>iryahoocom%254 024.9.0.131[/color]

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=Xns907570C9Alylefairyahoocom%40

24.9.0.131

The simplest way to get this URL is to view the article in original
format and remove the &output=gpla in 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]

Re: How to Compact a Split Database?

In article <dolnvvkcl9evgd 8otto6bpmtripps anaku@4ax.com>, Steve Jorgensen <nospam@nospam. nospam> wrote:[color=blue]
>On Wed, 07 Jan 2004 09:33:59 GMT, lands@somewhere inhawaii.net (Larry L) wrote:
>[color=green]
>>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[/color]
>
>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.[/color]

Steve,

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

"C:\Program Files\Microsoft Office\Office\M SACCESS.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]

Re: How to Compact a Split Database?

In article <btgun5$70uv1$1 @ID-66191.news.uni-berlin.de>, "Roberto Spier" <forspam@invali d.com> wrote:[color=blue]
>[/color]
[color=blue]
>
>another nice "CDMA" solution to compact BE from te Front End:
>
>(notice word wrap)
>http://groups.google.com/groups?hl=p...dm=Xns907570C9
>Alylefairyahoo com%4024.9.0.13 1&rnum=1&prev =/groups%3Fhl%3Dp t%26lr%3D%26ie% 3DUTF
>-8%26safe%3Doff% 26selm%3DXns907 570C9Alylefairy ahoocom%254024. 9.0.131
>
>Roberto
>[/color]

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]

Re: How to Compact a Split Database?


"Larry L" <lands@somewher einhawaii.net> wrote in message
news:Sd4Lb.4984 7$nG3.20240@twi ster.socal.rr.c om...[color=blue]
> In article <btgun5$70uv1$1 @ID-66191.news.uni-berlin.de>, "Roberto Spier"[/color]
<forspam@invali d.com> wrote:[color=blue]
>
> 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?[/color]

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]

Re: How to Compact a Split Database?

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/SchedulingAgentVB6.zip

I probably couldn't have accomplished it without the help of this group.
Hope this gives you some ideas.


"Larry L" <lands@somewher einhawaii.net> wrote in message
news:W94Lb.4982 1$nG3.13851@twi ster.socal.rr.c om...[color=blue]
> In article <dolnvvkcl9evgd 8otto6bpmtripps anaku@4ax.com>, Steve Jorgensen[/color]
<nospam@nospam. nospam> wrote:[color=blue][color=green]
> >On Wed, 07 Jan 2004 09:33:59 GMT, lands@somewhere inhawaii.net (Larry L)[/color][/color]
wrote:[color=blue][color=green]
> >[color=darkred]
> >>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[/color][/color][/color]
works[color=blue][color=green][color=darkred]
> >>great. Now after everyone's advice I split my database, so the data is[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> >>a second (back-end) database with all the tables linked. However, now[/color][/color][/color]
when[color=blue][color=green][color=darkred]
> >>I close the database, it compacts the front end, since that's what's[/color][/color][/color]
open,[color=blue][color=green][color=darkred]
> >>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[/color]
> >
> >Well, it's not just the bloating. You need to compact the database from[/color][/color]
time[color=blue][color=green]
> >to time to optimize the table indexes, and clean up nascent corruption[/color][/color]
before[color=blue][color=green]
> >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[/color][/color]
the[color=blue][color=green]
> >/compact parameter. See "Startup command-line options" in the Access[/color][/color]
help for[color=blue][color=green]
> >details.[/color]
>
> Steve,
>
> Thanks, but I tried adding that switch, using this syntax:
>
> "C:\Program Files\Microsoft Office\Office\M SACCESS.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[/color]

[Roberto Spier]

Re: How to Compact a Split Database?


"David W. Fenton" <dXXXfenton@bwa y.net.invalid> escreveu na mensagem
news:94699C946d fentonbwaynetin vali@24.168.128 .78...[color=blue]
> forspam@invalid .com (Roberto Spier) wrote in
> <btgun5$70uv1$1 @ID-66191.news.uni-berlin.de>:
>[color=green]
> >another nice "CDMA" solution to compact BE from te Front End:
> >
> >(notice word wrap)
> >http://groups.google.com/groups?hl=p...fe=off&threadm
> >=Xns907570C9Al ylefairyahoocom %4024.9.0.131&r num=1&prev=/groups%3Fhl
> >%3Dpt%26lr%3D% 26ie%3DUTF-8%26safe%3Doff% 26selm%3DXns907 570C9Alylefa
> >iryahoocom%254 024.9.0.131[/color]
>
> 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=gpla in 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[/color]

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

Got it. Thanks for the tip.

Roberto

[Roberto Spier]

Re: How to Compact a Split Database?


"Rick Brandt" <rickbrandt2@ho tmail.com> escreveu na mensagem
news:btimim$7q2 ab$1@ID-98015.news.uni-berlin.de...[color=blue]
>
>
> I've never tried the code, but the links by themselves don't cause the[/color]
back end[color=blue]
> file to be "in use" unless your front end is actually doing something with[/color]
them.[color=blue]
> 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.
>
>[/color]
Right, but there is a typo in the original code that causes
CanBeOpenedExcl usively() to always return false:

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

Set d = P(0).OpenDataba se(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.Coun t 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]

Re: How to Compact a Split Database?

"Roberto Spier" <forspam@invali d.com> wrote in
news:btjlgu$7ul 18$1@ID-66191.news.uni-berlin.de:
[color=blue]
> "Rick Brandt" <rickbrandt2@ho tmail.com> escreveu na mensagem
> news:btimim$7q2 ab$1@ID-98015.news.uni-berlin.de...[color=green]
>>
>>
>> I've never tried the code, but the links by themselves don't cause the[/color]
> back end[color=green]
>> file to be "in use" unless your front end is actually doing something
>> with[/color]
> them.[color=green]
>> 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.
>>
>>[/color]
> Right, but there is a typo in the original code that causes
> CanBeOpenedExcl usively() to always return false:
>
> You need to correct the OpenDatabase argument list, adding an extra
> comma as bellow:
>
> Set d = P(0).OpenDataba se(FullPath, , True)[/color]

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.OpenD atabase (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]

Re: How to Compact a Split Database?


"Lyle Fairfield" <MissingAddress @Invalid.Com> escreveu na mensagem
news:Xns946A5C5 D8DF80FFDBA@130 .133.1.4...[color=blue]
> "Roberto Spier" <forspam@invali d.com> wrote in
> news:btjlgu$7ul 18$1@ID-66191.news.uni-berlin.de:
>[color=green]
> > "Rick Brandt" <rickbrandt2@ho tmail.com> escreveu na mensagem
> > news:btimim$7q2 ab$1@ID-98015.news.uni-berlin.de...[color=darkred]
> >>
> >>
> >> I've never tried the code, but the links by themselves don't cause the[/color]
> > back end[color=darkred]
> >> file to be "in use" unless your front end is actually doing something
> >> with[/color]
> > them.[color=darkred]
> >> 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.
> >>
> >>[/color]
> > Right, but there is a typo in the original code that causes
> > CanBeOpenedExcl usively() to always return false:
> >
> > You need to correct the OpenDatabase argument list, adding an extra
> > comma as bellow:
> >
> > Set d = P(0).OpenDataba se(FullPath, , True)[/color]
>
> 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.OpenD atabase (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)[/color]

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=Xns907570C9Alylefairyahoocom%4024.9.0.131

Private Function CanBeOpenedExcl usively(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).OpenDataba se(FullPath, True)
CanBeOpenedExcl usively = Not (d Is Nothing)
Set d = Nothing
Set p = Nothing
End Function

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

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

Regards, Roberto

[Larry L]

Re: How to Compact a Split Database?

In article <btjvr7$7rfvf$1 @ID-66191.news.uni-berlin.de>, "Roberto Spier" <forspam@invali d.com> wrote:[color=blue]
>
>Regards, Roberto
>[/color]

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/TableOfContents2k.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]

Re: How to Compact a Split Database?


"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
news:dolnvvkcl9 evgd8otto6bpmtr ippsanaku@4ax.c om...[color=blue]
> On Wed, 07 Jan 2004 09:33:59 GMT, lands@somewhere inhawaii.net (Larry L)[/color]
wrote:[color=blue]
>[color=green]
> >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[/color][/color]
works[color=blue][color=green]
> >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[/color][/color]
when[color=blue][color=green]
> >I close the database, it compacts the front end, since that's what's[/color][/color]
open,[color=blue][color=green]
> >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[/color]
>
> Well, it's not just the bloating. You need to compact the database from[/color]
time[color=blue]
> to time to optimize the table indexes, and clean up nascent corruption[/color]
before[color=blue]
> 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[/color]
for[color=blue]
> details.[/color]

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