|
|
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
|
|
"Brian" <Brian[ at ]discussions.microsoft.com> wrote in message
news:2CD0CBE9-5834-4F51-B2C5-B5A8C82CA8F2[ at ]microsoft.com...
[Quoted Text] > 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)
|
|
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.
"Dirk Goldgar" wrote:
[Quoted Text] > "Brian" <Brian[ at ]discussions.microsoft.com> wrote in message
> news:2CD0CBE9-5834-4F51-B2C5-B5A8C82CA8F2[ at ]microsoft.com...
> > 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)
>
>
|
|
"Brian" <Brian[ at ]discussions.microsoft.com> wrote in message
news:3EA23FA5-B290-4A0C-BA60-D54F2D2A2D0C[ at ]microsoft.com...
[Quoted Text] > 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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
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" wrote:
[Quoted Text] > "Brian" <Brian[ at ]discussions.microsoft.com> wrote in message
> news:3EA23FA5-B290-4A0C-BA60-D54F2D2A2D0C[ at ]microsoft.com...
> > 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
|
|
"Brian" <Brian[ at ]discussions.microsoft.com> wrote in message
news:315582E4-5A45-4F2F-BB70-9C2529628A28[ at ]microsoft.com...
[Quoted Text] >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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
On Dec 17, 9:59 am, "Dirk Goldgar"
<d...[ at ]NOdataSPAMgnostics.com.invalid> wrote:
[Quoted Text] > "Brian" <Br...[ at ]discussions.microsoft.com> wrote in message
>
> news:315582E4-5A45-4F2F-BB70-9C2529628A28[ at ]microsoft.com...
>
>
>
> >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.
>
> --
> Dirk Goldgar, MS Access MVPwww.datagnostics.com
>
> (please reply to the newsgroup)
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?
|
|
<benodan[ at ]gmail.com> wrote in message
news:9900d09e-aa66-451b-9c5a-0274cbf27012[ at ]w24g2000prd.googlegroups.com...
[Quoted Text] > 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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
On Dec 30, 7:45 pm, "Dirk Goldgar"
<d...[ at ]NOdataSPAMgnostics.com.invalid> wrote:
[Quoted Text] > <beno...[ at ]gmail.com> wrote in message
>
> news:9900d09e-aa66-451b-9c5a-0274cbf27012[ at ]w24g2000prd.googlegroups.com...
>
> > 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.
>
> --
> Dirk Goldgar, MS Access MVPwww.datagnostics.com
>
> (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!
|
|
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 ...
Dani wrote:
[Quoted Text] >On Dec 30, 7:45Â pm, "Dirk Goldgar"
><d...[ at ]NOdataSPAMgnostics.com.invalid> wrote:
>> <beno...[ at ]gmail.com> wrote in message
>>
>[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!
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200812/1
|
|
On Dec 31, 10:24 am, "tkelley via AccessMonster.com" <u47368[ at ]uwe>
wrote:
[Quoted Text] > 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 ... > > > > Dani wrote: > >On Dec 30, 7:45 pm, "Dirk Goldgar" > ><d...[ at ]NOdataSPAMgnostics.com.invalid> wrote: > >> <beno...[ at ]gmail.com> wrote in message > > >[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! > > -- > Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200812/1
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!
|
|
|