Good book on Access administration

[melloman]

Specifically Access97.
I just got an account that has an access database that has apparently grown to barely under 500 megs, and needs to be cleaned up. I barely use access, but know the other office products pretty well (well enough to support them). Also, what kinds of stresses will that kind of database put onto a system, is it more memory intensive than hard drive, and how much will putting the network up to 100 megabit vice 10 megabit help? 6 computer network, win98. (I plan to put the database on a faster computer by itself, on win2k pro, set to do backgrounded/networked apps faster, and without a main user. A stripe set and scads of ram are also in the works.

Most access is happening on p2-400's with 128 megs ram.

[sdkfz]

To start you could do a couple of things that will probably have a big impact right off, do these while you are the only one logged into the database (this utility reads the ldb file that access creates when any user logs into the database so you know who is using the DB http://support.microsoft.com/default...EN-US;Q176670& )

1) open database exclusively and then through tools-->Database utilities--> repair and then compact the database. Do the repair first then the compact. You will see a big drop in size if this has not been done in a while (kinda like a defrag.
2) Once this is done the next steps will take a bit longer, basically you need to see what the heck has been going on and look for garbage left behind:
step one is to copy the whole thing somewhere else so you can screw it up in safety, just in case.
screen print the db opened to tables, queries etc so you have a list of what is in play, you can use the database documentor (tools--> analyze) but this is faster and uses less paper
A) Design each macro and review each step, crossing off listed queries/tables etc as you go (if you can not read the query/table name, copy it from the line and paste it into the comments line, which is a good idea regardless)
B) Repeat for the forms, and then the queries, mdules etc.

Once this is done anything not crossed off is suspect, why wsa it not crossed off? It could be left overs from development etc. Change it's name by adding an X to the name (instead of deleting it) and test to make sure nothing broke. I would be very careful at deleting, once you are certain that the item is not in use, change the production item and wait until all business cycles are done then delete it.

Finally, you mighth want to separate the tables from the database, (look up linking in help) , just remmeber that Access97 uses ABSOLUTE referncing, so all users must have the same mapping schema if you start breaking out the DB.

Access has a limit of 2 GB per database, by separating out tables into their own database you could end up with many GB in size, of course speed of use will slow as size goes up, and yes Access is a heavy ram user, 24 Mbs I beleive. Hope that helps

signed
self taught access guru wanna be

[doctorgonzo]

I personally use the Access 2000 developer's handbook (no good for you, but there may be an Access '97 handbook around, or you may be able to get Access 2000).

sdkfz has some good tips to start off with. However, you are really pushing the limits of Access. First of all, the size is a problem. 500 MB is way, way too big for an Access database. Although the official size limit is 2 GB, the effective size limit is much smaller, maybe around 200 MB or so. More than that and the Jet database engine can corrupt data. Not good.

Second, Access is a file-server program, not a client-server. That means that absolutely no data processing occurs on the server that actually has the back end data. The server only reads the data and sends it all over the network, where the client PCs crunch the data for queries, etc. So, setting the Win2K server to do background apps faster won't have an effect. Databases are disk-intensive, so the RAID array will help, and because all data is sent over the network, 100 Mbps will be better than 10 Mbps, but not by much.

I designed a database for the place I work for in Access '97, and even though it was much better than the non-relational DOS-based database we had been using for the past 10 years (Q&A, if anybody remembers), once we hit a couple hundred thousand records and a file size of only 150 MB, it just wasn't working anymore. Way too slow, because all of that data has to cross the network every time you do something. I moved it to SQL Server, which is pretty much what you have to do with a database of that size. SQL Server is a client-server program, so the data is processed on the server where it should be, and only needed data is sent over the network.

For more ideas, I will have to know more about what kind of database you have. How many records? Is it transactional or pretty much read-only? Are you planning on having several users access the database at once? Again, even though Access has a theoretical limit of 255 concurrent users, its real limit is 15 or 20.

[sdkfz]

doctorgonzo,

Q&A!, boy that takes me back, still have some floppies with data in Q&A, of course they are the old floppy floppies, haven't seen a 5 inch floppy drive in a while. (my win 3.x is on these floppies too)

agreed on the sql route, my DB is headed that way soon too.

melloman, this book helped me a lot when I was starting on access I am surprised it is still in print considering the current version of access out there

http://www.amazon.com/exec/obidos/tg...53533?v=glance