Recordset Update Error

J

Joe Wedel

Greetings!
I am getting the following error when I click the [Save]
button on my form:

Run-time error '-2147217887 (80040e21)':
The Microsoft Jet database engine stopped the process
because you and another user are attempting to change
the same data at the same time.

The code is below. The error occurs at the .Update line,
3rd from the bottom.

Dim rsD As New ADODB.Recordset 'Opens Recordset
Dim strSQL As String 'SQL Statement

'Open recordset
strSQL = "SELECT * FROM tblItems WHERE ItemID=" &
txtItemID & ";"
rsD.Open strSQL, CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

'Update fields
rsD("AgendaSec") = cmbSection
rsD("MeetID") = txtMeetID
rsD("UserName") = UserName
rsD("ItemNumber") = ItemNumber
rsD("Dept") = Dept
rsD("DeptContact") = DeptContact
rsD("CouncilAction") = CouncilAction
rsD("ActionShow") = ActionShow
rsD("ActionReq") = ActionReq
rsD("BackShow") = BackShow
rsD("Backgnd") = Backgnd
rsD("PrevShow") = PrevShow
rsD("PrevAction") = PrevAction
rsD("FundShow") = FundShow
rsD("Funding") = Funding
rsD("KeyShow") = KeyShow
rsD("KeyFeatures") = KeyFeatures
rsD("RecShow") = RecShow
rsD("Recommend") = Recommend
rsD.Update
rsD.Close
Set rsD = Nothing

This application is under development, so there are no
other users. All data is stored in a SQL Server database,
I have linked all tables through an ODBC System DSN. I
get a similar error message if I open the Table (in
Access) and attempt to change data directly in the table
view, when there are no forms or code running. Thanks for
the help!
 
C

chris

I got caught by the same error. Perhaps the reason is the
same.
Mine involved the design parameters of the SQL table.
I found that there were a mixture of "bit" type fields.
Some allowed nulls and some didn't. (It was by accident -
not design). I made them all the same, relinked the table
for good measure, and my problem went away.

I'll leave you to work out why, I haven't a clue
 
J

Joe

Thanks for the response, but it did not fix the problem.
I have several "bit" type fields, but all are the same.
All fields accept nulls, except the Identity. I relinked
the table anyway, but the error persists. Any other ideas
out there?
-Joe
-----Original Message-----
I got caught by the same error. Perhaps the reason is the
same.
Mine involved the design parameters of the SQL table.
I found that there were a mixture of "bit" type fields.
Some allowed nulls and some didn't. (It was by accident -
not design). I made them all the same, relinked the table
for good measure, and my problem went away.

I'll leave you to work out why, I haven't a clue
-----Original Message-----
Greetings!
I am getting the following error when I click the [Save]
button on my form:

Run-time error '-2147217887 (80040e21)':
The Microsoft Jet database engine stopped the process
because you and another user are attempting to change
the same data at the same time.

The code is below. The error occurs at the .Update line,
3rd from the bottom.

Dim rsD As New ADODB.Recordset 'Opens Recordset
Dim strSQL As String 'SQL Statement

'Open recordset
strSQL = "SELECT * FROM tblItems WHERE ItemID=" &
txtItemID & ";"
rsD.Open strSQL, CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

'Update fields
rsD("AgendaSec") = cmbSection
rsD("MeetID") = txtMeetID
rsD("UserName") = UserName
rsD("ItemNumber") = ItemNumber
rsD("Dept") = Dept
rsD("DeptContact") = DeptContact
rsD("CouncilAction") = CouncilAction
rsD("ActionShow") = ActionShow
rsD("ActionReq") = ActionReq
rsD("BackShow") = BackShow
rsD("Backgnd") = Backgnd
rsD("PrevShow") = PrevShow
rsD("PrevAction") = PrevAction
rsD("FundShow") = FundShow
rsD("Funding") = Funding
rsD("KeyShow") = KeyShow
rsD("KeyFeatures") = KeyFeatures
rsD("RecShow") = RecShow
rsD("Recommend") = Recommend
rsD.Update
rsD.Close
Set rsD = Nothing

This application is under development, so there are no
other users. All data is stored in a SQL Server database,
I have linked all tables through an ODBC System DSN. I
get a similar error message if I open the Table (in
Access) and attempt to change data directly in the table
view, when there are no forms or code running. Thanks for
the help!
.
Click to expand...
.
Click to expand...
 
J

Joe

Hey, Chris, my bad. Your solution actually Did fix the
problem - sort of. I had to put 0 in the Default Value
and replace all the <NULL>'s in the database and my
problem went away. Thanks for pointing me in the right
direction!
-Joe
-----Original Message-----
I got caught by the same error. Perhaps the reason is the
same.
Mine involved the design parameters of the SQL table.
I found that there were a mixture of "bit" type fields.
Some allowed nulls and some didn't. (It was by accident -
not design). I made them all the same, relinked the table
for good measure, and my problem went away.

I'll leave you to work out why, I haven't a clue
-----Original Message-----
Greetings!
I am getting the following error when I click the [Save]
button on my form:

Run-time error '-2147217887 (80040e21)':
The Microsoft Jet database engine stopped the process
because you and another user are attempting to change
the same data at the same time.

The code is below. The error occurs at the .Update line,
3rd from the bottom.

Dim rsD As New ADODB.Recordset 'Opens Recordset
Dim strSQL As String 'SQL Statement

'Open recordset
strSQL = "SELECT * FROM tblItems WHERE ItemID=" &
txtItemID & ";"
rsD.Open strSQL, CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

'Update fields
rsD("AgendaSec") = cmbSection
rsD("MeetID") = txtMeetID
rsD("UserName") = UserName
rsD("ItemNumber") = ItemNumber
rsD("Dept") = Dept
rsD("DeptContact") = DeptContact
rsD("CouncilAction") = CouncilAction
rsD("ActionShow") = ActionShow
rsD("ActionReq") = ActionReq
rsD("BackShow") = BackShow
rsD("Backgnd") = Backgnd
rsD("PrevShow") = PrevShow
rsD("PrevAction") = PrevAction
rsD("FundShow") = FundShow
rsD("Funding") = Funding
rsD("KeyShow") = KeyShow
rsD("KeyFeatures") = KeyFeatures
rsD("RecShow") = RecShow
rsD("Recommend") = Recommend
rsD.Update
rsD.Close
Set rsD = Nothing

This application is under development, so there are no
other users. All data is stored in a SQL Server database,
I have linked all tables through an ODBC System DSN. I
get a similar error message if I open the Table (in
Access) and attempt to change data directly in the table
view, when there are no forms or code running. Thanks for
the help!
.
Click to expand...
.
Click to expand...
 
Top