![]() ![]() Here is a sample screen shot to show the message from Microsoft Access: When you delete the Linked Server, you're merely removing the link, not deleting the server instance. It is similar in behavior to Linked Servers in Microsoft SQL Server. Deleting the table in Access only removes that linked table. Just as the user, via Microsoft Access, has no rights to make DDL changes to the underlying SQL Server database (no matter what rights have been granted to that user in the SQL Server database) they are also unable to delete the table directly in the Microsoft Access application. ![]() Stating it simply, deleting a linked table in Microsoft Access has absolutely no effect on the underlying SQL database. Thanks for asking it! We appreciate the community feedback. If we look at the SubCategories table in Access we'll see that the primary key has now been set. Once done I will launch the Linked Table Manager and follow the same process we used for our Territories table example. PK_SubCategories PRIMARY KEY CLUSTERED ( SubCategoryID ) I'll now switch back to SQL Server and add a primary key by running the following T-SQL command.ĪLTER TABLE dbo.SubCategories ADD CONSTRAINT I'll now link to the SubCategories table in Access and not assign a Unique Record Identifier at this time (since there is no primary key defined in the SQL Server table), using the techniques demonstrated in the initial tip on Linked SQL Server tables in Microsoft Access. SubCategoryName NVARCHAR ( 15 ) NOT NULL, SubCategoryID INT IDENTITY ( 1, 1 ) NOT NULL, ![]() The table will be a heap (no index) when created initially using the following script. Let's take a look at the process using a table that I am going to create in the Northwind database on my SQL Server instance called SubCategories. 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. asked in the forums of the Linked SQL Server Tables tip about skipping the assignment of a primary key during the initial linking. Now, if we are to look at the linked table in Design Mode within Access you will see the two new columns we added in SQL Server. Then cancel out of the Linked Table Manager to return to the main Access interface. ![]() You will receive a notification that all tables were successfully updated. Once you select the table(s) you wish to update select OK. To launch, select Tools\ Databases Utilities\ Linked Table Manager from the menu bar in Access. 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. We will now add two columns to the SQL Server table RegionSupervisorID (int, null) and TerritorySupervisorID (int, null) as shown below:Īfter the changes, the Access table will still appear as it did prior to the DDL changes: The current schema for the table looks like this from a SQL Server perspective: We will use the Territories table in the Northwind database as an example. If you need to make changes to the table you will need to do so in Microsoft SQL Server and then re-link the table. 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. This is because the design of the linked table is owned by the underlying object within SQL Server. When you attempt to modify the design of a linked table in Access you receive the following message: This is accomplished by navigating to the Tables tab, selecting the appropriate table and right clicking on the 'Design View' option on the menu. Let's start with modifying a Linked SQL Server table's structure. Let's take a look at both of those situations one step at a time. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |