Access updating using linked tables
For example, you might import data because it is no longer necessary to use the legacy system that you have in place.I have a MS Access Database which has a Linked Table called "Combined" that is from a text file.If any tables were not updated, we want to display them to the user; otherwise, display a message that the update succeeded.The i Updated counter variable is used to display the succeeded message only when tables were actually updated.Many Access applications split the database - that is, the tables are located in one database while the application components (forms, reports, and code) are in another.
To split the database in Access 2007, choose the Database Tools tab in the Ribbon and then click the Access Database button in the Move Data group. When deploying a solution where the database is split, you may need code that dynamically refreshes the linked tables in the front-end database.
This is to make sure that the database always has the latest connection information.
To do this, create a new module and add the following routine: Public Function Ensure Tables Connected() As Boolean Dim db As DAO. Table Def Dim st Backend Db As String Dim st New Backend As String Dim st Not Updated As String Dim i Updated As Integer ' number of tables updated ' get the current database Set db = Current Db() The first thing we want to do is iterate through all the tables in the database so we'll begin a loop.
To get the database to verify that the tables are linked when the application loads, call this routine using the Run Code action in a macro named autoexec.
The Linked Table Manager is an important tool for working with linked tables.