Connect Access to a SQL Table?

[JessiRight77]

Hello:

My Electronic Document Storage software uses a "MS SQL Server Table
Lookup" feature to simplify document indexing. Although we do have
SQL Server database installed, the indexing information we need is
currently being maintained in an Access Database.

It is my understanding that it is possible to somehow connect our
Access database to a SQL Server Table Lookup... but I have no idea how
to do this. Can someone give me some pointers, or point me to a
website that explains how to do this?

Any help at all would be greatly appreciated!

Jessi

 

[Douglas J. Steele]

Create a DSN that allows you to connect to the SQL Server database.

In Access, select File | Get External Data | Link Tables... from the menu.
Set the Files of Type combobox to "ODBC Databases", select the DSN you just
created, then select the table from the list that appears.

You'll now be able to work with the table in SQL Server as though it were in
your Access database (assuming, of course, that the SQL Server Table has a
primary key defined on it. You won't be able to update the table if it
doesn't)

Once you've done this, it's possible to modify your linked table so that it
doesn't require the DSN. This will make it easier to share your Access
front-end with other people, as they won't have to create the DSN on their
machines. See http://www.accessmvp.com/DJSteele/DSNLessLinks.html for one
way to do this.

 

[JessiRight77]

Thanks so much for your help, Mr. Steele. I will defnitely try your
instructions next week.

Another question: On which computer should I create the DSN? (The
Access database is on one computer, and the scanning software that will
use the connection is on another computer, and the SQL Server database
is on a third computer.) Also, I assume that I do the Access
procedure (Link Table) from the first computer that uses the Access
database, right?

Thanks,
Jessi