Corruption Problem with 2.0

[Louise54]

I have a multi-user (approx 8 people) on a back-end 2.0 database which is
currently about 86,000KB in size. They each have a front end on their local
hard drive, using whatever Access version is on their pc (from 2000 up to
2007). The backend has been working just fine for several years until a few
weeks ago.

In the last 3 weeks we've been getting "unrecognized database format" errors
(7 times total). I've been able, thus far, to repair/compact, but it's taking
longer each time. I'm running through various checks to see if I can find the
cause.

At the same time, I think it's probably time to bite the bullet and convert
the back end to a higher version. I know this has been discussed many times,
but is there a favorite set of procedures to do this conversion? And a
version more stable than others that I should move to? We don't have that
many tables, but I think the biggest headache for me will be dealing with
several auto-number fields. Also, is it ok to have two backend databases?
Because of size restrictions, I thought it might be better to split the one
backend into two this time?

Any and all help, comments, suggestions would be greatly appreciated. Thank
you!
Louise

 

[Tom van Stiphout]

On Wed, 18 Jun 2008 05:53:00 -0700, Louise54

You should upgrade the BE to the least common denominator, which you
state is A2000.
There is no point in having 2 databases; 86MB is peanuts.
For corruption tips check this out:
http://www.granite.ab.ca/access/corruptmdbs.htm

-Tom.

 

[Jerry Whittle]

At times like this, nothing beats a good backup. In fact make a complete
backup of your database now and put it away for safe keeping. I suggest daily
backups until you can figure out the problem.

I'm wondering what has changed in the last few weeks on the network. Have
they installed any new equipment? Possibly some of the old equipment is
failing such as a switch, router, cable, or network interface card (NIC). See
if your network people can run a sniffer for bad packets. That can cause
corruption.

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

[Tony Toews [MVP]]

In the last 3 weeks we've been getting "unrecognized database format" errors
(7 times total). I've been able, thus far, to repair/compact, but it's taking
longer each time. I'm running through various checks to see if I can find the
cause.

I'm with Tom and Jerry. 86 Mb is peanuts. Convert to A2000 your
lowest common denominator.

And see if discussions with your IT department can figure out what's
new in the last three weeks. New PC? Or what.

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/

 

[Louise54]

THanks everyone. We added two new things in the last month. The first being a
new phone system, but not sure how that would affect anything in regards to
this database? Second is a managed ops service, but I questioned whether
anything may be a conflict there and they don't seem to think so.
Otherwise, no new pcs or servers.

When I make the conversion to 2000, can I do one table at a time and just
make the re-link to each table as I add them in? Or should I do the entire
database at once?

Also, can I jump directly from 2.0 to 2000? Best procedures to handle
auto-number fields?
Thanks so much!
Louise

 

[Tony Toews [MVP]]

When I make the conversion to 2000, can I do one table at a time and just
make the re-link to each table as I add them in? Or should I do the entire
database at once?

No, convert the entire MDB. It will only take a few or thirty
seconds. Besides converting one table at a time would lose
referential integrity.

Also, can I jump directly from 2.0 to 2000?

Yes. If directly converting doesn't work then create a new MDB and
import the tables. Although you will lose the layout of the
relationships window.

Best procedures to handle auto-number fields?

Nothing to handle. Those will convert just fine. Or am I missing
something here?

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/

 

[Guest]

You haven't got anyone connecting using DAO 2.5 or
Access 2 do you? The database is OK with Jet 4.0
(Access 2000/200x) only as long as you don't try to
use Access 97 or Access 2 (or VB3 or VB4-16) on
it at the same time.

There are only four* database versions: 2, 3, 4
and ACDB. Access 2000-2007 use version 4.0.

There is a new Access Project version inside the database
for each Access version. Since this is a BE database,
it doesn't matter which project version you also have inside
the database. If you use Access to convert the database
(instead of DAO), you will get a new Access project in
the current version, but the actual data conversion is done
by Jet 4, and will be exactly the same no matter which
version the conversion is called from.

Which project version should you have in your BE database?
If you have a 5 year long change control process, don't do it
that way at all. Do a DAO conversion, so that you don't get
a project at all. Then if you open the BE in Access, you will
get a conversion message, which will remind you that you
shouldn't be modifying the BE.

If you have more dynamic upgrade process, do the conversion
in your current development environment. Earlier version users
won't be able to open the BE in Access (only link to it), which
will remind them that they shouldn't be modifying the BE.

(david)

*ignoring 1 and 1.1, Jet 2.5 and Jet 3.5 use version 2.0 and 3.0

 

[Louise54]

Regarding the auto-number fields: they will remain the same once converted,
and then pick up reassigning where it left off? Maybe I'm thinking of what
happens when you import old data into new tables (not just convert them).

 

[Larry Daugherty]

To ease your concerns, you should always backup your data to a safe
location with a differently named MDB. That way you'll have an
ironclad fallback.

Your autonumbers won't miss a beat. They're no more at risk than any
other data.

HTH

 

[Louise54]

Thanks David, but I'm sorry I'm confused about your answer. All users will
have their front end in various versions of Access (2000, 2003, or 2007). I'm
assuming I can convert the back end from 2.0 to 2000, and then everyone will
be happy? Not sure what you mean about DAO as opposed to Access for the
conversion. What is DAO, and also, what is a 5 year long change control
process? I'm not a programmer, but would be very interested in learning what
those are so that I can understand if or how I should use them? I was
planning to do a practice run of the conversion by using a copy of the back
end database (renaming it first) to make sure everything runs smoothly. Sound
ok?
Thanks so much for your help.

 

[Louise54]

Thanks so much Larry. I'm feeling much better about this already!

 

[Tony Toews [MVP]]

Regarding the auto-number fields: they will remain the same once converted,
and then pick up reassigning where it left off? Maybe I'm thinking of what
happens when you import old data into new tables (not just convert them).

What Larry said.

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/

 

[Guest]

You know what Access is, and it has version 2, 2000 and so on.

Each version of Access can connect to lots of different kinds of
databases. Access databases, SQL Server databases, FoxPro
databases and so on.


There are three kinds of "Access databases", 2, 3, and 4.

Access 2000 - 2003 normally use a type 4 database.
Access 97 uses a type 3 database
Access 2 uses a type 2 database.

Access uses the 'Jet engine' to talk to these databases

Jet 2.5 for database type 2 originally from Access 2
Jet 3.5 for database type 3 originally from Access 95
Jet 4.0 for database type 4 originally from Access 2000

You don't have to use Access to talk to these databases.
Any kind of program can use the Jet engine to talk to these
databases.

You can use your current version of Access to convert
your database. It probably won't fix your corruption problem,
unless your problem is that someone is using an old program
to talk to your database. If someone is using an old program
to talk to your database, changing the database from version
2.0 to version 4.0 will stop that person, and stop that program.
They won't be able to use the database anymore.

Corruption problems are also caused by bad network cards,
or by networks. Changing the database from 2 to 4 won't
make that go away. Problems don't come from nowhere, and
they don't just go away.

For the corruption problem, it won't matter if you use 2000
or 2003 to convert the database. 2000, 2002 and 2003 all
use the same version of Jet to talk to the database. The version
of the database (version 4, Jet 4) will be exactly the same.
Jet 2.5 can't talk to a version 4 database, so if that is your
problem, conversion will stop the problem.

If you use Access 2003 to convert the database, no one will
be able to open the database in 2000 any more, but that won't
affect the users using 2000 or 2002. They don't open the database
in 2000 or 2002. They just use Jet 4 to talk to the database. It
doesn't matter if a BE database is converted in 2000 or 2003.

After you convert the database, it may be much slower. You may
have to do more work to make it faster again, and it may never
be as fast as it is now.

(david)

 

[Louise54]

Daivd, thank you so much for that very detailed response. It's just what I
needed to help understand the differences. The part that has me worried is
where you mention that if I convert the 2.0 database to, say, 2003, it will
never run as fast as it currently does. That's mainly the reason why I've
never converted it over the years - it has been running just fine, so why
take the risk? My users will complain for sure if I give them something
slower.

How do other people handle the slowdown - and is it wrong to keep this 2.0
going?
Once again, I very much appreciate everyone's help.

 

[Guest]

There are a couple of simple optimisations that bring back most of
the speed -- keep linked databases open, subdatasheet name.
See Tony's speed FAQ:
http://www.granite.ab.ca/access/performancefaq.htm

Try the conversion and see if it is acceptable -- it is for everyone
else, so it will probably be ok for you too.

Corruption is only a problem with version 2.0 database if someone
uses Access 2000-2003 at the same time on the same database as
someone is using Access 2.0 or a VB3 program.

(david)

 

[Louise54]

Thank you so much!

There are a couple of simple optimisations that bring back most of
the speed -- keep linked databases open, subdatasheet name.
See Tony's speed FAQ:
http://www.granite.ab.ca/access/performancefaq.htm

Try the conversion and see if it is acceptable -- it is for everyone
else, so it will probably be ok for you too.

Corruption is only a problem with version 2.0 database if someone
uses Access 2000-2003 at the same time on the same database as
someone is using Access 2.0 or a VB3 program.

(david)

 

[Tony Toews [MVP]]

Corruption is only a problem with version 2.0 database if someone
uses Access 2000-2003 at the same time on the same database as
someone is using Access 2.0 or a VB3 program.

Almost. Newer versions of Access at times will be able to open and
fix corruptions that older versions of Access couldn't handle. This
was particularly notice with A2000 and A2003.

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/

 

[Guest]

Almost. Newer versions of Access at times will be able to open and
fix corruptions that older versions of Access couldn't handle. This
was particularly notice with A2000 and A2003.

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/

But the statement still stands doesn't it?

Access 2003 can fix problems in the Access 2000 project that
Access 2000 can't fix.

Sometimes you might be able to compact/repair a database
with the Jet Compact./Repair utility that you can't compact/repair
with Access 2.0 (Although this possibility is so rare that I've never
experienced it)

But apart from the problem caused by Access 2K+ deleting the
LDB file while Access 2.0 is still using it, an Access 2.0 database
is no more likely to become corrupt than an Access 2000 database.

(david)

 

[Tony Toews [MVP]]

But the statement still stands doesn't it?

Access 2003 can fix problems in the Access 2000 project that
Access 2000 can't fix.

Sometimes you might be able to compact/repair a database
with the Jet Compact./Repair utility that you can't compact/repair
with Access 2.0 (Although this possibility is so rare that I've never
experienced it)

But apart from the problem caused by Access 2K+ deleting the
LDB file while Access 2.0 is still using it,

I hadn't come across that problem before.

an Access 2.0 database
is no more likely to become corrupt than an Access 2000 database.

That's very likely.

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/

 

[a a r o n . k e m p f]

bullshit you just deny everything

MDBs haven't been updated in several versions of Access and thus are
dead end technology.

Aaron
--
Aaron Kempf, Microsoft Certified IT Professional: Database
Administrator