Access DB grows terribly fast

I know this is the VB world but I use Access along with VB. I have a database that starts off at about 10 meg in size. During the day I have about 6 users adding records. At the end of the day the DB is sitting at about 200 meg. I look to see how many records were added and it totals maybe 100. The record length is only 200 bytes. I then compact the database and it drops back down to about 2 or 3 meg more than it was before the day began. I am doing alot of SQL statements and updates to the records being entered. Any ideas? I have access to an Oracle system. If I link Access to the Oracle database, would that possibly stop the problem?

Thanks in advance.

You could write a little program that automatically runs once a day and uses the following snippet to repair and compact the database.

DBEngine.RepairDatabase sDBName
DBEngine.CompactDatabase sDBName, "compact.bak"
Kill sDBName
Name "compact.bak" As sDBName

------------------
Marty
What did the fish say when it hit the concrete wall?
> > > > > "Dam!"

Yes, Thank you. I have thought about this but I cant imagine that this would not be something that Microsoft would not want to correct. Does anyone know if Oracle or Sequel Server would have the same problems?

Thanks

Total usage is not only amount of records stayed at the end of the day. Are your enserting a lot of records and deleting them later?

The users are not deleting records. They are adding records and then other users will go in and add data to those new records. The only thing I can think of is that I do a whole lot of the following:

Dim DB as Database
Dim RC as Recordset
Dim strSql as string

set DB = currentdb
strSQL = "Select Statement goes here"
rc = DB.Openrecordset(strSql)

Would this cause the DB to hold or create tables or recordsets that reserve space and need to be compacted?

It seems for me that you are using DAO. Wich is not a right way for the network. As soon as your APP is for multi-users over network, I would go with ODBC.

USE ADO its faster on ODBC databases

I did some experimentation with ODBC. I could not figure it out. I was able to look in the ODBC under control panel and I think I was able to create a user DSN. However, I wasn't able to access it. How do I set it up and how do I do a query against it?

Access databases get very bloated very quickly. SQL Server doesn't suffer in the same way. I think the problem lies when you delete records from Access - they are not actually deleted, just flagged as being deleted. When you compact these are actually deleted. (All a compact is doing is creating a new database, copying the live records over, deleting the original and renaming the new one to the same as the original - don't believe me, watch the directory the database is sitting in while you compact it)



------------------
Mark "Buzby" Beeton
VB Developer
[email protected]

Steve!

Have you fixed it?

I have not fixed the problem but have issolated it. I removed all the tables from the Access database and linked them. I then watched what heppened. Turns out the access DB that has absolutly no tables, is growing and the other linked tables grow at the proper rate. I am now considering rewriting so that my front end is done in VB, using a datagrid to access DB. I am also considering Oracle as apposed to Access because I am having speed issues. I am choosing Oracle because we already have a box. I would have chosen MS sequel but don't have a box. I am a little nervous as I have never touched Oracle. I am hopeing I can copy my tables into Oracle very easily????

Are U using Access 2000 if so try converting it back to Access 97 and see what happens. I had the same prob with VB6, ADO and Access 2000