Compress and Repair

[denyoung]

I am about to compress and repair an Access 2007 BE database for the first
time after its first three weeks of use that saw it grow from 20 megs to 35
megs. Is it required or advisable to compress and repair the 30 FE databases
that link to the BE?

Thanks.

 

[Tom Wickerath]

Hi Dennis,

Make absolutely certain that you have a good backup copy of your BE
database, before you initiate a compact and repair. There have been several
reports of people's Access 2007 databases disappearing after they did a
compact.

As far as compacting the FE goes, this would likely help, but it would be
awfully painful to have to walk around to 30 different workstations to
compact each copy. I recommend instead that you implement the free AutoFE
Updater utility, from Access MVP Tony Toews. Once you have it implemented
correctly, you would only need to compact your copy of the FE application,
and then place a copy of it in the distribution folder on a file server. The
next time any user opened your database (they must use a special shortcut
that you create with the StartMDB.exe utility to open it--not open the FE
directly), they will automatically get the latest copy (which you compacted
prior to posting). More info. here:

Auto FE Updater
http://www.granite.ab.ca/access/autofe.htm

Getting started with the Auto FE Updater
http://www.granite.ab.ca/access/autofe/gettingstarted.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[denyoung]

Thanks, that certainly makes sense though I do have a number of users who are
merrily cranking out queries and reports. For those, perhaps I'll copy their
current FE first and copy back the individual objects after the updated FE is
distributed.

 

[Tom Wickerath]

Hi Dennis,

Yes, that could be a problem. I only distribute in the compiled .mde format,
so my users are not making their own reports. They usually haven't a clue as
to the folder on their PC that contains the actual FE application.

Have you looked into the possibility of using the Query By Form (QBF)
technique? I'm not talking about the rather limp QBF that is built-into
Access. Instead, I'm thinking of an unbound search form, with a bound
subform, where you build the WHERE clause of a SQL statement on-the-fly. Here
are some examples:

Easiest example:
http://www.accessmvp.com/TWickerath/downloads/elements.zip

A bit more involved:
http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

Tutorial with .mdb file and Word doc.:
http://www.seattleaccess.org/downloads.htm

Query By Form - Multi Select
Tom Wickerath, February 12, 2008


An even easier example of QBF that doesn't involve building the WHERE clause
on-the-fly can be found here:
http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip

However, this method has some limitations, including no support for
multi-select list boxes.

As far as specifying report criteria on-the-fly, try Access MVP Armen
Stein's sample here:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp
See: Report Selection Techniques download


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tony Toews [MVP]]

Thanks, that certainly makes sense though I do have a number of users who are
merrily cranking out queries and reports. For those, perhaps I'll copy their
current FE first and copy back the individual objects after the updated FE is
distributed.

For those power users give them an MDB linked to all the tables in the
BE. They can create queries and reports in that MDB rather than in
your distributed FE MDB/MDE.

And every once in a while ask them if they would like some of those
reports which are run on a regular basis in the distributed FE
MDB/MDE?

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[denyoung]

Is that essentially 2 FE's linked to the single BE, one of which thay can
play with and one not? Imagine they open two instances of Access on their
client, one for each FE. Can Access handle that situation?

Thanks.

 

[denyoung]

You know, let me go back to me original question and rephrase it. If I
compress and repair a BE and I haven't made any structural changes to the
tables in it, is it necessary to compress and repair the FE's, or should they
all still link to the BE correctly?

Thanks

 

[Tony Toews [MVP]]

Is that essentially 2 FE's linked to the single BE, one of which thay can
play with and one not?
Correct.

Imagine they open two instances of Access on their
client, one for each FE. Can Access handle that situation?

Very nicely.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

You know, let me go back to me original question and rephrase it. If I
compress and repair a BE and I haven't made any structural changes to the
tables in it, is it necessary to compress and repair the FE's, or should they
all still link to the BE correctly?

They will link quite nicely.

Now if you insert fields into the middle of tables you might come up
with the -1517 error after you compact the BE database. Relinking the
FE tables solves that problem.

http://www.granite.ab.ca/access/reservederror1517.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[denyoung]

Thanks once again. Both answers provide me with useful info.

 

[David W. Fenton]

Now if you insert fields into the middle of tables you might come
up with the -1517 error after you compact the BE database.
Relinking the FE tables solves that problem.

http://www.granite.ab.ca/access/reservederror1517.htm

Er, don't you say that "compacting the front end" also solves the
problem? I'd think it would, since metadata stored in the links is
refreshed on compact.

 

[Tony Toews [MVP]]

Er, don't you say that "compacting the front end" also solves the
problem? I'd think it would, since metadata stored in the links is
refreshed on compact.

I think it does too. However I just relink the tables.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/