Multi-User DAO/JET AddNew doesn't work? Am I missing something? 
Author Message
 Multi-User DAO/JET AddNew doesn't work? Am I missing something?

I am working on a Multiuser application, that would be used by about 5 - 10
people at a time.  I have a Jet Database on a network and I can get access
to the Tables by more then one person at a time to view the data.

I can allow Edits to existing records with no problem, but when I attempt
an AddNew i always get error 3186, stating:

Couldn't save; currently locked by user 'x' on machine 'y'.

I am opening my databases like this:

in general declarations:

        Global dbComfort As Database
        Global rstCustomer As Recordset

in a function that opens my databases:

        Set dbComfort = OpenDatabase(MAINDATABASENAME)
        Set rstCustomer = dbComfort.OpenRecordset("customer", , dbOptimistic)

the save routine is like this:

                rstCustomer.AddNew
                rstCustomer.Fields("custnum") = strNewCustNum
        rstCustomer.Fields("custfirstname") = txtFirstName.Text
        rstCustomer.Fields("custlastname") = txtLastName.Text
        rstCustomer.Update

The error always happens on the UPDATE.

I am using VB5sp2 Professional under WIN95.

I know DAO is not the best way to go about this, but I need to demonstrate
it with DAO before I can convince them to spend the money on something
better.  I can only use Access databases currently.

Any ideas on what I am missing would be appreciated.



Sat, 26 Aug 2000 03:00:00 GMT  
 Multi-User DAO/JET AddNew doesn't work? Am I missing something?

I am not sure if you have done the following checking:

1.    Your version of DAO. The latest version on my machine is 3.5.

2.    Were you able to add new records by opening the table in access
environment? ie, without using code.

3.    Is the database stored on a server as .MDB, or in a SQL server?

4.    Was user 'x' on machine 'y' accessing the database when you tried to
do the update?

Conway

Quote:

>I am working on a Multiuser application, that would be used by about 5 - 10
>people at a time.  I have a Jet Database on a network and I can get access
>to the Tables by more then one person at a time to view the data.

>I can allow Edits to existing records with no problem, but when I attempt
>an AddNew i always get error 3186, stating:

>Couldn't save; currently locked by user 'x' on machine 'y'.

>I am opening my databases like this:

>in general declarations:

> Global dbComfort As Database
> Global rstCustomer As Recordset

>in a function that opens my databases:

>    Set dbComfort = OpenDatabase(MAINDATABASENAME)
>    Set rstCustomer = dbComfort.OpenRecordset("customer", , dbOptimistic)

>the save routine is like this:

>        rstCustomer.AddNew
>        rstCustomer.Fields("custnum") = strNewCustNum
>    rstCustomer.Fields("custfirstname") = txtFirstName.Text
>    rstCustomer.Fields("custlastname") = txtLastName.Text
>    rstCustomer.Update

>The error always happens on the UPDATE.

>I am using VB5sp2 Professional under WIN95.

>I know DAO is not the best way to go about this, but I need to demonstrate
>it with DAO before I can convince them to spend the money on something
>better.  I can only use Access databases currently.

>Any ideas on what I am missing would be appreciated.



Sun, 27 Aug 2000 03:00:00 GMT  
 Multi-User DAO/JET AddNew doesn't work? Am I missing something?

Patrick,

Try opening an Empty Recordset based on the key you wish to save to.

Global dbComfort As Database
Global rstCustomer As Recordset
Dim strSQL as String

Set dbComfort = OpenDatabase(MAINDATABASENAME)
strSQL="Select * From myTable Where myTableKey = '" & currentKey & "'"
Set rstCustomer = dbComfort.OpenRecordset(lstrSQL, dbOpenDynaset) 'I still
use the old method

If rstCustomer.RecordCount = 0 Then
    rstCustomer.AddNew
    rstCustomer.Fields("custnum") = strNewCustNum
    rstCustomer.Fields("custfirstname") = txtFirstName.Text
    rstCustomer.Fields("custlastname") = txtLastName.Text
    rstCustomer.Update
Else
    MsgBox "Duplicate Key ...."
End If

That way you shouldn't be grabbing a page someone has locked.

I hope this helps,
Myron.

Quote:

>I am working on a Multiuser application, that would be used by about 5 - 10
>people at a time.  I have a Jet Database on a network and I can get access
>to the Tables by more then one person at a time to view the data.

>I can allow Edits to existing records with no problem, but when I attempt
>an AddNew i always get error 3186, stating:

>Couldn't save; currently locked by user 'x' on machine 'y'.

>I am opening my databases like this:

>in general declarations:

> Global dbComfort As Database
> Global rstCustomer As Recordset

>in a function that opens my databases:

>    Set dbComfort = OpenDatabase(MAINDATABASENAME)
>    Set rstCustomer = dbComfort.OpenRecordset("customer", , dbOptimistic)

>the save routine is like this:

>        rstCustomer.AddNew
>        rstCustomer.Fields("custnum") = strNewCustNum
>    rstCustomer.Fields("custfirstname") = txtFirstName.Text
>    rstCustomer.Fields("custlastname") = txtLastName.Text
>    rstCustomer.Update

>The error always happens on the UPDATE.

>I am using VB5sp2 Professional under WIN95.

>I know DAO is not the best way to go about this, but I need to demonstrate
>it with DAO before I can convince them to spend the money on something
>better.  I can only use Access databases currently.

>Any ideas on what I am missing would be appreciated.



Sun, 27 Aug 2000 03:00:00 GMT  
 Multi-User DAO/JET AddNew doesn't work? Am I missing something?

Due to Jet's page locking, that is just how things will be. I have the same
situation, with 5-10 concurrent users. All can query and edit records, but
when they want to add a new record, 9 out of 10 times it is locked....



Quote:
> I am working on a Multiuser application, that would be used by about 5 -
10
> people at a time.  I have a Jet Database on a network and I can get
access
> to the Tables by more then one person at a time to view the data.

> I can allow Edits to existing records with no problem, but when I attempt
> an AddNew i always get error 3186, stating:

> Couldn't save; currently locked by user 'x' on machine 'y'.

> I am opening my databases like this:

> in general declarations:

>    Global dbComfort As Database
>    Global rstCustomer As Recordset

> in a function that opens my databases:

>            Set dbComfort = OpenDatabase(MAINDATABASENAME)
>            Set rstCustomer = dbComfort.OpenRecordset("customer", ,
dbOptimistic)

> the save routine is like this:

>            rstCustomer.AddNew
>            rstCustomer.Fields("custnum") = strNewCustNum
>            rstCustomer.Fields("custfirstname") = txtFirstName.Text
>            rstCustomer.Fields("custlastname") = txtLastName.Text
>            rstCustomer.Update

> The error always happens on the UPDATE.

> I am using VB5sp2 Professional under WIN95.

> I know DAO is not the best way to go about this, but I need to
demonstrate
> it with DAO before I can convince them to spend the money on something
> better.  I can only use Access databases currently.

> Any ideas on what I am missing would be appreciated.



Sun, 27 Aug 2000 03:00:00 GMT  
 Multi-User DAO/JET AddNew doesn't work? Am I missing something?

I had this exact problem.  I used the Visual Data Editor to create a version
2.0 MDB database. I used VisData to change it to a Version 7.0 MDB database
and the locking problems during add-new are now gone. Apparently a 7.0 MDB
(with DAO 3.5, which is what comes with VB 5) can switch to a different page
to add a new record if the page is locked by another user.

Since I did this we have not had any "locked by user x" errors, where before
we'd get them daily.

Rob

Quote:

>I am working on a Multiuser application, that would be used by about 5 - 10
>people at a time.  I have a Jet Database on a network and I can get access
>to the Tables by more then one person at a time to view the data.

>I can allow Edits to existing records with no problem, but when I attempt
>an AddNew i always get error 3186, stating:

>Couldn't save; currently locked by user 'x' on machine 'y'.

>I am opening my databases like this:

>in general declarations:

> Global dbComfort As Database
> Global rstCustomer As Recordset

>in a function that opens my databases:

>    Set dbComfort = OpenDatabase(MAINDATABASENAME)
>    Set rstCustomer = dbComfort.OpenRecordset("customer", , dbOptimistic)

>the save routine is like this:

>        rstCustomer.AddNew
>        rstCustomer.Fields("custnum") = strNewCustNum
>    rstCustomer.Fields("custfirstname") = txtFirstName.Text
>    rstCustomer.Fields("custlastname") = txtLastName.Text
>    rstCustomer.Update

>The error always happens on the UPDATE.

>I am using VB5sp2 Professional under WIN95.

>I know DAO is not the best way to go about this, but I need to demonstrate
>it with DAO before I can convince them to spend the money on something
>better.  I can only use Access databases currently.

>Any ideas on what I am missing would be appreciated.



Sun, 27 Aug 2000 03:00:00 GMT  
 Multi-User DAO/JET AddNew doesn't work? Am I missing something?

Greg,
WOW, I am new to Access, MS Jet and VB but have quite a lot of
experience in Paradox. I am amazed at this problem. How could any
commercial DB behave this way? If 9 out of 10 times I can not add a
new record to a table on a shared drive then I am out of business. So
the big question is what do you do to get around this? Is the previous
suggestion of creating an empty record set firs the way to go? Why
doesn't that have the same page locking problem? This is all very
serious, but I have to believe there is a way around it or else no one
could ever use Jet for multi-user apps.

TIA
John Lee



Quote:
>Due to Jet's page locking, that is just how things will be. I have the same
>situation, with 5-10 concurrent users. All can query and edit records, but
>when they want to add a new record, 9 out of 10 times it is locked....



>> I am working on a Multiuser application, that would be used by about 5 -
>10
>> people at a time.  I have a Jet Database on a network and I can get
>access
>> to the Tables by more then one person at a time to view the data.

>> I can allow Edits to existing records with no problem, but when I attempt
>> an AddNew i always get error 3186, stating:

>> Couldn't save; currently locked by user 'x' on machine 'y'.

>> I am opening my databases like this:

>> in general declarations:

>>        Global dbComfort As Database
>>        Global rstCustomer As Recordset

>> in a function that opens my databases:

>>        Set dbComfort = OpenDatabase(MAINDATABASENAME)
>>        Set rstCustomer = dbComfort.OpenRecordset("customer", ,
>dbOptimistic)

>> the save routine is like this:

>>                rstCustomer.AddNew
>>                rstCustomer.Fields("custnum") = strNewCustNum
>>        rstCustomer.Fields("custfirstname") = txtFirstName.Text
>>        rstCustomer.Fields("custlastname") = txtLastName.Text
>>        rstCustomer.Update

>> The error always happens on the UPDATE.

>> I am using VB5sp2 Professional under WIN95.

>> I know DAO is not the best way to go about this, but I need to
>demonstrate
>> it with DAO before I can convince them to spend the money on something
>> better.  I can only use Access databases currently.

>> Any ideas on what I am missing would be appreciated.



Sun, 27 Aug 2000 03:00:00 GMT  
 Multi-User DAO/JET AddNew doesn't work? Am I missing something?

The .AddNew is the biggest bottleneck in an Access 2.x environment and
yes, you will always get lock conflicts when multiple users are adding
records and all contending for the page at the end of the database. We
got around it by using pessimistic locking and performing the .AddNew
only when the user was ready to save. If the .AddNew generates an
error, retry multiple times at random time intervals. Note: Used
pessimistic locking as this is a demanding POS environment and adding
a record cannot ever fail. eg., if the .AddNew takes, then you are
guaranteed the new record will be saved. Had to play with the time
intervals and retry counts but found that retry intervals between 25
and 200 ms with 50 retries worked well for a 20 user system. No super
fast workstations (100 Mhz 486's, 10 MB Ethernet, Novell 3.12 server)

HTH

Quote:

>Due to Jet's page locking, that is just how things will be. I have the same
>situation, with 5-10 concurrent users. All can query and edit records, but
>when they want to add a new record, 9 out of 10 times it is locked....


>> I am working on a Multiuser application, that would be used by about 5 -
>10
>> people at a time.  I have a Jet Database on a network and I can get
>access
>> to the Tables by more then one person at a time to view the data.

>> I can allow Edits to existing records with no problem, but when I attempt
>> an AddNew i always get error 3186, stating:

>> Couldn't save; currently locked by user 'x' on machine 'y'.

>> I am opening my databases like this:

>> in general declarations:

>>        Global dbComfort As Database
>>        Global rstCustomer As Recordset

>> in a function that opens my databases:

>>        Set dbComfort = OpenDatabase(MAINDATABASENAME)
>>        Set rstCustomer = dbComfort.OpenRecordset("customer", ,
>dbOptimistic)

>> the save routine is like this:

>>                rstCustomer.AddNew
>>                rstCustomer.Fields("custnum") = strNewCustNum
>>        rstCustomer.Fields("custfirstname") = txtFirstName.Text
>>        rstCustomer.Fields("custlastname") = txtLastName.Text
>>        rstCustomer.Update

>> The error always happens on the UPDATE.

>> I am using VB5sp2 Professional under WIN95.

>> I know DAO is not the best way to go about this, but I need to
>demonstrate
>> it with DAO before I can convince them to spend the money on something
>> better.  I can only use Access databases currently.

>> Any ideas on what I am missing would be appreciated.



Sun, 27 Aug 2000 03:00:00 GMT  
 Multi-User DAO/JET AddNew doesn't work? Am I missing something?

To clarify, the .AddNew bombed whenever anyone was in the system....even
just viewing a record in unbound text fields, as the .AddNew attempts to
lock the entire database momentarily when adding the new record.  This was
with an Access 2.0 database.  Converting the database to an Access 7.0
format cleared up the problem.

Thanks to Rob Durfee for this solution!



Sun, 27 Aug 2000 03:00:00 GMT  
 Multi-User DAO/JET AddNew doesn't work? Am I missing something?

Friends,

And I thought I was just a bad programmer.  I've had this problem for weeks
now in a testing environment (VB 4.0-16 bit app), and our users are ticked.
Specifically, users are simply editing existing records to a db on a network
drive, and they are locked out.  Our company has no $ for Access 7.0, are
there any other suggestions/solutions/incantations for Access 2.0?  I
originally thought that network traffic and slow PC's were to blame.  But,
we are sporting brand new workstations (266 mghz with 64MB RAM, ethernet
connect, over Netware 4.11) and the problem still exists.



Tue, 29 Aug 2000 03:00:00 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. MultiUser Database DAO - 'Addnew'

2. MultiUser Database DAO - 'Addnew'

3. Databound Checkbox doesn't work BindingContext(...).AddNew

4. AddNew works, but then it doesn't (ADO)

5. Addnew works but doesn't...

6. AddNew works, but then it doesn't (ADO)

7. AddNew method doesn't work right!!

8. Addnew Doesn't Addnew!

9. Access Runtime - Am I Missing Something?

10. AM I missing something - Sum and Cumulative Sum

11. VBA For Dummies --- Am I missing something here?

12. Am I missing something?

 

 
Powered by phpBB® Forum Software