MSAccess to SQLServer 2000 via ODBC
-
Hi all
I have been asked to connect the tables of an existing Access 97 application to SQL 2000 so that the existing app remains in place but the underlying tables are SQL 2000. I have managed to create the link using ODBC, and data entry works fine but when trying to edit a record I get a "record locked, the table is being used by another user" type error. Does anybody know why this is happening and how I can overcome the problem?
Thanks in advance for any help.
cheers
Steve
-
I had same problem while ago. I found the solution. Check your table (Related to that form your haveing problem) in desing view and look for "bit" datatype field. Change the default value for all "bit" datatype field to (0). Then relink your table. It should work.
-
Thanks for the reply. I tried your solution but I'm still getting the same problem as before (Write Conflict - The record has been changed by another user since you started editing it....)
Steve
-
Is this Access 2000?
This sort of behaviour often is related to the indexes (within SQL-Server) on the table. If Access isn't sure which one to use for what then you can read data OK but get odd errors when you try to update.
When you linked your SQL-Server tables to Access were you given the option of choosing a "unique identifier"? If so then it might be worth dropping and re-linking the tables and choosing a different column (or group of columns) as the unique identifier.
Sam
-
Hi
Just re-read your original post and I saw that it was Access 97 you were talking about, so ignore the first question.
The rest of it still holds true though... in fact I believe that Access 97 is a lot less clever about these sort of things than Access 2000 is.
Sam
-
Hi, I once had the same problem. For me, the problem was related to a bit-field, as explained in this link:
http://www.ssw.com.au/ssw/kb/KB.aspx?KBID=Q482854. After changing the bit field(s), did you remember to update existing records?
Other users have experienced the same error, but attributed it to a lack of TIMESTAMP column. It was not helpful in my particular case: http://forums.devarticles.com/t9007/s.html
http://www.adminlife.com/247reference/msgs/1/6087.aspx
Never experienced this one, but still: http://www.kbalertz.com/kb_295225.aspx
-
After you correct the Bit datatype issue, delete all of the linked tables and link them again. Just relinking them does not refresh the information Access stores about the linked table, that is why you have to delete them.
Also, place the application on the local machines. Do not run it with mulitple users from a network location. Remember, Access works with page locking and not record locking. So, if the edited record shares a page with another record that is also being edited, you will get errors like this. Moving the application MDB/MDE local will stop this problem. It will also give you a bit of a speed boost since the one MDB/MDE does not have to contend with multiple users and the reduction of network traffic.
-
To all who have replied so far - thanks. I'm a bit busy with other projects at the moment, but as soon as I've tried your solutions to this teaser I'll let you know the outcome.
Gratefully
Steve Wilson
-
I've now been through the various suggestions: set zero as the default value for all bit fields, deleted existing links, relinked the tables etc... all to no avail, I'm afraid - still got the same problem.
Sam, the Indexes in the SQL tables are all set, and when I link the tables I don't receive a prompt to select an index - the linked tables show the same indexes as the SQL tables (sorry, I mean Primary Keys, there are no secondary indexes).
The SQL tables were constructed by importing the data from the original Access 97 tables, so I wonder if some of the SQL data types such as NVarChar, SmallDateTime ... might be causing a problem. I thought I would ask first, since there lots of tables and a huge number of NVarChar type fields, and altering them all would take an age!
I'm hoping that someone might save me here - if I can't solve this one soon I have to ditch the Access front end and rewrite the whole thing (a LOT of work) in Delphi!
-
Hi Stephen,
Sorry if this sounds obvious but but can you try creating a simple test table in SQL-Server, then linking it to MS Access and seeing if you can do any inserts into that. Keep simple datatypes and and make sure that there is an identity column that's the primary key.
If that works then you know in principle everything is set up correctly.
Very obvious things to double check.... try deleting and recreating the ODBC connection. Does the user that you are connecting with have full permissions on the tables? .. Is the access database on a local drive or a network share. If a network share then try it on your C:\ drive.
Have a look on the microsoft web site.. has there been an update to the ODBC drivers that you are using? There might be something in the knowledge base on Microsoft site that'll point you in the right direction.
It would possibly the NVarChars. Try changing these on just one table and see what happens.
Don't give up. There is technically no reason why this shouldn't work.
Hope that helps.
Sam
-
Unless you are using international character sets, keep it to varchar, text and char. Leave the N off of them. I have linked properly to tables the the N based datatypes, but it is a waste fo space if you are not using international character sets.
Follow Sam's advice and create a new small table with each of the "normal" datatypes. Link it and see if you can enter and change data. If you have access to it, use the sa account to do the linking the first time. Then delete the linked table and relink using your production user ID.
-
Sam, Robert, Dhaval, Leifah
Thanks to all of you for your help (and particular thanks to Sam for his patience and persistence). I managed to get it working in the end.
The problem was related to the bit fields of various tables - somehow, after following Dhaval's and Leifah's advice about setting the default to zero I managed to let a couple of existing null values escape me and it was these that were causing the problem. Once set to 0 everything works fine.
I assume the bit field problem has to do with the fact that SQL uses 0 and 1 where Access uses 0 and -1 values?
BTW, I take your point about the wastefulness of N types, Robert. I don't need them so I'll change the NVarChars to VarChars.
Thanks again
regards
Steve
-
In SQL Server, it is only "bi_logical" (Yes/no, 0/1). Access allows a "tri_logic", yes, no, null or -1, 0, null. By cleaning out the nuls, you have the bi-logic working as designed.
-
Stephen
No problem.... we've all been there and know what it's like to hit a brick wall. Glad my mutterings helped.
Sam
-
I had a problem with the Identity field update blocking a permitted user from editing after the increment was done by the SQL Server. I tried everything listed in this posting and others, following the timestamp and bit fields. My solution was My identity field in sql server was a bigint. When I changed it to int, then access behaved as expected. The timestamp solution had not affect. I hope this helps someone in the future.
Colin
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply