Access97's mdb break limit

ShuckyD · August 2nd, 2007, 09:55 AM

I know (or at least am pretty sure) that Access 97 databases cannot get over the 1gb limit. Unfortunately we have a piece out antiquated software that still uses Access 97 databases so there is always a constant eye out there on certain files. The screenshot below is one of those files. As you can see the filesize and the filesize in parentheses is different, as is with most files that ive seen. Also off to the right it is seen by explorer as 928mb, so a entirely different number than is seen in its properties window.

What i want to know is what is the actual break limit that CRITICAL on Access97 databases? I see 906mb and i see 950mb yet I see 928mb when I check it through My Computer. Which one is the real determining one and also is it 1000mb or is it closer to 1024mb when they begin to "crap out." This has been an on-going thing with this file for the past year or so. It grows to 975mb or more (in explorer) and then we compress it with Access and it get down to about what it is seen at now.. Once i have heard of them compressing it after it broke the 1gb limit. They proceeded to get people out of the software so that they could do a compact on it mid-day.... Thankfully I check it every other day and usually compact it weekly..

Steve R Jones · August 2nd, 2007, 11:03 AM

Do you run the "Compact database" function in Access?

Have you looked through MS Knowledgebase to see if there is a file size limit? I'm guessing there isn't a limit.....

ShuckyD · August 2nd, 2007, 11:22 AM

ive always been told that Access 97 databases break at the 1gb limit and have always been under that assumption. I usually use the built in feature in Access 97 to compact the database, but i have also used a utility called JetComp4 when Access is not available on a computer (Access 97)

http://www.mssqlcity.com/Articles/Co...QLvsAccess.htm

although it doenst say what the max amount of rows are that you can have in an Access 97 database, I'm curious to know

U-96 · August 7th, 2007, 05:29 AM

Access 97 does have a 1GB limit - but this is per .mdb file and you can make a database with more than one .mdb file, which might offer a short term fix. There are no max rows AFAIK - it's the 1GB limit that will define that, depending on the depth and number of your tables.

You really should think about migrating to another solution though - even if it's a newer version of Access.

ShuckyD · August 8th, 2007, 12:43 PM

the software that we are using is old and was made to work with 97 and so until we move our whole client base to the newer sql version of the app then we'll be plauged by this limit

Rootstonian · August 11th, 2007, 10:46 AM

ShuckyD, you lucky dog! Data conversions and interfaces are my speciality

cadetstimp · August 12th, 2007, 10:15 PM

Do they need all the information in the MDB everytime they access it?

Maybe they could start to archive the older or 'closed' information in the MDB to another file so that the MDB everyone will access everyday is 'active' information.

Either way, I would start to think of a backup procedure for the 97 MDB so that you always have a daily backup copy. Especially if your ramping for a future SQL migration.. you'll need a copy to play with anyways when pre-checking the new SQL version.