Access not reading extended parameters

Question

We have an ADP Access 2010 project, linked to a MS SQL 2012 server.

I am trying to set up a table that has a combo box to select appropriate values, which are supplied by another table.

A table under the default dbo schema reads the extended properties, and shows the appropriate combo box
Combo Box Example

When I try to add the same extended properties to a table that is under another schema, the combo box does not appear.

What do I need to do to solve for this, or am I resigned to giving up with the task, and deal with it later when we drop the ADP?

(I tried asking this on SuperUser, but got no response)

Access code that opens table that works:

Private Sub cmdAvayaQ_Click()
DoCmd.OpenTable "dbo.tbl_o_etc_Avaya_Skills", acViewNormal, acEdit
End Sub

Access code that opens table that doesn't work

Private Sub cmdNonPhone_Click()
DoCmd.OpenTable "forecasting.tbl_nonphone_queues", acViewNormal, acEdit
End Sub

• Aaron Bertrand: What permissions are set for this user against the other schema? Are they the same as for dbo (other than only one can be the default)?
• SeanC: The user is owner of the second schema. dbo is the users default schema
• Aaron Bertrand: And does your ADP explicitly reference second_schema.table_name, or just table_name?
• SeanC: The extended properties are for setting up a combo box dropdown for the column. It works fine within the dbo tables, but not within the other tables. When Access attempts to set the properties, it states I do not have permission, but I can add them from SSMS (I even had them added by the sa to see if that was the issue). I have a feeling it has something to do with the way access accesses the table, but as this is the only experience I have had with extended properties, I wanted to make sure I was doing nothing wrong server side
• Aaron Bertrand: But again, are you pointing Access at some_table or some_schema.some_table? Can you capture what Access is trying to do with a server-side trace? Sorry, I haven't used Access in literally 15 years, so I have no idea if there is a trick to do what you want to do or if it simply isn't possible (there's a chance it is still coded to assume that everything on earth is in the dbo schema).

Answer 1

I don't use Access, but it is likely that it is unable to query extended properties except those under the dbo schema - it must make the assumption that everything is under dbo. I don't really have a solution for you other than not using schemas with your ADP if extended properties are critical.