Microsoft Office Tutorials and References
In Depth Information
When you attach a table from an ODBC data source, Access adds a prefix to
the table’s name, which is dbo_ by default (short for database owner). This
prefix indicates the owner of the table in SQL Server. If you don’t want to see
the SQL tables with the prefix, simply right-click the table, choose Rename
from the contextual menu, and give the linked table a new name.
Using linked tables in Access
Now that you have these tables from SQL Server appearing inside Access,
what do you do with them? Well, you use them just as you use any other
local tables in Access. You can build queries, forms, reports, macros, and
modules that reference these tables, just as you do in the other eight minibooks.
The main difference is that you can’t alter the structure of a linked table.
If you need to add a field or change a field size, you have to log in to SQL
Server to perform that operation.
You can mix and match tables in your Access database. To see how this
process works, create a few local tables for data that doesn’t need to be on the
server, connect to a several different databases, and gain access to the data
from one .accdb file. The possibilities are endless when you know where all
this data resides.
Maintaining linked tables
Different things can cause the links to an ODBC database to break. The
server may change locations, someone may change the structure of the
SQL Server table or database, or you may move the .accdb file to another
machine. Whatever the reason, if the link is broken, you’ll know: The tables
won’t display any data, or you get an error when opening a linked table.
To fix this problem, click the Linked Table Manager button in the Import
& Link group on the External Data tab of the Ribbon, select all the linked
tables, select the Always Prompt for New Location check box in the
bottomleft corner of the dialog box, and then click OK. The Select Data Source
dialog box opens (refer to Figure 2-3, earlier in this chapter). Select the
existing data source or create a new data source to correct the problem.
For more information on using the Linked Table Manager, see Book II, Chapter 4.
ODBC allows you to use data from any number of data sources, including SQL
Server. Just one simple button on the Ribbon opens your world and a variety
of options. Whether you’re in a small company with one server or a large
organization with multiple servers around the world, you can use Access to
connect to these databases and build eye-catching front-end applications
to view and manipulate this data.