| Author |
 Topic  |
|
|
bell1jl
USA
12 Posts |
 Posted - Mar 05 2009 : 08:33:10
|
| I have connected to the MLK database through ODBC and can read the DB just fine in a MS Access form. Looking at the table in a grid view, I can even add a record if needed. However, I am unable to update any user in the database. Access always gives me an error that another user has updated the record or something like that. It seems like a record locking issue but I can't figure out how or why that would be. I have completely closed MLK and I still get the message. I double checked the ODBC setup and I do not have read only access turned on. Do you have any suggestions on how I can use Access to update MLK records? |
|
|
xequte
7042 Posts |
Posted - Mar 05 2009 : 13:17:21
|
Hi
I'll need to have this one investigated.
What version of Access is it?
So it is when you post your changes after adding a new record that the error arises.
What is the exact text of the error?
Nigel
Xequte Software
www.xequte.com
nigel@xequte.com
|
 |
|
|
bell1jl
USA
12 Posts |
Posted - Mar 05 2009 : 14:21:00
|
I was afraid you might ask that. I'm not in the office again until next week Monday but I'll give you what I know. I'm using Access 2003 (I don't know if all updates have been applied).
I created a linked table to the MLK mlk_main table. If I double click the table and open it up, I can go to the bottom and add a new row just fine. However, if I go into an existing record and try to change anything, Access won't let me. It says something about another user having updated this record while I was in change mode and asks me to "copy to clipboard" or "abandon changes". I Googled what I remember and this is the error that comes up:
http://www.databasedev.co.uk/image/writeconfl.gif
The only difference is, the "Save Record" button isn't active so I only have two options. |
|
|
|
xequte
7042 Posts |
Posted - Mar 05 2009 : 14:38:02
|
Thanks, it appears that Access can't handle Boolean fields having NULL values (which is a major limitation for a database product).
Try downloading the database utility from http://www.xequte.com/download/dbsys330.exe
Then use SQL to set a FALSE value for all the Null Boolean fields (Validated, ExtraBool, ExtraBool2):
UPDATE MLK_MAIN Set Validated=False
Nigel
Xequte Software
www.xequte.com
nigel@xequte.com
|
|
|
|
bell1jl
USA
12 Posts |
Posted - Mar 05 2009 : 16:22:15
|
| Thanks. What suggestions do you have for keeping that up to date as I add records? |
|
|
|
xequte
7042 Posts |
Posted - Mar 05 2009 : 18:54:13
|
Hi
Our database model uses null values for some field states. Though if enough users encounter the issue we will revisit that.
You may need to use an automated SQL script to clear the null values.
Nigel
Xequte Software
www.xequte.com
nigel@xequte.com
|
|
|
|
bell1jl
USA
12 Posts |
Posted - Mar 05 2009 : 19:02:26
|
Yeah, that's what I was thinking too. Seems a little messy but maybe it won't be too bad. As far as storing a null boolean, you may want to revisit that anyway. A boolean, by nature, is either true or false -- nothing else. By throwing a null value in there, a boolean operator now becauses more than a binary flag because it has 3 possible values rather than 2.
With the particular fields in the question, it seems like the default value should be false.
Thanks for the help |
|
|
|
xequte
7042 Posts |
Posted - Mar 06 2009 : 00:03:46
|
Hi
A Boolean field in a database has three states so that one can determine whether it is True/False or has not been set. We use it for that purpose, e.g. to determine whether a user has passed/failed validation or is yet to be checked.
Nigel
Xequte Software
www.xequte.com
nigel@xequte.com
|
|
|
|
bell1jl
USA
12 Posts |
Posted - Mar 11 2009 : 17:35:01
|
| Are the three fields you mention in your post above the only null boolean fields to your knowledge? After I updated the table with FALSE values, Access still will not perform an update. |
|
|
|
xequte
7042 Posts |
Posted - Mar 12 2009 : 19:40:14
|
Hi
Yes, that should be all. Try creating a small test database with only a few members in it.
Nigel
Xequte Software
www.xequte.com
nigel@xequte.com
|
|
|
|
bell1jl
USA
12 Posts |
Posted - Jul 08 2009 : 15:25:38
|
Nigel, I have tried updating those fields with a value of FALSE but I still can't update the database from Access. I used the DB Tools to check the structure of the MLK_Main DB and noticed there were a few more Boolean fields. I updated those with FALSE as well - still I get the same error.
How can I update the MLK_MAIN DB from within Access? One of the reasons I chose MLK is because of the ODBC connectivity. See, I work for a non-profit group and we have converted our mail lists over to MLK and are using it to manage our mail lists. On top of that however, I have built some related Access tables to manage donation histories, communications, etc.. I currently don't allow editing of the records from within the Access form but it's becoming a pain to have to leave the Access application, find the record in MLK, update it, and go back to the Access application.
So, there's the reason I would like to update the table from within Access. Do you have any other ideas?
Thanks so much! |
|
|
|
xequte
7042 Posts |
|
| |
Topic |
|
Updating records through ODBC
