Set the check boxes for the tables that you need to re-link and then set the check box at the
bottom for “Always prompt for new location.” Click OK and you will be prompted to browse
to the new target database. The selected tables will then be set to point to this database.
If you use SQL Server or Oracle databases, these very rarely move their location. If they
do, you can fix this by going to the ODBC Administrator and editing your DSN to the new
server/database name.
Far more likely is the addition of new fields into tables (which you will be expected to
incorporate into your application), or even worse, the re-naming of fields. Again a refresh
through Linked Table Manager, without the “Always prompt for new location” check box
being set, will bring these changes into your linked tables.
Of course, you will have to manually change your queries and code to take account of
these table structure changes.
An obvious problem is also the fact that only you have the ODBC DSN set up on your
computer. If another user wants to view your application, it will not work until the DSN has
been created on the user’s computer.
Fortunately, there is an easy way to do this, by adding the following code into your
opening form:
DBEngine.RegisterDatabase "MyDSN", "SQL Server Native Client 10.0", True, _
"MyServer" _
& vbCr & "Database=MyDatabase"This will create a DSN called MyDSN (or over-write it if it is already there) to connect to
a server called MyServer and a database called MyDatabase. You do not need the ID and
password because these are already embedded in the linked table, and it would be bad from a
security angle to show a password within your code.
One further problem you may encounter is the performance of your queries based on
tables linked in this way. If the tables are large or not indexed, your queries may take a long
time to run because Access is drawing across the data for each table and then processing it
through your query. This can also cause performance problems for other users of the external
database and may make you extremely unpopular! However, solutions are shown in the next
sections.Using Pass-Through Queries
Pass-through queries are similar to Access queries except that they run on the server, so that
the client end receives nothing more than the data required. They effectively run like a stored
procedure on the server and have a huge speed advantage when there is a lot of data.
To put a pass-through query together, you still need a DSN to connect to the database. If
you already have your DSN set up, click Create on the Access menu and then click the Query
Design icon in the Macros & Code group of the ribbon.
This will call up the Query Design window. Close the Show Table window and click the
SQL View icon in the Views group of the ribbon. Click the Pass-Through icon in the Query
Type group of the ribbon and then select the Property Sheet icon in the Show/Hide group of
the ribbon. This will display the property sheet (see Figure 19-6).246 Microsoft Access 2010 VBA Macro Programming