Include AS400 Password in Query

[schneidm]

Hi,

I have an Access query that I would like to automate. However, it is using an ODBC link to an AS400 Table that requires a password. How do I programmatically include the password so my macro doesn't halt at a Logon box?

Thanks.

[Oorang]

Hi schneidm,
This is one of those issue where you need to balance usability with security. Hard-coding passwords tends to be a problem no matter what your approach. As you may be aware the Visual Basic Project itself is not encrypted even when locked for viewing. This means if someone opens you Office File with notepad instead of Access/Excel etc. They can read the password you hard-coded in plain text.
If create linked tables instead the password then gets saved as part of the table connection string, and will often appear in the hover text when floating over the table. Access 2007 attempts to mitigate this, but it is still visible if you view the CurrentDB.TableDefs("MyTable").Connect property.
If you go with a user or machine DSN, the password is stored in plain text in the registry. And with a file DSN it's in the file.
Bottom line, unless you want to roll your own encryption (don't do that BTW) you will end up with a password stored in plain text somewhere on the computer.
The preferred approach (and the one likely to be compliant with your companies IT policies) is to allow the user to be prompted for their credentials and then proceed when they are logged on.

[schneidm]

Thanks for the info. Not really what I wanted to hear, but I understand. Because I want the queries to run early in the morning automatically, I may just have to logon to the AS400 server prior to leaving for the day. Once I've logged on in a session, it will retain my logon info until I shutdown.

[Oorang]

Well... Leaving an unattended login session has it's own risks. You might consider talking to the Admin and seeing if you can get a push job set set up. So at night the batch runs and the files are pushed to an SFTP or shared drive or whatever.