RE: Linked Table Password

From: kbanes (kbanes_at_discussions.microsoft.com)
Date: 09/01/04 

Date: Wed, 1 Sep 2004 06:11:03 -0700

I assume that you are using queries to access the data in the SQL Server
tables. It is possible to imbed the user ID and password in a connect string
for the query. The following is an explanation that I wrote for other Access
users in my company. It is directed for Oracle users, but also applies to
SQL Server, as noted.

I hope it works for you.

The following is a simplified instruction set that allows a user to imbed an
Oracle SQL connect string into an ACCESS query.

Open the Access table.

Go to the queries tab and click on the 'new' button to create a new query.

Select design view when asked how you want to create the query.

A SHOW TABLE box will pop up when the query design screen opens. Close this
box without selecting a table.

Go to the menu bar at the top of the Access screen (or right click in the
area where you table normally is displayed) and select 'Properties'. This
will show you the list of options for this query.

About halfway down, you will see a field called 'Source Connect Str'

Copy the following line into this field:

ODBC;DSN=?????????;UID=????????;PWD=???????;DBQ=????????;ASY=OFF;

The "DSN=" is the dataset name that you select for the data when you link
the tables the normal. Use the same name for the "DBQ=" entry.

Close the Properties box.

Move your mouse back to the query building screen to the area where your
selected tables are normally displayed (which is now empty).

Right click this empty area. A list of possible actions will pop up. Click
on 'Show Table...'

Access will go out and get a list of all tables in the database that you
specified in the 'Source Connect Str', in this case LKWPROD.

Select whichever table you need.

You will now have a box for the selected table showing all of the available
fields. The header for the box title will be similar to:

IKW_TEST.V_PPT_RAPID_FOR_SALE

The information to the left of the '.' is what Access recognizes as the
server name and the information to the right of the '.' is the table that I
selected for this example.

BEFORE YOU SELECT A FIELD, YOU MUST DO THE FOLLOWING:

Right click on the header for the table.

Select 'properties'.

The Field List Properties box will open.

In the 'Alias' field, modify the existing string (in this case
IKW_TEST.V_PPT_RAPID_FOR_SALE) by removing the Server Name (IKW_TEST) and the
period, leaving just the table name in the alias (V_PPT_RAPID_FOR_SALE).

You can now treat this query like you would any other. Make sure you save it.

If you want to use more than one table from the same Oracle Source,
duplicate the above instructions for each table needed.

Although I've never tried it, you should be able to use the method to gather
data from servers of different types (i.e Oracle and SQL Server). SQL Server
requires a slightly different connect string, which looks something like this:

ODBC;DSN=ccndsxsql1;UID=nuc_dev;PWD=nucdev;DBQ=ccndsxsql1;ASY=OFF;

The only real difference that you see is that Access REQUIRES the server
name for Oracle tables to be in ALL CAPS. If you don't put it in ALL CAPS,
it won't work. Or at least it hasn't for me. Non-caps is ok for SQL Server.
 I don’t know why.

Be warned. This can be very picky. For some reason, it may take several
attempts to make this work. You may have to re-do it a couple of times.
Once you get a query to actually run one time, it works every time after
that. I can not explain this.

"Neil Ginsberg" wrote:

> I have ODBC linked tables to a SQL 7 database in an A2K database. The linked
> tables do not have the password stored in them, so the first time the user
> accesses them, they need to enter the SQL password.
>
> I am developing a process that will automatically run at night which will
> access those tables. I need to be able to give Access the password, as the
> user currently does, so that the process can run without a password prompt
> appearing. Opening a recordset or some other object based on one of the
> tables in which I could provide a password would be ideal, but any method
> would be fine.
>
> Is there a way to provide a password in code for a SQL linked table?
>
> Thanks!
>
> Neil
>
>
>
>