| Author |
Topic |
|
mharvey
Starting Member
13 Posts |
 Posted - 2003-10-06 : 14:22:23
|
| Hello All,We are trying to upgrade several ACCESS databases to use a SQL Server back-end. When I INSERT records into SQL tables from either an ACCESS form or VBA/ADO code, the records are inserted correctly but, when I try to edit those records just inserted, I receive a "Write Conflict" dialog box containing the following text: "This record has been changed by another user since you started editing it. If you save the record you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user made, and then paste your changes back in, if you decide to make changes." There are three buttons on this dialog box: > Save Record (not enabled)> Copy to clipboard (enabled)> Drop changes (enabled)Therefore, I cannot edit/re-save any editing changes. I KNOW, I'm the only one adding records to these tables via these interfaces because I'm the only developer working in the ACCESS front-end and the back-end SQL database.I should also add that, I can insert records into the SQL tables directly from table datasheet view with NO problems; only from an ACCESS form or from ADO code does this problem occur. What gives?ANY SUGGESTIONS? Are there other connection properties I need to be aware of? Is this an uncommitted transaction issue? If so, what do I need to do?HELP!Thanks, Michael |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-06 : 14:26:54
|
quote:
Originally posted by mharvey
When I INSERT records into SQL tables from either an ACCESS form or VBA/ADO code
Well which one is it?Kinda hard to test when you've got multiple things going on...And are you sure that it's writing to SQL Server?Sounds like an Access error to me...Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
 |
|
|
mharvey
Starting Member
13 Posts |
Posted - 2003-10-06 : 14:29:48
|
| It's both, there are only two conditions, as I stated in my e-mail. As I also said, it works fine when I insert records from datasheet view but, generates the dialog when I insert records from EITHER a form OR ADO code. Thanks,Michael |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-06 : 14:35:46
|
| I guess my question is...if you're doing both, how do you know it's not you who's bloclking you?And are you sure it's a sql server table?I would suggest you close everything, and then give it a shot...It should be realtivelt straigt forward.How do you Access the SQL Server table btw?Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
mharvey
Starting Member
13 Posts |
Posted - 2003-10-06 : 14:48:06
|
| First - thanks for taking the time to give me some ideas and help. I understand your question - I'm not doing both AT THE SAME TIME. I'm certain I'm updating the SQL tables for a few reasons: 1) All ACCESS tables were removed, only links to SQL 2000 back-end.2) IN the case of entry/edit forms, I've reset the record source strings, updated some fields via SQL Server, then viewed the results on the ACCESS form; and changed data (in existing records ) right on the form - all updates took, no problems.3) In the ADO code, I'm using explicit connection strings to the back-end SQL Server server.4) As I said, the INSERTS took, I just can't go back into the recently inserted rows and edit the records created. 5) I've reviewed results both in ACCESS and SQL Server - I'm certain the updates and inserts took in the SQL tables.I can't help but think that there is some parameter on the connection string or on the forms that I need to adjust for this but, I'm at a loss as to what it is. Thanks, Michael |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-06 : 14:51:58
|
| Are you doing a BEGIN TRANSACTION and not a COMMIT?Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-06 : 15:02:07
|
| when you movenext in a rs, doesn't it commit? Or is it on close of the rs?I would think it's got to be commit per row....Are you leaving the connection open?Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
mharvey
Starting Member
13 Posts |
Posted - 2003-10-06 : 15:08:33
|
| Tara and Brett: In the ADO code I'm doing both the BEGIN and COMMIT transactions. I'm not doing a rs.MoveNext in this case because I'm only writing (INSERT) to the record set objects.The connection objects are closed and set to NOTHING in all cases. This is why I'm so stumped - this exact code (with the exception of the connection object which now points to SQL Server), has been working in production for MONTHS!!!!!Michael |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-06 : 15:10:54
|
| If you run SQL Profiler, you'll see what commands are running on your server. It will show which query is causing the problem. There probably is an extra BEGIN TRAN with no corresponding COMMIT. SQL Profiler will show this if that is the case. It will show everything as long as the trace is setup for your needs.Tara |
|
|
|
|
Write Conflicts -