Access database corrupted - Relationship between related tables LOST

[Leinad Kong]

I'm using Access 2002, as front-end and back-end as well:

1) I faced database corrupted problems, when more than 1 user edit
concurrently.
I'm using All-records Locking, and open-exclusively as default.
But, still face the same problem: Database corrupted! (But, it allows
to be repaired)

2) I found that the relationship linkage between those related
tables(which corrupted) was disconnected/lost after problem (1)
occured. Is there anyway to prevent this happen or to master-lock the
relationship?

3) I know too little about SQL server, but I'm going (have to)to move
my database into SQL Server as back-end db. Any important steps/things
should me know before i do this? or anyway tool or better way of doing
this. Any guides and full instruction available in converting access
db into SQL db while remaining the access forms, queries, macro,
reports, VB scripts as front-end.

I need those who expert in this help me... THANKS : )

LEINAD
IT SERVICES DEPARTMENT

Anyone who can advise me on this problem

[Allen Browne]

HI Leinad

It is possible to get Access running very reliably as a multi-user database.

The default locking (optimistic locking, also called 'no locks') is the best
approach for nearly everyone, and is certainly the most tested scenario. I
would encourage you to choose that option unless you have a reason not to.

Other suggestions for preventing corruption are in this article:
http://allenbrowne.com/ser-25.html
The article explains the most common cause of corruption. Please take
seriously the comments regarding the kind of network you can use, splitting
the database so each user has their own front end, ensuring that everyone
has at least SP2 for Office 2002, *and* SP8 for JET 4, turning off Name
AutoCorrect, and working around the LinkChildFields bug.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Leinad Kong" <ks****@hotmail.com> wrote in message
news:39************************@posting.google.com ...

I'm using Access 2002, as front-end and back-end as well:

1) I faced database corrupted problems, when more than 1 user edit
concurrently.
I'm using All-records Locking, and open-exclusively as default.
But, still face the same problem: Database corrupted! (But, it allows
to be repaired)

2) I found that the relationship linkage between those related
tables(which corrupted) was disconnected/lost after problem (1)
occured. Is there anyway to prevent this happen or to master-lock the
relationship?

3) I know too little about SQL server, but I'm going (have to)to move
my database into SQL Server as back-end db. Any important steps/things
should me know before i do this? or anyway tool or better way of doing
this. Any guides and full instruction available in converting access
db into SQL db while remaining the access forms, queries, macro,
reports, VB scripts as front-end.

I need those who expert in this help me... THANKS : )

LEINAD
IT SERVICES DEPARTMENT

Anyone who can advise me on this problem

[Leinad Kong]

Thanks Allen.

But i'm still doubt of using "No Locks" will be the best solution.
Because i changed from it to "Edited Lock", and it caused "less"
corruption.

And why does more restrictive "lock" may cause more conflicts?

Another question:
How do i apply lock to avoid user to edit the same record at the same
time within a form where there are main form(use table with 1 primary
key-"A") and sub form (use table with 2 primary key -"A" & "B"), they
are linked with "A".
It seems like the "page-locking" locked all records whenever only 1
user is editing 1 record. Is there any way of doing it?

Leinad

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

HI Leinad

It is possible to get Access running very reliably as a multi-user database.

The default locking (optimistic locking, also called 'no locks') is the best
approach for nearly everyone, and is certainly the most tested scenario. I
would encourage you to choose that option unless you have a reason not to.

Other suggestions for preventing corruption are in this article:
http://allenbrowne.com/ser-25.html
The article explains the most common cause of corruption. Please take
seriously the comments regarding the kind of network you can use, splitting
the database so each user has their own front end, ensuring that everyone
has at least SP2 for Office 2002, *and* SP8 for JET 4, turning off Name
AutoCorrect, and working around the LinkChildFields bug.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Leinad Kong" <ks****@hotmail.com> wrote in message
news:39************************@posting.google.com ...

I'm using Access 2002, as front-end and back-end as well:

1) I faced database corrupted problems, when more than 1 user edit
concurrently.
I'm using All-records Locking, and open-exclusively as default.
But, still face the same problem: Database corrupted! (But, it allows
to be repaired)

2) I found that the relationship linkage between those related
tables(which corrupted) was disconnected/lost after problem (1)
occured. Is there anyway to prevent this happen or to master-lock the
relationship?

3) I know too little about SQL server, but I'm going (have to)to move
my database into SQL Server as back-end db. Any important steps/things
should me know before i do this? or anyway tool or better way of doing
this. Any guides and full instruction available in converting access
db into SQL db while remaining the access forms, queries, macro,
reports, VB scripts as front-end.

I need those who expert in this help me... THANKS : )

LEINAD
IT SERVICES DEPARTMENT

Anyone who can advise me on this problem

[Allen Browne]

If you lock all records, you will certainly get conflicts more often, i.e.
you will get a conflict if another user tries to edit *any* part of the
table. It is also more likely to cause you problems because it is less
tested, i.e. if there are millions of Access databases running with "No
Locks", the chance of uncovering a bug is large, whereas if there are only a
handful runnning locking "All Records" you are much more like to find a
problem that has not been uncovered before.

If the subform is based on a different table, it should not cause a locking
issue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Leinad Kong" <ks****@hotmail.com> wrote in message
news:39************************@posting.google.com ...

Thanks Allen.

But i'm still doubt of using "No Locks" will be the best solution.
Because i changed from it to "Edited Lock", and it caused "less"
corruption.

And why does more restrictive "lock" may cause more conflicts?

Another question:
How do i apply lock to avoid user to edit the same record at the same
time within a form where there are main form(use table with 1 primary
key-"A") and sub form (use table with 2 primary key -"A" & "B"), they
are linked with "A".
It seems like the "page-locking" locked all records whenever only 1
user is editing 1 record. Is there any way of doing it?

Leinad

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

HI Leinad

It is possible to get Access running very reliably as a multi-user database.
The default locking (optimistic locking, also called 'no locks') is the best approach for nearly everyone, and is certainly the most tested scenario. I would encourage you to choose that option unless you have a reason not to.
Other suggestions for preventing corruption are in this article:
http://allenbrowne.com/ser-25.html
The article explains the most common cause of corruption. Please take
seriously the comments regarding the kind of network you can use, splitting the database so each user has their own front end, ensuring that everyone has at least SP2 for Office 2002, *and* SP8 for JET 4, turning off Name
AutoCorrect, and working around the LinkChildFields bug.
"Leinad Kong" <ks****@hotmail.com> wrote in message
news:39************************@posting.google.com ...

I'm using Access 2002, as front-end and back-end as well:

1) I faced database corrupted problems, when more than 1 user edit
concurrently.
I'm using All-records Locking, and open-exclusively as default.
But, still face the same problem: Database corrupted! (But, it allows
to be repaired)

2) I found that the relationship linkage between those related
tables(which corrupted) was disconnected/lost after problem (1)
occured. Is there anyway to prevent this happen or to master-lock the
relationship?

3) I know too little about SQL server, but I'm going (have to)to move
my database into SQL Server as back-end db. Any important steps/things
should me know before i do this? or anyway tool or better way of doing
this. Any guides and full instruction available in converting access
db into SQL db while remaining the access forms, queries, macro,
reports, VB scripts as front-end.

I need those who expert in this help me... THANKS : )

LEINAD
IT SERVICES DEPARTMENT

Anyone who can advise me on this problem