Solved

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

Posted on 2012-04-05
18
951 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 119

Expert Comment

by:Rey Obrero
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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 119

Expert Comment

by:Rey Obrero
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

932 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now