Can 2003 compact a 97 BackEnd?

[mjt7email-ggroups]

Hello Everyone

We are preparing to upgrade from 97 to 2003. Initially, I'm opting to
keep the (secure) BE in 97 and distributing the (secure) FE mde in 97
and 2003 versions, and first tests seem OK.

When all the PC's have been upgraded to 2003, then a decision will be
made whether to also move the BE to 2003.

But I have a particular problem…soon after releasing the FE mde
versions so the process of upgrading the PC's can begin, I'm going
away for four weeks.

Does there have to be at least one PC with Access 97 still installed
so the BE compact routine can be actioned, or can the compact be done
via 2003?

I tried compacting via 2003 and the BE certainly compacted, so if it
doesn't mess with the 97 tables I'm happy with the message: "The
current user account doesn't have permission to convert or enable this
database". Users would get used to hitting OK and the db closes.

So, is it safe for 2003 to compact a 97 db?

I tried jetComp.exe but got absolutely nowhere; a generic error
message "Error compacting database". I checked the BE for a table
MSysCompactError but there wasn't one.

Background info:
All permissions for all tables in the BE are revoked, all access is
via RWOP queries.
A "caretaker" user has permission to open the BE exclusively; the
caretaker password is no secret and lots of folk have been given the
job of compacting over the years. No-one has permission for the
startup form in BE so it closes immediately if anyone tries to open it
normally.

Thank you for any help.
Marguerite

 

[Jerry Whittle]

No. It seems to; however, it doesn't reduce the file size. I took an old A97
3mb database file and deleted the records in the tables. I C&R'ed it and the
file size stayed the same. Trying to do the same in Access 2007 caused some
error messages.

However all is not lost. The JetComp utility does a better job of compacting
repairing anyway especially as it demands that you do a backup first. It is a
stand-alone program.

http://support.microsoft.com/kb/295334

 

[mjt7email-ggroups]

Hello Jerry

Thank you for your help. You did a proper compact test while I just
believed the status bar text "compiling..." or something like it<s>.

I had already tried the The JetComp utility (though I referred to it
as jetComp.exe in my post) and it it didn't work at all. The JetComp
readme doc clearly states "You can use Jetcomp.exe on secured
databases..." It took about a millisecond to return the generic
message "Error compacting database".

I tried it on a BE db dated back in March (the earliest I've got
without going to the backups on CD) with the same result. It shouldn't
make any difference because jetComp doesn't open the db, but I also
tried restoring the startup options, and it's true - it didn't make
any difference!

Can you suggest any ideas why JetComp would consistently fail? Might
it have anything to do with the fact that I have both 97 and 2003
installed on my PC?

Regards
Marguerite

 

[mjt7email-ggroups]

Hello again Jerry

Before posting my original question, I had done a test run compacting
via 2003 (not *compiling* as I said in my first reply<s>) and I noted
the file size reduced marginally. I have now followed your example and
done a proper test by deleting all the relationships and records in
the BE and compacted it with 2003. The file size reduced from 11+ Mb
to less than 1 Mb.

I also compacted the same bloated db via 97 and the 2003 compacted
size is slightly smaller, so hooray for 2003!

Can I believe this reduction using 2003?

Regards
Marguerite

 

[david]

Has the database ever been opened in a later version?
JetComp might not like the tables created by A2K when
you 'enable' the database? You could try using the DAO
CompactDatabase method to see if that works.

BTW, compact does not always make the smallest database,
because it sorts and adds free space at the end of tables,
but the reason the 2003 is compacting smaller is probably
because it is doing a better job of compacting the extra
'enable' data that A97 is ignoring.

(david)


Hello Jerry

Thank you for your help. You did a proper compact test while I just
believed the status bar text "compiling..." or something like it<s>.

I had already tried the The JetComp utility (though I referred to it
as jetComp.exe in my post) and it it didn't work at all. The JetComp
readme doc clearly states "You can use Jetcomp.exe on secured
databases..." It took about a millisecond to return the generic
message "Error compacting database".

I tried it on a BE db dated back in March (the earliest I've got
without going to the backups on CD) with the same result. It shouldn't
make any difference because jetComp doesn't open the db, but I also
tried restoring the startup options, and it's true - it didn't make
any difference!

Can you suggest any ideas why JetComp would consistently fail? Might
it have anything to do with the fact that I have both 97 and 2003
installed on my PC?

Regards
Marguerite

 

[mjt7email-ggroups]

Hello David

I tested JetComp with a virgin 97 BE with the same non-result.

I have a couple of questions...

Q1.
I have no idea 'enable' data is, would you explain that please? I
don't have A2K and until a fortnight ago, I'd never seen 2003
either<s>.

Users have a shortcut they've used for years to compact the db. The
shortcut has a 'caretaker' user and password with a /compact switch.
Changing the shortcut to point to the Access 2003 exe instead of 97
*seems* to work (see my later reply to Jerry).

Q2.
I would prefer to continue using the shortcut, but would you
anticipate any problem using 2003 to compact the 97 BE?

Q3.
If I can get JetComp to work on the 97 BE, I assume the option between
3x and 4x for the detination file is 3x - are you able to confirm that
for me please?

Q4
If 3x does apply, and the user forgets and doesn't change it from the
default 4x, what is the result?

Four questions is more that a couple of questions - sorry<s>

Regards
Marguerite

 

[Jerry Whittle]

Your results were better than mine thankfully.

As far as JetComp not compiling properly, did your version of JetComp have
check boxes for Jet 3.X and Jet 4.0 databases? A newer version of JetComp
does.

It's also possible that your database is corrupt and can't be compacted.
However since you did so, that almost certainly eliminates that possibility.

 

[mjt7email-ggroups]

Hello Jerry

I have two versions of JetComp.exe on my system:
1. the one that comes with Jet35sp3.exe (for Access 97), downloaded
years ago and I hadn't read the readme.doc, or had forgotten about it!
It doesn't have an option for the destination db.
2. jetcu40.exe, recently downloaded, has the 3x and 4x (default)
option.

I used the second one for my first failed tests. It was sensible to
use the first file now that I've found it but that failed too. At
least the error message was more informative and I have established
that, for me anyway, both versions of the JetComp utility fail because
the built-in Users group does not have permission to open the db.

According to (my understanding of) the SecFAQ bible, permission for
the Users group to open the db should be removed: Item 40. "I ran the
Security Wizard but users from another workgroup can still open the
database".

If the Users group has permission to open the db, both versions of the
JetComp utility work as expected.

So my choice is: tell the company to keep at least one PC with Access
97 for BE compact until I return when the BE will be converted to
2003, or use 2003 to compact.

I should have given this thread the subject line, "Is it safe for 2003
to compact a 97 BackEnd".

Do you know if it is safe to do so?

Thank you for your help.
Marguerite

 

[david]

Access puts an Access/VBA project into a JET/MDB
database.Then you add more data tables to the JET/MDB
database.

If you use the DAO create database method to create a
database, Access is not involved, and you get a JET/MDB
database that does not have an Access/VBA project inside it.

If you create a database with Access 2007 you get an Access
2007 project inside a Jet 4.0 database. If you create a database
with Access 97 you get an Access 97 project inside a Jet 3.0
database.

If you open an Access 97/Jet 3.0 database with Access 2003,
you have to choose between 'converting' and 'enabling' the
database.

"Converting" creates a new Access 2003 project inside a new
Jet 4.0 database.

"Enabling" adds properties and tables to the Jet 3.0 database
so that it is compatible with an Access 2003 project.

Linking to an Access 97 database does not require either
converting or enabling, because you specifically link to specific
data tables. You can ignore the Access project, and no Access
project is required.

Compacting using the MSACCESS.EXE command line
requires that MSACCESS.exe opens the database. For
this work, the database first needs to be 'converted' or
'enabled'.

Compacting use DAO or the Jetcomp utility does not
require that Access opens the database. However,
perhaps the Jetcomp utility has problems with a database
that has been 'enabled'?

Corruption of the Access project will prevent Access
from opening the database, so some forms of corruption
are best handled by DAO or by the Jetcomp utility.

However, Access 2000+ projects can not be completely
compacted by DAO, so for Access 2000+ you have
do an Access compact after adding and removing forms.

Alternatively, if you have to frequently compact your
database, you have a design issue that could be addressed.

(david)




Hello David

I tested JetComp with a virgin 97 BE with the same non-result.

I have a couple of questions...

Q1.
I have no idea 'enable' data is, would you explain that please? I
don't have A2K and until a fortnight ago, I'd never seen 2003
either<s>.

Users have a shortcut they've used for years to compact the db. The
shortcut has a 'caretaker' user and password with a /compact switch.
Changing the shortcut to point to the Access 2003 exe instead of 97
*seems* to work (see my later reply to Jerry).

Q2.
I would prefer to continue using the shortcut, but would you
anticipate any problem using 2003 to compact the 97 BE?

Q3.
If I can get JetComp to work on the 97 BE, I assume the option between
3x and 4x for the detination file is 3x - are you able to confirm that
for me please?

Q4
If 3x does apply, and the user forgets and doesn't change it from the
default 4x, what is the result?

Four questions is more that a couple of questions - sorry<s>

Regards
Marguerite

 

[david]

If you look carefully at the Jet 4.0 version of Jetcomp, you
will see that it has provision for compact using a specific
user name and password and workgroup (mde) file.

If you have a secured database, you can still use the
utility to compact.

(david)

Hello Jerry

I have two versions of JetComp.exe on my system:
1. the one that comes with Jet35sp3.exe (for Access 97), downloaded
years ago and I hadn't read the readme.doc, or had forgotten about it!
It doesn't have an option for the destination db.
2. jetcu40.exe, recently downloaded, has the 3x and 4x (default)
option.

I used the second one for my first failed tests. It was sensible to
use the first file now that I've found it but that failed too. At
least the error message was more informative and I have established
that, for me anyway, both versions of the JetComp utility fail because
the built-in Users group does not have permission to open the db.

According to (my understanding of) the SecFAQ bible, permission for
the Users group to open the db should be removed: Item 40. "I ran the
Security Wizard but users from another workgroup can still open the
database".

If the Users group has permission to open the db, both versions of the
JetComp utility work as expected.

So my choice is: tell the company to keep at least one PC with Access
97 for BE compact until I return when the BE will be converted to
2003, or use 2003 to compact.

I should have given this thread the subject line, "Is it safe for 2003
to compact a 97 BackEnd".

Do you know if it is safe to do so?

Thank you for your help.
Marguerite

 

[mjt7email-ggroups]

David, thank you for your detailed reply - all of it very informative
and I've learnt a lot.

I'm going to pick out just one comment, but not because the others are
of less value -

I'd read this about the JetComp utility, but didn't know about a DAO
compact (keeping in mind your last comment about 2000+).

Again, thank you for your time taking me by the hand and leading me
onwards<s>.

Regards
Marguerite

 

[mjt7email-ggroups]

David

I feel foolish...but if the name fits<bg>

And it's true of the Jet 3 version too.

Many thanks,
Marguerite