Need help for sharing the database in multiuser environment....

[Ahamed]

Hi Developers,

In my company, I've done my application software and its working in multiuser
environment.

I'm using in Win-NT 4.0 as server and workstations in Win-98.
My database is MS-Access 97 & using DAO 2.5/3.51 compatible library.

The database file is put in server & location is \\server\server\data\auto.mdb.
And my application package is in each user.

Dim mydb as database
Dim rstbank As Recordset
Dim rstcust As Recordset
Set mydb = OpenDatabase("\\server\server\data\auto.mdb")
Set rstcust = mydb.OpenRecordset("custmast", dbOpenDynaset)
Set rstbank = mydb.OpenRecordset("bankmast", dbOpenDynaset)

In the above all commands, I put in each form load event & the database close
in each Form_QueryUnload event.
I've more then 50 forms and each form to open the database & close the database
whenever it's close the form.

Here more then 4 users access this shared database. This is working well.
But in some situation, if more then one user access the same table to update
or edit, the database is become corrupt. Then I made repair & compact database.
Sometime its repair good, but some time it's can't recover. It's fully damaged.
So here I face it heavy problem in my company. And also my software is working
too slow in network.

I faced the error: 3197
'The Microsoft Jet database engine stopped the process because you and another
user are attempting to change the same data at the same time'.
This error occurs & the database can't recoverd, its fully damaged.

And sometime error occurs:3159 'not a valid bookmark'
Then i tried to see the table through MS-ACCESS, its fill all records & fields
as "#VALUE". only if i made repair the database, it can see the all records
(original datas) in table.

So, what is the good & best way to open the shared database in multiuser
environment?
I need to change the program fast in network environment.
I know ADO well. Is it convert all to ADO becomes fast? How?

So please kindly request you to need the above requirements as soon as possible.
I appreciate your helps.
Thanks a lot in advance.
Regards: Ahamed

[Wineskin]

MS Access is not really designed to work for a multi-user environment. You
would definitely run into serious problems with your database everytime 2
users access the same data.

You might want to try switching to SQL Server. If it is imperative that you
use MS Access, then you might want to try to learn about data-splitting.
But it still is no guarantee and heavily relies on the design of your tables.
It just is an alternative solution. Access comes with its own tool called
the data-splitter or something like that.



"Ahamed" <ahamed2605@yahoo.com> wrote:
>
>Hi Developers,
>
>In my company, I've done my application software and its working in multiuser
>environment.
>
>I'm using in Win-NT 4.0 as server and workstations in Win-98.
>My database is MS-Access 97 & using DAO 2.5/3.51 compatible library.
>
>The database file is put in server & location is \\server\server\data\auto.mdb.
>And my application package is in each user.
>
>Dim mydb as database
>Dim rstbank As Recordset
>Dim rstcust As Recordset
>Set mydb = OpenDatabase("\\server\server\data\auto.mdb")
>Set rstcust = mydb.OpenRecordset("custmast", dbOpenDynaset)
>Set rstbank = mydb.OpenRecordset("bankmast", dbOpenDynaset)
>
>In the above all commands, I put in each form load event & the database
close
>in each Form_QueryUnload event.
>I've more then 50 forms and each form to open the database & close the database
>whenever it's close the form.
>
>Here more then 4 users access this shared database. This is working well.
>But in some situation, if more then one user access the same table to update
>or edit, the database is become corrupt. Then I made repair & compact database.
>Sometime its repair good, but some time it's can't recover. It's fully damaged.
>So here I face it heavy problem in my company. And also my software is working
>too slow in network.
>
>I faced the error: 3197
>'The Microsoft Jet database engine stopped the process because you and
another
>user are attempting to change the same data at the same time'.
>This error occurs & the database can't recoverd, its fully damaged.
>
>And sometime error occurs:3159 'not a valid bookmark'
>Then i tried to see the table through MS-ACCESS, its fill all records &
fields
>as "#VALUE". only if i made repair the database, it can see the all records
>(original datas) in table.
>
>So, what is the good & best way to open the shared database in multiuser
>environment?
>I need to change the program fast in network environment.
>I know ADO well. Is it convert all to ADO becomes fast? How?
>
>So please kindly request you to need the above requirements as soon as possible.
>I appreciate your helps.
>Thanks a lot in advance.
>Regards: Ahamed
>