Access updating linked tables
For the purpose of this tip however, we're going to focus exclusively on Microsoft SQL Server.
The process from this point forward is compatible with all releases of SQL Server since version 7.0.
Though Access can be considered a sub-par application by the relational database elitists, it can be a very capable alternative for querying data from SQL Server without the learning curve associated with Visual Studio.
In some respects, it is the perfect tool for the casual business user who wants to drill into and share their data. In order to connect to Microsoft SQL Server from Microsoft Access you need to set up at least one Linked Table.
However, when I go to view my form of the information from the table the form does not show the same amount of records as the table or any updates that were made to the table.
When I checked the Linked Table Manager I found that the table used to populate the form was not among the tables listed in the Linked Table Manager.
I have checked Linked Table Manager and the tables are linked properly and update.Thus far in this tips series on Access and SQL Server, we have created an ODBC Data Source Name (DSN) using the OLEDB driver for connecting to SQL Server 2000, as well as a System DSN to connect to a SQL Server 2005 instance using the new SNAC (SQL Native Client) driver.This was in preparation for using Microsoft Access as a front-end tool for a SQL Server 2005 database.I am using Northwind as a database source in this tip, so you will see below that I have selected all tables except for the date-specific table 'Category Sales for 1997' in the tables listing on the interface.Once selecting the 'OK' button on the previous screen you will be prompted to select a 'Unique Record Identifier' for each table.