Do other developers have experience of Access dumping tables?

[Chrisso]

Hi All

I had a table with two fields only. One field was text where I
recorded username and the other field long number to hold primary keys
of objects of interest to the user when they click in list boxes. This
way I can keep track of which records my users are interested in
through using the list boxes.

I am currently developing this database and was the only user. An
error occurred of some sort when my code was running delete and append
queries on this table (after working fine for 1.5 days) and Access has
seemed to dump the table! It no longer can be seen in the tables
window!

Do other developers have experience of Access dumping tables?

Is this just bad luck or have I done something wrong in my design
(which is very simple) to cause this? Can unhandled VB errors result
in Access dropping a table?

Any thoughts would be of help becuase I have to decide whether to
abandon this approach.

Cheers
Chrisso

 

[HiTechCoach via AccessMonster.com]

Chrisso,

Are multi[ple users opening the sane database or front end? If yes, then this
is part of your problem.

You database MUST be spit into a front end and back end. Each user MUST alsi
have their own copy of the front end that is NOT shared. It is common to
place the front in of the local hard drive of the workstation.

Boyd

 

[Chrisso]

Thanks for your input Boyd. I actually goofed after a long day and
Access did not dump my table all along.

However I would like your advice regarding your comments on front ends
etc. I am designing this DB very simply and do not intend to have any
tiers at all - therefore cocncurrent users would be appending and
deleting from my table when it goes into production with a maximum of
5 users.

Are you saying that Access does not allow such concurrent operations
natively? I assumed Access would lock the tables during
transactions.....

Chrisso

 

[Keith Wilby]

Thanks for your input Boyd. I actually goofed after a long day and
Access did not dump my table all along.

However I would like your advice regarding your comments on front ends
etc. I am designing this DB very simply and do not intend to have any
tiers at all - therefore cocncurrent users would be appending and
deleting from my table when it goes into production with a maximum of
5 users.

Are you saying that Access does not allow such concurrent operations
natively? I assumed Access would lock the tables during
transactions.....

Chrisso

It's the sharing of the user interface that can be a problem, not record
locking. Have a look at this:

http://www.granite.ab.ca/access/splitapp/why.htm

Keith.
www.keithwilby.com

 

[John W. Vinson]

Are you saying that Access does not allow such concurrent operations
natively? I assumed Access would lock the tables during
transactions.....

It does... but it doesn't do it perfectly. Multiple users sharing the same
frontend or unitary database is allowed, but puts you at considerable risk of
database corruption and inefficiency. In addition, if any user attempts to
change the *design* of anything (a form, a report, vba code) while other users
are in the database, you'll get conflicts.


John W. Vinson [MVP]