Access size 1

[evergreean]

My Access 2000 database increases about 600 records a month which equals around 5MB. So it grows about 5 to 6MB a month.

I wont have alot of concurrent usage on the database and the front end will be a Web Application.

Please advise how long this database will last before I will start having problems with max size, records and performance.

 

[CautionMP]

The Microsoft Jet data base engine supports files up to a maximum of 1 gigabyte in size.

1,024 / 6 = 170 months (souble check the math on that).

(GMT-07:00) Mountain Time (US & Canada)

 

[Golom]

It's Access 2K so the limit is 2GB. (341 months or 28 years, 5 months)

The usual rule of thumb is that you could start seeing problems when it hits around 90% of the max size.



[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[evergreean]

Thanks,

I seem to hear alot that Access cant handle heavy concurrent use and size and performance issues but my current Access database will only have 7 to 8 concurrent users accessing different records with web front end and has not had any problems. But people suggest that I upgrade to SQL Server or Oracle.

 

[easyit]

I have more then 80 users in a db, works fine. Only real drawback is speed, which is one reason to switch to sqlserver.

Your db grows 5 mb a month, is that with or without compacting & decompiling?

EasyIT

 

[evergreean]

Grows 5 to 7 MB a month without compacting.

I dont know what decompiling is?

 

[easyit]

..use /decompile as a commandline option.

 

[belovedcej]

you might also want to tryout the database utility "compact and repair." in fact, options give you a check off box to compact on close.

 

[evergreean]

Thanks,

Do you mean spead as far as processing on the Front end using the Web application?
Because right now it processes very quickly.

I am sure I will need to check speed issues when I hit over a few hundred MB.

 

[easyit]

I suggest not to use the compact on close option. It makes the closing a lot (!) slower.

 

[evergreean]

How often you recommend I compact and repair on my database that grows 5 to 7 MB with 600 to 800 records a month?

 

[easyit]

Hm,

I would say try & error on this. Even once a month could be too much, although it wouldn't hurt of course. how long is this db in operation? have you experienced corruption, sluggishness?

EasyIT

 

[JerryKlmns]

evergreean,

how about take a backup copy and then compact & repair on daily basis around 04:30:00?
You do take a backup on daily basis, don't you?

 

[JerryKlmns]

Just read a post from Roy-Vidar mentioning this faq705-2852

 

[mp9]

You can use the Handy Access Launcher to schedule db compacts, repairs, decompiles, macro runs... to run at any time/day/recurrent pattern.

You can get HAL from

http://www22.brinkster.com/accessory/resources/001.shtml

 

[James529]

JerryKlmns (IS/IT--Management) 7 Apr 06 3:48

Just read a post from Roy-Vidar mentioning this FAQ705-2852


I like the idea of auto compacting but ran into a problem. The problem is this:

The database resides on a server running win 2003 terminal services, the folder the database is in has permissions set on it.

When the compact runs, a new database is created in folder and the permissions on the new database are not automatically reset. This requires a systems administrator to re-apply the permissions to the folder (and it's contents) before it can be accessed by anyone.

Anyone have a solution that will negate the administrators intervention?

thanks

 

[lameid]

To work around the compacting issue you could create a sub folder with all the correct permissions for the file on it.

When you compact, compact to the sub folder. Since this is multiuser you should probably move the file to the sub folder first to ensure noone ends up opening the other copy (compacting to a different location creates a copy in my experience). Then MOVE the file to the above directory. Files in windows inherit permissions from the parent folder and file permissions persist when moved on the same server (across servers is technically creating the file and then inherits permissions; although I have not verified this since NT4 so MS may have changed this).

As how to accomlish that programmatically... Perhaps a batch file or vbscript called via the shell function? I don't know much about vbscript but I think batch files are probably on their way out. A batch file like below ought to do the trick... There might be some play in the double quotes. Compact is still a valid command line switch for Access, right? One more thing, batch files are text files with the extension bat. They run a series of dos commands.

@echo Off
Echo Press any key after Access is closed.
Pause
Move "x:\Path1\DB.mdb" "x:\Path1\sub Folder\db.mdb"

"%systemdrive%\Program Files\Microsoft Office\Office\MSACCESS.EXE" /Compact "x:\Path1\sub Folder\db.mdb"

Move "x:\Path1\sub Folder\db.mdb" "x:\Path1\DB.mdb"

 

[evergreean]

Thanks for all the posts.

On all my databases I manually back them up almost everday by copying each Access database from my server and putting them on a zip disk. Do any of you ever automate that and if so how??


"Even once a month could be too much, although it wouldn't hurt of course."
On all my other Access database that average about 2 to 7 MB in size after 3 or 4 years of use, I compact about once or twice a year. I assume I should do it more often.

"how long is this db in operation? have you experienced corruption, sluggishness?"
This database is only a month old but I estimate it to grow about 5 to 7 MB a month because that is what it was doing in test environment. I have not experience any corruption in this database.

In the past on the very few times I seen corruption in my other databases it always gave me the option to fix it and Access always fixed the database.

 

[easyit]

I have several multiuser db's, which I compact only occasionally - that is around 3 times a year each. Instead of automating the process, I have a entry in outlook; less work .

The only db's that I compact automatically and daily are those that are copied from the network to the localdrive (a performance issue). Compacting saves time, because the db shrinks from 240 Mb to 110 Mb. However if I don't compact this db, it grows further to around 350 Mb - but not much more.

With 6 Mb a month you could easaly compact only once a year (probably even once in five years).

EasyIT

 

[evergreean]

Thanks