access to ms sql server (record lock problem)

D

dva224

Guppy
hi,

I wanted to convert tables from a access database to a ms sql database, every thing went well (transferring data, remake the auto numbering, and set the table key's)
next step was to relink the tables via odbc in the old access database (this also went well) but now i was testing it and there apears to be a few errors:
1) when I tried to make a new record, access made a new record but immediatly emptied the data I entered, by reentering the data every thing seemd to go well
2) I can update all exsisting records, I can enter new data, but I can not update new added data in acces olso not after rebooting
it looks like it is locking al new entered data after saving, when I try to update data I get the message cannot update record is locked by another user, drop changes? or coppy to clipboard?

I also have some other databases running in ms sql 2000 that do not have these problems I tried to import a test table from the old acces database to a database in sql that did not have that problem , to see if the problem was database related or table related, but the outcome of this test was the same so according to this test it seemed to be table related

I hope someone is familliar whit this problem and has solved it
many thanks in advance

P.S. sorry for my poor english
 
I would recommend just using Enterprise Manager and getting a similar inferface, but one that works well.
 
thanks fore replying

my whole aplication is written in access, but the sql engine in access is not stable enough so I decided to export the tables to ms sql 2000, in order to youse the access aplication I have linked the tables in access via odbc from ms sql 2000 I did it once befor and it worked perfect, but now for a this database it somehow doesnt work I can update all imported data but i cant update any new added data while i am logged on as dbowner
this is a real problem for me i realy hope someone can help me

hope this explains the problem better
 
I had this same problem and it turned out to be something obscure.

See Microsoft Article Q280730.
You can't have NULL bit fields. Do you have any?
If so, you must specify the default value as =0. You must also change any existing records -- if NULL, change the value to 0.
 
Hi

Did you solve this issue?

I have exactly the same problem, after transfering the database to MS SQL server new posts can't be edited.

The error message says that the post is edited by another user.
 
I'm not sure that we are talking about the same issue, but the problem I had was that I converted an Access database to a SQL Server database. Then I opened Access and linked the tables, but one table was not editable. The error message had to do with record locking, which was very confusing.

I tried to find the Microsoft article I mentioned earlier but it isn't there anymore. But this is how to tell if it is the same problem. If you have any Yes/No fields in Access, those fields become BIT fields in SQL Server, but in SQL Server, those BIT fields can't be NULL. If you have existing data, you need to go in and change all of the NULL values to 0. Then you need to modify the table design and set the default value to 0 -- this means that any new records will have a default value of 0.

Then relink the table in Access and magically, the table will now be editable.

If you don't have any BIT fields in your table, then we have different issues and you'll have to try something else.
 
You must log in or register to reply here.
Back
Top