ODBC Login Issue

[jackdandcoke]

So I'm running some queries off some linked ODBC tables. When I kick off the query it asks for a username and password. I'd like to put those queries into a Macro but I can't figure out how to put my credentials in query.

Instead I'm refreshing the link using the DoCmd.TransferDatabase acLink. But when I do acLink, it wants a destination table. I'm having trouble here. Can someone see if this code should work.


DoCmd.TransferDatabase acTable, "ODBC Database", "ODBC;DSN=IBM_PR6;HST=ibslprb6.sysplex.company.com; _
PRT:4116;uid=hdjbc;pwd=xxxx;", acTable, "PRHDW.CUST_ORD", "PRHDW_CUST_ORD", 0, True


I think the problem is that the original table that I link changes names from PRHDW.CUST_ORD to PRHDW_CUST_ORD. Has anyone accomplished logging into a DB2 table from vba before that could give me some pointers?

[CreganTur]

WHen you setup your initial ODBC link to the external table, there is an option to save the password. This means that the username and password will be saved as a part of the default connection string that is used every time Access attempts to interact with the external table.

Doing this eliminates the need for your users to login every time they run a query.