Solved

Control can't be edited; bound to replication system column

Posted on 2012-04-05
18
965 Views
Last Modified: 2012-04-13
I am working in Access 2010 (32 bit).
I have a simple table I set up to do some quick processing on tables in my database (.accdb). The name of the table is "tbls". It has one field in it: 'Name'. Yes, I know that is not a good 'name' for a field. It's actually the name of the field from the MSysObjects table which is where I extracted the table names from to begin with; but I digress.
Anyway, I opened this table in datasheet view and tried to change the value of the data in the Name field. The field is locked. On the bottom of my window is the message:

Control can't be edited; it's bound to replication system column 'Name'

I don't know what this means or what to do about it. Can someone help me?
0
Comment
Question by:shambalad
  • 11
  • 5
  • 2
18 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37813285
upload a copy of the db
0
 
LVL 7

Author Comment

by:shambalad
ID: 37813303
I'll see if I can isolate the table for upload. Db has proprietary information. I just got an emergency call from a client (networking issue) and am heading to his office. I'll be back in an hour or two.
0
 
LVL 75
ID: 37813328
Before anything else, make a copy to the Table (and/or db) then do a Compact & Repair.

mx
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 7

Author Comment

by:shambalad
ID: 37814066
Ran compact and repair: No change.
Created new database and imported table into it. It's still behaving the same way.
I've attached the db.
BTW: is anyone else having problems keying in the required description for attachments? I'm finding it to be one squirrely textbox. I can't see the contents of the textbox when I'm keying into it; basically I have to type blindly into it.
Locked-Table.accdb
0
 
LVL 7

Author Comment

by:shambalad
ID: 37814106
I've replicated creating a table with this behavior. Ran the following query in the 'Locked-Table.accdb' db:

SELECT Name, Owner, Type INTO tblMsysObjectsExtract FROM MSysObjects;

The fields in tblMsysObjectsExtract are locked and have the same message.
0
 
LVL 75
ID: 37814111
I created a new field (xName), saved, then pasted in the data ... and that can be edited.

Where did this db originate from ?  Was it at one time a replilcation db?

mx
0
 
LVL 75
ID: 37814112
Interesting that this field cannot be deleted either.  Possibly in could in code using ALTER >> DROP COLUMN.

mx
0
 
LVL 7

Author Comment

by:shambalad
ID: 37814125
This db was created from 'scratch', so to speak. I imported a table that I had created and loaded myself. Created a new table using a similar query as above. Fields are not locked. It's appearing to be related to the system tables.
0
 
LVL 7

Author Comment

by:shambalad
ID: 37814138
Created another table from another system table:

SELECT ACM, FInheritable, ObjectId, SID INTO tblMSysACEsExtract FROM MSysACEs;

Same error.
0
 
LVL 75
ID: 37814144
Yeah ... I missed your previous comment.

Can't delete than field in code either ... just tried.

Interesting the MakeTable does this ...

mx
0
 
LVL 7

Author Comment

by:shambalad
ID: 37814166
Opened tblMSysACEsExtract in Design mode. Did a Save As 'tblMSysACEsExtractSaveAS'.
Loaded tblMSysACEsExtractSaveAS with the following SQL:

INSERT INTO tblMSysACEsExtractSaveAS ( ACM, FInheritable, ObjectId, SID )
SELECT ACM, FInheritable, ObjectId, SID
FROM tblMSysACEsExtract;

Same error with tblMSysACEsExtractSaveAS.
0
 
LVL 7

Accepted Solution

by:
shambalad earned 0 total points
ID: 37814200
Created a new table name tblObjects. Loaded it with the following SQL:

INSERT INTO tblObjects ( Name, Type ) SELECT Name, Type FROM tblMsysObjectsExtract;

Fields in tblObjects are not locked.

It's appearing to me that Access doesn't want me to touch its system tables, even 'remotely'. Seems like new rules, but I can guess I can play within those parameters.
If I had some application that was premised on extracts from the MsysObjects table, though, I'd be worried.
0
 
LVL 7

Author Comment

by:shambalad
ID: 37814218
This isn't a new issue:

http://www.ureader.com/msg/1064728.aspx
0
 
LVL 75
ID: 37814240
"It's appearing to me that Access doesn't want me to touch its system tables,"

Pretty much. And there is no guarantee that the MSys tables won't change from version to version ... so, extracting data directly might be iffy, depending on the intended use.

mx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37814364
test this, get the names of table using the codes in module1 and store in table1
Locked-Table.accdb
0
 
LVL 7

Author Comment

by:shambalad
ID: 37821136
Thank you for the routine Cap.
I am using a variation on that theme:

Private Function GetTbls()
      Const strProcedure As String = "GetTbls"
      Dim varTbls() As Variant
      Dim tdf As DAO.TableDef
      Dim db As DAO.Database
      Dim i As Integer
10    On Error GoTo ErrorHandler

      ' Returns array of Tables
20    GetTbls = Null
30    Set db = CurrentDb
40    With db
50       For Each tdf In .TableDefs
60          With tdf
70             Select Case True
                    Case .Name Like "msys*"
80                Case .Name Like "~*"
90                Case .Name Like "tblDd*"
100               Case Else
110                  i = i + 1
120                  ReDim Preserve varTbls(1 To i)
130                  varTbls(i) = .Name
140            End Select
150         End With          'With tdf
160      Next tdf             'For Each tdf In .TableDefs
170   End With                'With db

180   GetTbls = varTbls
ExitFunction:
190   On Error Resume Next
200   Set db = Nothing
210   On Error GoTo 0
220   Exit Function

ErrorHandler:
230   Debug.Print strProcedure, Err, Err.Description, Erl
240   Resume ExitFunction
End Function
0
 
LVL 7

Author Comment

by:shambalad
ID: 37821148
Cap - I appreciate your help. Nonetheless, I hope you understand that I'm not accepting your comment as the answer since it does not address the central question which is why those fields are not updatable.
I have done a lot more research into this, and have come across some other questions about 'updatable' fields which I will be posting shortly.
Regards,
Todd
0
 
LVL 7

Author Closing Comment

by:shambalad
ID: 37841629
Fields derived from msys tables are not updateable.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question