Problems saving record to a SQL Server via VBA

[Brian]

Background: MS Access 2007 front-end; SQL Server 2005 back-end. Forms in
Access are unbound. Through VBA routines, I find the record in the SQL
database, copy the data to the form. When the user clicks the Save button, I
again find the record in the SQL database and copy the data from the form to
the database using rst.edit and rst.update commands.

I have one form that gives me a constant error when saving a record. The
error is that Access gives is:
---------------------------------------------------------------------------
Run-time error '3197':
The Microsoft Office Access database engine stopped the process
because you and another user are attempting to change the same
data at the same time.
---------------------------------------------------------------------------
First and foremost, this database is in development, so there isn't anyone
else using it. And I only have one instance opened, so the actual error
description itself is bogus.

But through some trial and error, I found that if my str_Notes field is set
to a string data type size of greater than 255 characters, I get this error.
I have tried SQL data types of nvarchar(MAX), nchar(4000), text,
varchar(MAX), and char(8000). If anything inside the () is greater than 255,
I get this error. Basically if Access translates the SQL data type into a
Memo data type, I get this error. But everything works is the size is 255 or
less.

I checked to make sure that my Open and Update VBA programs all had the
appropriate rst.close and set rst=nothing statements and they do. Plus of
course, everything works when the notes field is limited to 255 characters.

I should also point out that I have other forms with a notes field whose
data type in SQL is nvarchar(MAX) and I don't have any problems with saving.

So at this point in time, I am wits end and quickly going bald. If anyone
has any ideas, please let me know.

Thanks

 

[Jeff Boyce]

Brian

This may not be related, but I recall an issue with records in SQL-Server
when the table did not have a field of type Timestamp.

Note that this is NOT a date/time field, but rather something of an
'internal' field SQL-Server uses to avoid record conflicts. All you'd need
to do (if this is the cause) is add a field to the SQL-Server table of data
type Timestamp.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brian]

You know Jeff, I read your response and thought "this guy's gotta be
kidding". But at this point in time, I'd try standing on my head whistling
Dixie if I thought it would work. So I added a field called ts_TimeStamp,
didn't reference it in my programming at all, and the damn thing worked!!!
Totally mind-boggling because I've never heard of this and all of my other
tables work without a timestamp field.

So to be on the safe side, do you think it's worth it to add to all of my
tables?

 

[Jeff Boyce]

Brian

Yeah, I know, I thought the person I read it from was crazed, but it worked
for me, too. Something about how SQL-Server keeps track of which record has
been "checked-out". Without the TS (timestamp) field, I guess it loses its
mind ?!?

If you have copious free time and are excessively anal, sure, go ahead, add
that TS field to each table in SQL-Server. I did...<g>

(and you may find other references to this issue here in the newsgroups --
some folks swear by it, some swear at it -- go figure!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rick Brandt]

You know Jeff, I read your response and thought "this guy's gotta be
kidding". But at this point in time, I'd try standing on my head
whistling Dixie if I thought it would work. So I added a field called
ts_TimeStamp, didn't reference it in my programming at all, and the damn
thing worked!!! Totally mind-boggling because I've never heard of this
and all of my other tables work without a timestamp field.

So to be on the safe side, do you think it's worth it to add to all of
my tables?

Search these groups. It's a common problem and what you suggest is what
many people do.