Access DB grows terribly fast

[Steve Thomas]

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.

[MartinLiss]

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!"

[Steve Thomas]

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

[LG]

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?

[Steve Thomas]

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?

[LG]

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.

[svskumar]

USE ADO its faster on ODBC databases

[Steve Thomas]

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?

[Buzby]

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]

[LG]

Steve!

Have you fixed it?

[Steve Thomas]

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????

[Andy Roberts]

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