Linked Table Password

  • Thread starter Thread starter Neil Ginsberg
  • Start date Start date
N

Neil Ginsberg

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
 
Neil Ginsberg said:
Is there a way to provide a password in code for a SQL linked table?
Click to expand...

I don't know about that but there is a 'remember password' option when you
first link the table.

Regards,
Keith.
 
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!)
 
Actually, I have a large amount of code that uses dynamic SQL. But your
advice gave me an idea: create a generic query with the password built in;
then change the SQL property of the query to match the current dynamic SQL
and execute it. That should work.

Neil
 
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.aspx?scid=kb;en-us;177594&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
 
Back
Top