By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,663 Members | 1,844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,663 IT Pros & Developers. It's quick & easy.

Linked Table Password

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"Neil Ginsberg" <ne**@nrgconsult.com> wrote:
Is there a way to provide a password in code for a SQL linked table?


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

Regards,
Keith.
Nov 13 '05 #2

P: n/a
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" <ne**@nrgconsult.com> wrote in message
news:3L*****************@newsread1.news.pas.earthl ink.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

Nov 13 '05 #3

P: n/a
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" <Fo**************************@Spameater.orgZERO_SP AM> wrote in
message news:e1**************@tk2msftngp13.phx.gbl...
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" <ne**@nrgconsult.com> wrote in message
news:3L*****************@newsread1.news.pas.earthl ink.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


Nov 13 '05 #4

P: n/a
Yes, Chuck, I realize that the password goes in the Connect statement or
property, if desired. The point was that it wasn't there. The user wants to
be prompted for it each time. But there is an automated process that is to
run at night that needs to be able to provide the password to Access without
the prompt. That's the dilemma.

Neil

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:00********************************@4ax.com...
On Wed, 01 Sep 2004 03:55:43 GMT, "Neil Ginsberg"
<ne**@nrgconsult.com> 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?


Yes. The password goes in your Connect statement or property. Of
course, remember that doing this results in a bypass of SQL's
security, and really isn't recommended. There are also ways to reveal
the connect string, which puts the SQL database even more at risk!

You really should be using trusted connections, and mixed SQL logins
to allow the user's Network Login to be passed to the SQL DB and have
it decide if the user can be linked.

But then again, since you're obviously allowing an Access Database to
link to tables directly.... <shrug>

The query below will show you the connect string currently being
stored in Access for each linked table:

SELECT MSysObjects.Name,
MSysObjects.Connect
FROM MSysObjects
WHERE MSysObjects.Type=4
ORDER BY MSysObjects.Name;

If you look at the MSysObjects.Connect column, you'll see the
connection string, UID and Password. Not pretty.

(Remember not to try to update this table directly! Even though you
can see it here, use valid code to update it, or risk the loss of your
database to corruption. You have been warned.... (Yeah, experience
is *nasty* teacher!))
--
A Prudent Man Does Not Make A Goat His Gardener.

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.