Logging into an Oracle dbase using Access 1

[ddonia]

Hello everyone,

Let me first start by saying that i am using Access 97. The problem i am trying to solve is as follows:

i have set up many databases to run at night via a scheduler. this works great for most dbases but a few of them require me to log into Oracle tables that we use. because the dbases are being updated at 2am, i am not here to log in.

i am wondering if there is a way, through VBA, to have the dbase automatically log itself in. when the dbase goes to use the Oracle tables can i call a function that tells Oracle who i am and what my password is?

any help will be greatly appreciated.

thanks,
Doug

 

[BillPower]

If your Tables are Linked using ODBC, you could try something along these lines:

To get Link info:

Dim CurDB As Database, tdfLinked As TableDef, tbl
Set CurDB = CurrentDb
For Each tbl In CurDB.TableDefs
If tbl.Attributes And dbAttachedODBC Then
Debug.Print tbl.Name & ", " & tbl.Connect
End If
Next
Set CurDB = Nothing

To Link to the Oracle DB:

Dim CurDB As Database, tdfLinked As TableDef
Set CurDB = CurrentDb()
For Each tdfLinked In CurDB.TableDefs
If tdfLinked.Attributes And dbAttachedODBC Then
If Len(tdfLinked.Connect) > 0 Then
tdfLinked.Connect = "ODBC;Description=TestSQL;DRIVER=SQL Server;" & _
"SERVER=oemcomputer;APP=????????sC;WSID=OEMCOMPUTER;DATABASE=NorthwindCS;" & _
"UID=sa;PWD=" 'Replace this string with whatever is returned above
tdfLinked.RefreshLink
End If
End If
Next tdfLinked
Set CurDB = Nothing

 

[ddonia]

Thanks billpower!!! this worked perfectly!