Does File Size Matter?

  • Thread starter Thread starter George Shubin
  • Start date Start date
G

George Shubin

We have recently experienced a problem of data simply disappearing. This
started happening in a database that we have been using problem-free for
over 4 years. But over the years as data has accumulated, the size of the
MDB (Access-2000, data only, no forms or reports or code) has now grown to
over 220 MB, consisting of over 1.5 million records of data in 20 different
tables. Now we are simply losing data, randomly, it seems.

The data is sales history and transaction information, and is accessed by
3-4 concurrent users via a VB6 front-end. The VB6 program has not changed
in over 9 months, so no errors have crept into the logic of the program.
The disappearing data problem has been manifesting itself within the last
month or so.

So... my question is: should I be looking at migrating to SQL Server or
splitting the MDB into pieces to hold the larger tables? Is there something
in Access that becomes unstable after the MDB grows to such a size or the
number of records exceeds a certain limit? How do I explain what is
happening and what do I recommend to management?

Thanks for any help or advice.

GS
 
I have a customer whos BE MDB is over 700 Mb. There have been instances
when it got corrupted (it is used over the network at all times with a
compiled front end), but in general a compact/repair works. Have you
tried that? Of course - back it up before trying.

Pavel
 
Pavel Romashkin said:
I have a customer whos BE MDB is over 700 Mb. There have been instances
when it got corrupted (it is used over the network at all times with a
compiled front end), but in general a compact/repair works. Have you
tried that? Of course - back it up before trying.

Pavel
Click to expand...

I also have gone way beyond the limits you are describing. So I would
suspect that it is a corruption of some sort. Compact and Repair should be
done routinely (at least once a week) on a DB of that size. I would
recommend using the JetComp utility from Microsoft:

http://support.microsoft.com/default.aspx?scid=kb;en-us;295334
 
Thanks, guys.

I think I'll go ahead and import all the tables into a fresh MDB, and then
add compact/repair logic to the program for performing compaction several
times a week. We'll see how that goes.

GS
 
In addition to what others have suggested, if your mdb gets too large,
you can always partition it into multiple mdb's and then link the
multiple tables to a single mdb. I once had a student who had an 8 GB
Access application configured that way. Granted, he was in a SQL
Server class learning how to convert it to an OLAP database where such
a beast belongs, but he claimed he'd had it running for several years
without any corruption problems.

-- Mary
MCW Technologies
http://www.mcwtech.com
 
I too have a dB much larger than the one you describe.
Compact and repair are essential and believe it or not I do it every night
before backup.

FYI: MS access has a 2GB file limitation (per .mdb) and a 2MB record limit.
in the data that is 'disappearing', is the record larger than 2MB?

file in FWIW category

HTH,

Rudy
 
FYI: MS access has a 2GB file limitation
(per .mdb) and a 2MB record limit.
Click to expand...

A Jet record can't be anywhere _near_ 2MB. Access 97 and earlier had a 1 GB
per MDB file limit; Access 2000 and later have the 2 GB file limit you
mention.

According to the Access 97 Help, records have a size limit of 2000
characters, and that is still the specification -- however, Access 97 stored
one-byte ASCII characters and Access 2000 and later store two-byte Unicode
characters, so the byte-size limit is more.

Larry Linson
Microsoft Access MVP
 
Back
Top