Thanks for your feedback. I might try just creating a new link with password
and then deleting it to see if that satisfies the password requirement.
Probably won't; but I'll try it. I may also try a variation of your #4, but,
instead of renaming the current links, just link the tables I need with new
names, and adjust the code to use those, and then drop them.
Interestingly enough, I found a knowledgebase article that gave instructions
on specifically how to avoid the password in code ("How To Bypass Login
Prompt When Opening Linked Table" -
http://support.microsoft.com/default...4&Product=acc).
Unfortunately, the article says it only applies to Access 97 and earlier.
Indeed, when I tried it, I got prompted to confirm the password that the
code provided. It's interesting that A97 and earlier were able to work
around this, but not A2K (or, at least, apparently not).
Thanks,
Neil
"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SP AM> wrote in
message news:e1JK65BkEHA.2908@tk2msftngp13.phx.gbl...[color=blue]
> 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.earthl ink.net...[color=green]
>> I have ODBC linked tables to a SQL 7 database in an A2K database. The[/color]
> linked[color=green]
>> 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
>>
>>
>>[/color]
>
>[/color]
