writing the oracle username to an oracle table 1

[joeythelips]

Hi,

I have an access application (2000) which uses vba to import excel files and write this data to an oracle database (8.1.7).
The tables are linked using a odbc link from access to oracle.
When a user opens the application, he/she is asked to verify the odbc link.

What i want to do is capture the user name in my logfile table in oracle.

Currently my logfile table captures the imported filename, the current time, and the number of unique records.
Please see my code below for how i do this. The new field name in the logfile table is called username.

I would really appreciate help on this.
Regards,

Joe

Sub Logfile(READFILENAME)
Dim dbs As Database
Dim rs(1 To 3) As Recordset
Dim i As Integer
Const conSource = "LogFile"
On Error GoTo ErrHandler



Set dbs = OpenDatabase("P:\FileImporter.mdb&quot

Set rs(1) = dbs.OpenRecordset("SELECT * FROM ReadingsBills&quot
Set rs(2) = dbs.OpenRecordset("SELECT DISTINCT [Meter Ref#] FROM readingsBills&quot
Set rs(3) = dbs.OpenRecordset("SELECT * FROM LogFile&quot

For i = 1 To 2
If Not rs(i).EOF Then
rs(i).MoveLast
End If
Next

With rs(3)
.AddNew
.Fields("FileName&quot = "RE" & READFILENAME
.Fields("TimeDateStamp&quot = Now()
.Fields("TotalRecordCount&quot = rs(1).RecordCount
.Fields("UniqueRecordCount&quot = rs(2).RecordCount
.Update
End With

For i = 1 To 3
rs(i).Close
Set rs(i) = Nothing
Next
Exit Sub
ErrHandler:
Set rs(1) = Nothing
Set rs(2) = Nothing
Set rs(3) = Nothing
Err.Raise Err.Number, conSource, Err.Description
End Sub

 

[Chalco]

Create a trigger in the oracle database which fires on insert only. The trigger should set the field to user (Oracle predefined variable). This way, your code is transparent, and you ensure that whenever a record is inserted (wether it be from your code or somebody elses, the value is always set)

 

[joeythelips]

Hi Chalco,

thanks for that.

are you saying that it is not possible for me to amend my vba to trap the oracle username?

Joe

 

[Chalco]

No, just that by putting it at the backend, it may be easier (I am more of an Oracle user than Access) and I try to put as much functionality into the database than the client - as I said, that way, it means you don't have to set the field in every program that may insert into the table (may only be 1 program now, but what about 5 years time ?).

No doubt an Access person will come up with the Microsoft view though !!

 

[joeythelips]

Hi again,

for the purposes of what i need, i would rather get the info from the access application rather than creating a trigger at the db level.

basically, is there a function in access which displays the oracle username that was prompted for at logon time?

Thanks again chalco

Joe