Re: Linked Table Password
From: '69 Camaro (ForwardZERO_SPAM.To.69Camaro_at_Spameater.orgZERO_SPAM)
Date: 09/01/04
- Next message: kbanes: "RE: Linked Table Password"
- Previous message: '69 Camaro: "Re: How do you change a "remembered" password to ODBC linked files?"
- In reply to: Neil Ginsberg: "Linked Table Password"
- Next in thread: Neil Ginsberg: "Re: Linked Table Password"
- Reply: Neil Ginsberg: "Re: Linked Table Password"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 1 Sep 2004 05:27:39 -0700
Hi, Neil.
If a linked table needs a password, then the user must supply the password
manually (or pretend to -- more on that later) the first time the table is
accessed. That's how this linked table was set up to be used. Your
automatic process that runs each night won't be able to complete its tasks
while using the current links without:
1.) Human intervention, or
2.) SendKeys commands to "pretend" that a human supplied the password
through the user interface, or
3.) Dropping the current links, recreating the links to the SQL Server
tables with the password, using the new linked tables and when finished,
dropping these links, and recreating the links again without the password so
that subsequent use of the linked tables will prompt the user the first time
the linked tables are accessed, or
4.) Renaming the current links, creating new links to the SQL Server tables
with the password, using these new linked tables and when finished, dropping
these links, and renaming the altered links to the name that they were to
begin with.
Solutions #3 and #4 will hose your relationships if you have relationships
established, so you'd have to programmatically undo those relationships
before, then redo those relationships for the new links, then redo those
relationships again after the automated process. Very messy. Not
recommended.
SendKeys commands will be sent to the window that currently has the focus,
and windows can change focus unpredictably, so SendKeys commands should be
avoided whenever possible.
A better alternative is to avoid the currently linked tables. Instead,
create connections to the SQL Server tables using ADO or DAO in a VBA code
module. These connections can be DSN or DSN-less connections, but these
connections would programmatically supply the User ID and password that your
automated process needs.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
(Any human can read my reply E-mail address and should alter it so that a
message will be forwarded to me. Spammers are free to use my UNALTERED
reply E-mail address. I will *never* get those messages!)
"Neil Ginsberg" <news@nrgconsult.com> wrote in message
news:3LbZc.4230$w%6.3824@newsread1.news.pas.earthlink.net...
> 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
>
>
>
- Next message: kbanes: "RE: Linked Table Password"
- Previous message: '69 Camaro: "Re: How do you change a "remembered" password to ODBC linked files?"
- In reply to: Neil Ginsberg: "Linked Table Password"
- Next in thread: Neil Ginsberg: "Re: Linked Table Password"
- Reply: Neil Ginsberg: "Re: Linked Table Password"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
