Allowing a macro to log in to Oracle ODBC automatically

[Shagnasty]

I am helping a coworker automate updating an Access 2000 database. He wants a macro to run at 6:00 and run a series of update queries. I made a batch file that triggers the macro and the Windows XP Scheduler successfully triggers it. However, the tables that the queries use are linked Oracle ODBC tables. They require a user name and password before the connection is established. I need to find a way for the macro to automatically log in to the ODBC connection before the queries run. I have the DNS, user name, and password that needs to be used.

 

[RV]

I guess you need to define a user defined function to define a connection using VBA.
Define the function in a module.
Save the module and call the function from within your macro.

Search the forum, similar questions have been asked before.

RV

 

[Pat Hartman]

You'll need to expand into VBA. Here's a function I used to log in to two Oracle databases in a batch process.

Public Function ConnectToOracleBatch()
    Dim WgtDB As DAO.Database
    Dim strConnect As String
    
    Set WgtDB = CurrentDb()
    strConnect = "ODBC;UID=WGT;PWD=weight;DSN=weight;Database=WGT"
    Set WgtDB = OpenDatabase("", False, False, strConnect)
    strConnect = "ODBC;UID=FARMS_EXTRACT;PWD=mike;DSN=farms;Database=farms"
    Set WgtDB = OpenDatabase("", False, False, strConnect)
    WgtDB.Close

End Function