I am new to Access and need to know best way to automatically populate username and password when I try to link Oracle tables - SOX compliance at my company requires that when I try to link tables from our Oracle DB, I need to populate the dialog box with username and password. IS there a way to automatically populate these credentials?
-
-
Hi - I'm not sure if this is what you're looking for, but it's close. We link to Oracle tables through ODBC and have a query that runs and makes the connection as soon as the database opens. Here's the breakdown:
1.
Creat an SQL passthru query using something similar to this: SELECT COUNT(*) FROM PRODUCT (any public.table in oracle....the smaller the better)
Right click the top bar to get to the query properties and enter the following into the ODBC Connect String box:
ODBC;DSN=DATA SOURCE NAME;UID=USERNA ME;PWD=PASSWORD ;DBQ=DATABASE_N AME;DBA=W;APA=T ;PFC=1;TLO=0;
Check the following in the query properties also:
Returns Records box = Yes
Log Messages = No
ODBC Timeout = 60
Change the neccessary elements such as the DSN= must be the DSN defined on your(each) pc. UID=your user id, PWD= your password. DBQ=database named in the ODBC connection.
Save the query, (we name ours qryODBCConnect) , then test by running it. You should get a record count from whatever table you used. If not, make sure the ODBC names are correct.
2.
Create a macro that opens the query you just created in edit mode, datasheet view and save it with the name autoexec.
Now, when you open the Access database, this query will run automatically connecting to Oracle. -
precious little useful information out there about this five years later.
seems like pass-through's are the way to handle that dialog (which can pop up at will even after you've made a connection in your code).Comment
Comment