Geoffrey wrote:
> I've got a client who is following my suggestion that they replace a set
> of excel spreadsheets with a database solution. They are looking at two
> proposals, postgresql solution or an Access solution. The requirements
> will include vpn connectivity from one site to another. It appears they
> will be going with the Access solution. I've got concerns regarding
> this based on research I've done that seems to indicate that Access,
> when used in a multi-user solution is easily corrupted. Does anyone
> have any knowledge/experience with such issues?
>
My experience: Access, like any desktop-based database, is subject to
data corruption in multi-user environments. Pretty easy to picture when
every machine keeps chunks of data in their local memory, has to write
back to the network and then somehow push the updates to the other
clients. You *can* avoid data corruption using very strict locks -- but
you can't use Access' built-in table/row locking as those functions also
suffer from the same multi-user synchronization problem. What you can do
is to write your own locking functions based on file handle access. Make
a temp dir on a network and everytime you want to update/insert, attempt
to create a file based on the table/record (e.g. CUSTOMERS-0 for
inserts, CUSTOMERS-1000 for specific records) -- a valid file handle
would then mean you have a lock. For the most part, you can avoid data
corruption if you bypass the built-in locking functions but you will
still have to periodically take down the system during production hours
for reindexing when even these measures fail.