Automatically Pass Credentials to Query Linked Tables

[Kinger43]

I apologize if this may not be the best place for this question but I wasn't sure where to put it. I'm working in Access 2007. I have an ODBC connection to an Oracle database where I have linked to a few tables. On a daily basis I need to query the tables for a list of customers and a bit of associated information. I then need to export these records to a CSV file and rename it. I constructed a batch file to be called by the Task Scheduler which opens the Access DB, runs the macro to create the customer list and export to CSV file, and renames the file. This all works fine. The problem is that in order to run the query I have to provide credentials to the Oracle database. This is a process that will run in the middle of the night. Essentially it is just putting out an up-to-date customer list for someone else and a different application to use on occasion. Is there some way through VBA code, or in the macro, settings in the database, etc. to pass these credentials to Oracle without them having to be manually typed? SendKeys half-ass works but we all know that is a poor solution. Any help is appreciated, thanks!

 

[pbaldy]

If the tables are linked, you should have had the option to check the "save password" box during the linking process.

 

[Kinger43]

Thanks pbaldy. I completely overlooked that checkbox when I was linking the tables. I knew there had to be a simple solution.

 

[pbaldy]

No problemo.