We will look at each of these situations in this tip.
Let's take a look at both of those situations one step at a time.
You must also unlink and relink any table if your user ID or your password changes.
What happens if you skipped assigning a primary key in Access and now wish to do so?
The process is identical to what I just demonstrated in regards to DDL changes to the SQL Server table.
When you attempt to modify the design of a linked table in Access you receive the following message: This is because the design of the linked table is owned by the underlying object within SQL Server.
Although the user may have been granted db_owner role rights in the underlying SQL Server database, that user is unable to make data definition changes (DDL changes) to the table.
The current schema for the table looks like this from a SQL Server perspective: The design is reflected in Access as: We will now add two columns to the SQL Server table; Region Supervisor ID (int, null) and Territory Supervisor ID (int, null) as shown below: After the changes, the Access table will still appear as it did prior to the DDL changes: In order to recognized the structural changes in the underlying SQL table you will need to make use of the Linked Table Manager within Microsoft Access.
To launch, select inked Table Manager from the menu bar in Access.
In a previous tip we saw how easy it was to link to SQL Server tables from Microsoft Access.
As is the case with all systems, how does Access manage the changes?
You can make some changes to the definitions of linked tables to customize them for use in your Access 2010 environment.