Can VBA Plug in ODBC password?

[Brad]

I want to use Access to pull data nightly from a remote SQL-Server database.
I have the ODBC connection working, but it requires that a password is
entered for each nightly run. The password cannot be removed.

Is there a way in VBA to make the ODBC connection and plug in the password
automatically so that no manual intervention is needed each night when this
process takes place?

Thanks in advance for your help.
Brad

 

[DaveT]

You might take a look at:

http://support.microsoft.com/kb/892490
How to create a DSN-less connection to SQL Server for linked tables in Access

 

[David H]

I'd have your SQL Server Administrator take a good look at the UserId to
ensure that if it and the password are compromised that the risk is
mitigated.

 

[Brad]

Dave,

Thanks for the info. I appreciate it.

Brad

 

[Brad]

David,

Thanks for your help.

Brad

I'd have your SQL Server Administrator take a good look at the UserId to
ensure that if it and the password are compromised that the risk is
mitigated.

 

[DaveT]

Agreed, good idea. In most caes the SQL DBA has already considered this in
providing - for example - read only privileges, etc.