Macro Automation & ODBC Driver Connect

[bkf7911]

I've got a database that contains a macro I'd like to put on a scheduler to have run overnight, but my current code isn't set up to get past the "Oracle ODBC Driver Connect" prompt that i'm receiving. The following is the code i've rewritten to work with my current project.

How could this be modified to include sending the "Service Name", "User Name" and "Password" for the ODBC prompt?


****************
dim o

set o=createobject("Access.Application")
o.automationsecurity=1
o.opencurrentdatabase "C:\Documents and Settings\Desktop\WS - DB1\DB1.mdb"
o.usercontrol=true
o.docmd.runmacro "Import"
o.CloseCurrentDatabase
o.Quit

set o=nothing

 

[rgbanse]

This is what I use
In your macro use RunCode - Establish_Connection()

Public Function Establish_Connection()
Dim db As Database
Dim dbcurr As Database
Dim ds As Recordset
Set dbcurr = CurrentDb()
'Logs on to Your Database
Set db = OpenDatabase("", dbDriverComplete, False, "ODBC;DSN=YourDSN;DB=YourDbName;HOST=YourHostName;SERV=YourSetting;SRVR=YourServerName;PRO=onsoctcp;UID=YourUsername;PWD=YourUserPassword")
End Function

RGB