Run-time error '3197'

[Brian]

For better or worse, my forms are not directly linked to underlying tables.
Instead, I copy the data from the table to the form where it can be modified
by the user. Then I copy the now modified data back to the table using VBA
and .edit and .update.

I am in the process of creating a new table and database. Whenever I try to
save the data back to the table, it works for about 3-4 times, and then I get
the following error:

Run-time error '3197'
The Microsoft Access database engine stopped the process
because you and another user are attempting to change the same
data at the same time.

When I click the 'Help' button, it tells me:
-------------------------------------------------
This error can occur in a multiuser environment.

Another user has changed the data you are trying to update. This error can
occur when multiple users open a table or create a Recordset and use
optimistic locking. Between the time you used the Edit method and the Update
method, another user changed the same data.

To overwrite the other user's changes with your own, execute the Update
method again.
-------------------------------------------------

The problem is that since the database is in development, there are no other
users. Furthermore, the only thing that is occurring between the Edit and
Update methods are 12 lines of :

rst![rel_EdCess] = Forms![frmPurchaseOrderHeader-DataEntry]![rel_EdCess]

each line with different fields of course.

If anyone can point me in the right direction, I would sure appreciate it!

Thanks

 

[Dirk Goldgar]

For better or worse, my forms are not directly linked to underlying
tables.
Instead, I copy the data from the table to the form where it can be
modified
by the user. Then I copy the now modified data back to the table using
VBA
and .edit and .update.

I am in the process of creating a new table and database. Whenever I try
to
save the data back to the table, it works for about 3-4 times, and then I
get
the following error:

Run-time error '3197'
The Microsoft Access database engine stopped the process
because you and another user are attempting to change the same
data at the same time.

When I click the 'Help' button, it tells me:
-------------------------------------------------
This error can occur in a multiuser environment.

Another user has changed the data you are trying to update. This error can
occur when multiple users open a table or create a Recordset and use
optimistic locking. Between the time you used the Edit method and the
Update
method, another user changed the same data.

To overwrite the other user's changes with your own, execute the Update
method again.
-------------------------------------------------

The problem is that since the database is in development, there are no
other
users. Furthermore, the only thing that is occurring between the Edit and
Update methods are 12 lines of :

rst![rel_EdCess] = Forms![frmPurchaseOrderHeader-DataEntry]![rel_EdCess]

each line with different fields of course.

If anyone can point me in the right direction, I would sure appreciate it!

How local is your recordset usage? When you load your form, do you open,
read, and close the recordset, or do you leave the recordset open? When you
unload the form, do you open, update, and close the recordset?

My best guess is that there is a recordset that isn't being closed.

 

[Brian]

An open recordset was my first guess as well, but I followed the whole
process through the error checking program and then the save program. Each
program is properly ended with a rst.close followed by a set rst=nothing
statements.

I should point out that in my development database, I have exactly one
record. If I open it, I can save it. But if I open it again, I get the
error. From there, I'm stuck and so far, the only way I've been able to fix
the problem is to delete the record and start over.

For better or worse, my forms are not directly linked to underlying
tables.
Instead, I copy the data from the table to the form where it can be
modified
by the user. Then I copy the now modified data back to the table using
VBA
and .edit and .update.

I am in the process of creating a new table and database. Whenever I try
to
save the data back to the table, it works for about 3-4 times, and then I
get
the following error:

Run-time error '3197'
The Microsoft Access database engine stopped the process
because you and another user are attempting to change the same
data at the same time.

When I click the 'Help' button, it tells me:
-------------------------------------------------
This error can occur in a multiuser environment.

Another user has changed the data you are trying to update. This error can
occur when multiple users open a table or create a Recordset and use
optimistic locking. Between the time you used the Edit method and the
Update
method, another user changed the same data.

To overwrite the other user's changes with your own, execute the Update
method again.
-------------------------------------------------

The problem is that since the database is in development, there are no
other
users. Furthermore, the only thing that is occurring between the Edit and
Update methods are 12 lines of :

rst![rel_EdCess] = Forms![frmPurchaseOrderHeader-DataEntry]![rel_EdCess]

each line with different fields of course.

If anyone can point me in the right direction, I would sure appreciate it!

How local is your recordset usage? When you load your form, do you open,
read, and close the recordset, or do you leave the recordset open? When you
unload the form, do you open, update, and close the recordset?

My best guess is that there is a recordset that isn't being closed.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

An open recordset was my first guess as well, but I followed the whole
process through the error checking program and then the save program.
Each
program is properly ended with a rst.close followed by a set rst=nothing
statements.

I should point out that in my development database, I have exactly one
record. If I open it, I can save it. But if I open it again, I get the
error. From there, I'm stuck and so far, the only way I've been able to
fix
the problem is to delete the record and start over.

Are you sure the forms involved are all unbound?

Does this happen on a form that doesn't open any other forms and reports,
and has no subforms? If so, could you post the complete code behind that
form? That may narrow the focus enough for someone to spot the problem.

 

[Brian]

I did some more research and found that there is one field in my form that is
causing the problem. When the field is populated, I get the error...when it
has no data, no error.

In SQL Server, there is a field called str_Notes which has a data type of
nvarchar(MAX). When the table is linked to Access, the field has a data type
of Memo. The VBA code to save data to this field in the table is:

rst.edit
....some VBA code here to save data in other fields, same format as below
rst![str_Notes] = Forms![frmPurchaseOrderHeader-DataEntry]![str_Notes]
rst.update

When I change the data type in SQL Server to be nvarchar(255), everything
works. The problem is that a user can easily exceed 255 characters in this
field.

If you have any ideas, please let me know.

 

[Dirk Goldgar]

I did some more research and found that there is one field in my form that
is
causing the problem. When the field is populated, I get the error...when
it
has no data, no error.

In SQL Server, there is a field called str_Notes which has a data type of
nvarchar(MAX). When the table is linked to Access, the field has a data
type
of Memo. The VBA code to save data to this field in the table is:

rst.edit
....some VBA code here to save data in other fields, same format as
below
rst![str_Notes] = Forms![frmPurchaseOrderHeader-DataEntry]![str_Notes]
rst.update

When I change the data type in SQL Server to be nvarchar(255), everything
works. The problem is that a user can easily exceed 255 characters in
this
field.

If you have any ideas, please let me know.

It's a new problem to me, but I've done some research and found that the
NVARCHAR(MAX) data type, new in SQL Server 2005, is not well supported by
Access. Can you change the field type to NTEXT? My reading sugggests that
should solve your problem.

 

[benodan]

I did some more research and found that there is one field in my form that
is
causing the problem.  When the field is populated, I get the error...when
it
has no data, no error.

In SQL Server, there is a field called str_Notes which has a data type of
nvarchar(MAX).  When the table is linked to Access, the field has a data
type
of Memo.  The VBA code to save data to this field in the table is:

rst.edit
   ....some VBA code here to save data in other fields, same formatas
below
   rst![str_Notes] = Forms![frmPurchaseOrderHeader-DataEntry]![str_Notes]
rst.update

When I change the data type in SQL Server to be nvarchar(255), everything
works.  The problem is that a user can easily exceed 255 characters in
this
field.

If you have any ideas, please let me know.

It's a new problem to me, but I've done some research and found that the
NVARCHAR(MAX) data type, new in SQL Server 2005, is not well supported by
Access.  Can you change the field type to NTEXT?  My reading sugggests that
should solve your problem.

I am having the same exact issue as Brian, and changing those fields
to ntext didn't help. It will only insert the data if in SQL Server it
is nvarchar(255) - but like Brian, I need it to be bigger than the
255. If I make it any bigger in SQL Server, Access will show it as
"Memo", and I have the same issues. Does anyone have a workaround or
any more ideas?

 

[Dirk Goldgar]

I am having the same exact issue as Brian, and changing those fields
to ntext didn't help. It will only insert the data if in SQL Server it
is nvarchar(255) - but like Brian, I need it to be bigger than the
255. If I make it any bigger in SQL Server, Access will show it as
"Memo", and I have the same issues. Does anyone have a workaround or
any more ideas?

After you changed the field type to NTEXT, did you relink the table in
Access? If you did, then I'm stumped, too, and will have to do some
experimentation to see if I can figure out what's up.

 

[Dani]

After you changed the field type to NTEXT, did you relink the table in
Access?  If you did, then I'm stumped, too, and will have to do some
experimentation to see if I can figure out what's up.

Thanks for the quick response. Yes, I relinked the table, and went and
changed those fields back to ntext and relinked again, just to verify.
Everything will run smoothly until a certain record, and then for each
subsequent function that updates a memo field, will break on the same
record. (Fields in between that aren't memo/ntext for the same record
work as expected.) Removing the record doesn't change the situation,
as it breaks at a different record down the line. I've been searching
for three days as to what the issue is, and I'm pretty stumped at this
point. I just wish there was a bigger limit for Text in Access - 255
seems archaic! Again, any ideas or light you can shed on the issue
would be greatly appreciated!

 

[tkelley via AccessMonster.com]

Okay ... I'm late to the fray. So this might be silly if out of context.

Are you opening a recordset on the linked tables? If so, to eliminate the
pain, can you just open the recordset using T-SQL and deal directly with the
backend? Or maybe a sproc or pass-through. I've passed over a 1000
characters to fields that way from an Access frontend using ADO. Also, I
just usually set my field to nvarchar(1000) or something like that. Some of
the gurus may be able to tell me why that's bad.

Just a couple of thoughts ...

[quoted text clipped - 15 lines]

(please reply to the newsgroup)

Thanks for the quick response. Yes, I relinked the table, and went and
changed those fields back to ntext and relinked again, just to verify.
Everything will run smoothly until a certain record, and then for each
subsequent function that updates a memo field, will break on the same
record. (Fields in between that aren't memo/ntext for the same record
work as expected.) Removing the record doesn't change the situation,
as it breaks at a different record down the line. I've been searching
for three days as to what the issue is, and I'm pretty stumped at this
point. I just wish there was a bigger limit for Text in Access - 255
seems archaic! Again, any ideas or light you can shed on the issue
would be greatly appreciated!

 

[Dani]

Okay ... I'm late to the fray.  So this might be silly if out of context.  

Are you opening a recordset on the linked tables?  If so, to eliminate the
pain, can you just open the recordset using T-SQL and deal directly with the
backend?  Or maybe a sproc or pass-through.  I've passed over a 1000
characters to fields that way from an Access frontend using ADO.  Also,I
just usually set my field to nvarchar(1000) or something like that.  Some of
the gurus may be able to tell me why that's bad.

Just a couple of thoughts ...

[quoted text clipped - 15 lines]

(please reply to the newsgroup)

Thanks for the quick response. Yes, I relinked the table, and went and
changed those fields back to ntext and relinked again, just to verify.
Everything will run smoothly until a certain record, and then for each
subsequent function that updates a memo field, will break on the same
record. (Fields in between that aren't memo/ntext for the same record
work as expected.) Removing the record doesn't change the situation,
as it breaks at a different record down the line. I've been searching
for three days as to what the issue is, and I'm pretty stumped at this
point. I just wish there was a bigger limit for Text in Access - 255
seems archaic! Again, any ideas or light you can shed on the issue
would be greatly appreciated!

Hi, thanks for the suggestion. Right now I'm using DAO to open and
manipulate the recordsets. While we may change this to use ADO in the
future, right now I'm really looking for a "quick and dirty" way to do
this. It's a top priority and I really need to get it working ASAP. We
were using Access as the back end, but our table has gotten too large,
and even after c&r, goes over 2GB after being populated - so this need
to move it over to SQL Server is somewhat urgent. I think right now I
would have to do more research on how to change all the recordsets
over to ADO - which is why I'm trying to go this route first - and not
have to rewrite a (pretty significant) amount of code. I will keep
your suggestion in mind, though!