This is a brief tutorial showing how to create an Access 2003 database and
then link to tables in SQL Server. This is a process similar to creating a
linked server in SQL. There are quite a few images shown and the process may
seem lengthy, but in practice it probably takes about a minute to do all the
work shown in this article.
Open Access 2003 (process is very similar for earlier versions), then select
Blank Database. Type in a new name and click OK.
One point to remember is that these databases work basically like any
document file. Unless you've delved into the supported security model, you can
just copy/move the file to any other machine that has Access installed and it
will work fine. The file contains all your database objects. The next image
shows you the default view of an empty database.
Now is where it gets interesting. Rather than create a table within Access,
we want to leverage some existing data - for this example our favorite, SQL
Server, but it does support quite a few different ones. Connecting to external
data is called "linking" and is very similar to the concept of a linked server
in SQL. Change the drop down (2nd image) to ODBC Databases.
Now we're going to select an existing DSN or create a new one. If you've
never created one, it's basically a way to describe how to connect to a data
source and that description is external to your application allowing it to be
changed without recompiling. For more info, I wrote an article years ago about
DSN's. Because it's key to the rest of the stuff we'll be discussing, I'll
go through the process of making one so you can follow along.
In the dialog above, click New. Scroll to the bottom and select SQL Server,
then click Next. Type in a name for the DSN, something easy to understand when
you look back at it later (something better than the 'TEST' I used!).
Click Next. Then click Finish. This launches a second wizard and here I've
elected to connect to "(LOCAL)", which is a shortcut to the default instance of
SQL installed on my machine.
Click Next. For this demo we'll stick with NT authentication, so just click
Next again. Then I'm changing the default database to Northwind.
Click Next. Then click Finish. At this point you can choose to test your
connection (a good idea) or complete the DSN by clicking ok.
Now we're almost back to where we started. We're going to select the 'Test'
DSN we just created and click OK.
Now, finally, we can do something meaningful. The imagine below shows all the
database objects in Northwind to which I'm allowed access. I'll select two
tables and click OK.
This will return you back to Access and you can see now that we have two
linked tables. Both the tables have the 'dbo_' prefix, which is the convention
Access follows, using the name of the object owner plus an underscore to make
the name unique.
At this point you can open either table and make changes that your database
permissions allow. Remember, this is live data you're modifying, Access
is only providing the interface to do the editing!
