Compact / Repair Required ?

[Logit]

.
I am not as familiar with Access as I am VBA/Excel and VB.

I have a database project constructed in VB6. It stores records in an Access table/sheet.

#1 - Is it necessary that this arrangement require a periodic Compact/Repair process since Access is only storing the data ?

#2 - I have read different websites that talk about Compact/Repair when the database program is closed and other sites that speak to C/R as the last thing BEFORE the database program closes.
Which process is better and why ?

#3 - For some reason (maybe my VBA / VB experiience) I an leaning toward the C/R as a 'before closed' process of the database. Will this produce future errors ?

The project uses Microsoft ActiveX Data Objects.



Thank you for your assistance !

[ranman256]

1. You can, not required. If the db grows exponentially,then I would c/r regularly.
2. You can c/r Open or closed
3.you don't have to do it daily. I compact weekly,or 2 weeks, but our db doesn't grow rapidly.

[isladogs]

Just to add to ranman's comments
Switch off the option Auto Compact On Close as it can lead to corruption especially in a multi-user environment
If this is a split database backend with multiple users, make sure you are the only user before compacting

[Logit]

Thank you for your replies.

The database will be a 'desktop' variety (non-network) personal database. Doubtful the total number of data rows will ever exceed 100,000 - 300,000 rows. Perhaps 20 columns each data row.

Interesting the built-in 'Auto Compact On Close' can cause corruption vs using a macro to do the same won't ?

[isladogs]

Starting in A2010, it is no longer possible to do a C&R on the current database using VBA or a macro.
I believe MS removed that functionality due to risks of corruption.
Even if you can do it in 2007 using a macro, I suggest you don't.

C&R is an important tool in your toolbox but take care.
Before running a C&R, make a backup for safety and close all open objects.

Decompiling is another excellent tool for developers. See this link for details http://www.fmsinc.com/MicrosoftAcces.../Decompile.asp

[Logit]

.
Seems like there is waaaay too much trouble using ACCESS as the data depository. I can use EXCEL instead.

Your thoughts ?

[isladogs]

Not sure why the previous answer led to that response

In answer to your question, that very much depends on your needs.
Although not its primary purpose, you can use Excel as a simple flat file pseudo database.
However, if you need any relational database features you need Access.
They are very different products and intended for different purposes.

Suggest you google the topic to get a variety of opinions

[Logit]

This is a flat file database. Any filtering can be accomplished via the Data Filtering option of Excel. Thank you so much for your answers.

[RogerD]

A quick comment about "If this is a split database backend with multiple users, make sure you are the only user before compacting"
I have split the database as per suggestions, and we currently have 6 users who each have a copy of the front end on their computer, with the back end on a shared drive.

I have told 4 of the users they need to compact and repair weekly in order to keep the db from growing too large. The database we are using "grows" on the users FE, not the shared BE, at least that is what I have been seeing in the short time we have been working with a shared db. Were the instructions I gave correct?

[isladogs]

Depends on the activity over that week.
In some cases that will be too often. For others perhaps not enough (less likely??)
Perhaps a better method would be to use VBA to check the file size & if it has exceeded a specified size, show a message advising that a compact should be done

[RogerD]

http://www.fmsinc.com/MicrosoftAcces.../Decompile.asp
was reading on the link above:
It reads as if the compact and repair "The Compact and Repair feature only applies to the tables and does not impact VBA code." quoted from the above link. So, this means the directions I gave to each end user to "Compact and repair", doesn't accomplish anything because the Growth in the database FE is not going to be resolved by a C & R?
Although I have been working with Access for about 10 years, this is the first time I have split a database, so all new to me.

[isladogs]

The answer is...it depends.
Code will increase slightly in size if you are compiling 'on demand' but that's not an issue if the FE is fully compiled before distribution.
Almost all the growth will be in the tables.
If you are working with temp tables in the FE using make table or append or delete queries, the FE database will increase in size.
Otherwise it's likely that doing a C&R weekly is too often.

That's why I suggested basing it on a file size check

[RogerD]

OK That explains why the FE grows.
One user runs a macro that opens 5 tables, deletes all data, uploads 10 xl spreadsheets, runs 36 appends or update queries, exports 2 xl spreadsheets and copies 4 tables out to xl files.
We import our production schedule into the database, do a bill of material explosion, and export as xl to analyze material requirements.
So the user who runs the macro will be the one that has the most growth of the FE, while the other users may not need to C&E as often.
I did a C&R on the BE and it is now about 6 meg. My FE is about 5.5 meg.