[AccessD] Access 2007 - compacted

I hear you.
This database is a research questionnaire tool and has strict
requirements. The researcher must be able to put it on a thumb drive and
transfer it to a laptop.  There could be several laptops involved at
different locations at the same time without network and/or web capability.

The researcher must be able to modify the questionnaire as needed... to
include deleting the current questionnaire and creating another one. Each
question can be modified, moved from one section to another, activated or
deactivated at will. Participants are assigned participant numbers (PIN's)
used to log in the system.  PINS are changed for every study.

After a given number of participants have taken the questionnaire,
responses to the questions are exported into Excel and then imported into
SPSS.

The researcher has the option of 36 different question types, each coded to
correspond to the researchers requirements so when the responses are
exported no additional coding is required in SPSS. Did I mention the
researcher can change the background color and text color as needed?

All this must be done through a user interfaces for the researcher and
participants.

Thanks,

Jim



On Tue, Mar 5, 2013 at 11:32 AM, Jim Lawrence <accessd at shaw.ca> wrote:

> Hi Jim:
>
> In order to increase speed of a database (even more so in a network type
> environment) in many cases it is easier, when a record is deleted to just
> turn it off so it is no longer displayed.
>
> Logically, the system should be able to go back and use the old record
> position for a new record but if the system uses Sparse Storage, in
> otherwards, only stores filled fields, the deleted record space is no
> longer
> the same size. A similar situation may happen when a table is modified is
> some way. There are a number of other situations that may cause the system
> waste or leave unused space and until the database is compacted, unusable
> space. Unlike memory management, like garbage collection, this process can
> not happen in real time as given the potential of issues and time
> constraints especially in a multi-user environment. So every record
> addition
> or change is just added to the end of the table space.
>
> In conclusion, compacting an active database, like an MS Access MDB, when
> it
> is no being used should be a regular discipline. Note that the MDB database
> was never designed to work in a large active multi-user environments but it
> is just fine for a small Mom an Pop type businesses.
>
> If you are going to be building or maintaining a growing and/or active
> system do yourself a favour and move to another BE database with greater
> capabilities...
>
> Jim
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Hewson
> Sent: Tuesday, March 05, 2013 6:33 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Access 2007 - compacted
>
> I had something happen that I can't explain. I hope someone on this list
> could shed some light on it.
>
> I have a complex database, 77 tables (2 linked, 2 user system), 518
> queries, 161 forms, 146 reports, 29 modules and 2 classes and approximatel
> 42,000 lines of code.
>
> While I was creating this database the size increased.  The latest was over
> 92 MB.
> I was doing some trouble shooting because one query wasn't working
> correctly with one of the linked tables.
> I got an error message... stating it couldn't find a field in the query and
> then it shut down.
> I reopened the database did the usual - compile, saved, etc. I fixed the
> error and then the issue went away.
> Then I did a compact and repair.
> Everything is working, all the code is there, it's as if nothing happened.
> Great! I'm back in business.
>
> I checked the size of the it's down to 22.3 MB! How can that happen? - goes
> from over 92 MB to 22.3 MB.
> I'm happy the file size is down and everything works! It also seems to work
> faster.
> I'm confused.
>
> Can that be explained?
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>