Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Linking Access to SQL Server

By Andy Warren, (first published: 2006/01/25)

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!

Total article views: 26832 | Views in the last 30 days: 21
 
Related Articles
FORUM

Export option (right click in the database - tasks - export data...)

Export option (right click in the database - tasks - export data...)

FORUM

Create Database

Create database taking forever

FORUM

Creating a SQL/Access Database from Scratch

One Man shop needs Direction to create a SQL/Access Database for Mangement Staff

FORUM

database properties not accessing

database properties not accessing

FORUM

grant access to all databases

grant access to all databases

Tags
access    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones