Alter schema of Access 2013 database with linked table

Question

I'm trying to change the schema of an Access 2013 table. I want to lengthen some of the text fields. When I change the schema and try to save I get dialog with Operation is not supported for this type of object, and a note that This property cannot be modified in linked tables. That makes sense. So I go to the relationships and try to delete one of the links. This gives me the error: You can't delete a relationship inherited from a linked database.

I don't know of any "linked databases" (I didn't create the database). Is there a way I can figure out what/where these might be?

Answer 1

Examine the TableDef properties for your linked table.

Here is an Immediate window session which examines properties for a linked table named remote_Foo:

' Connect identifies the database source of the linked table
? CurrentDb.TableDefs("remote_Foo").Connect
;DATABASE=C:\share\Access\BigDb_secure.mdb

' SourceTableName is the remote table name
? CurrentDb.TableDefs("remote_Foo").SourceTableName
tblFoo

You can use Ctrl+g to go to the Immediate window.

In Access 2010, that information is also available from the Linked Table Manager, which can be opened by right-clicking a link name in the Navigation pane and then choosing it from the shortcut menu. I don't know whether that option has changed in Access 2013.

You will need to make your design changes on the table in the remote database. Then recreate the link or use its RefreshLink method so that Access will recognize the design change and update the metadata it maintains for that link.

Answer 2

1. Your Table must be linked to Sharepoint site.

2. Go to "External Data" field and click "Work Offline" button (its a toggle button).

3. Make any schema changes.

4. Click again work online tab to sync changes to sharepoint.