MS Access, ODBC, Jet OLEDB, Pass Through, Oracle, Trigger

[PCLewis]

Oracle ODBC Client 9.02
MS Access 2003
Oracle 10g

I am not an Oracle or Microsoft trained programmer so there may be something in the following scenario(s) that a trained expert would not fall for.

I have a legacy client application which runs in Access 2003. I have been replacing code in the Access front end with either Stored Procedures or with Pass Through Queries as much as possible to both speed things up and to place the code in the appropriate tool.

The legacy code uses linked tables to the Oracle database quite extensively and I have not undertaken to change that at this stage.

When the user first starts the application, they are prompted for their Oracle username and password by the ODBC driver because the main menu gets data from one table in the Oracle database via one of these linked tables.

For running pass through queries and stored procedures I have a user on the Oracle database (SPUser) with rights to run some stored procedures and to view data ONLY. Definitely no CREATE or UPDATE.

Due to the ID field in a "Clients" table being migrated from MS Access "Autonumber", there is a trigger on that table to replace the ID with one from a sequence if the ID supplied is null. Testing select queries from SQLPLUS as SPUser works fine (data returned). Testing inserts from SQLPLUS as the SPUser gives expected results - i.e. Insufficient Privileges.

However,
Running a pass through select from Access as SPUser works as expected (results returned).
Running an INSERT as SPUser from Access with the TRIGGER enabled (and no ID specified) results in 2 entries into the target table wher eonly the ID is different.
Running an INSERT as SPUser from Access with the TRIGGER disabled (and hard coding the ID field with the next sequence value) results in 1 entry into the target table PLUS the execute statement failing due to the second insert violating the uniqueness constraint on ID.

I traced the username being used to insert into the target table and when successful, it is not SPUser that is being used. The user doing the insert is the user that is logging in via ODBC when the application first starts.

So my questions if anyone is able to help:
1) What is the point of having to specify the additional parameter "Jet OLEDBass Through Query Connect String" for a pass through query if it's not being used? I can see that in my code, I am using the CurrentProject.ActiveConnection to execute the insert statement so that's why it's my username "doing" the insert, but the requirement to run a pass trough query from Access is to include those two properties (see code below). Unless by some way, an INSERT statement is not considered a pass through query?

In what way is the "Jet OLEDBass Through Query Connect String" actually used? I tested the connect string by putting in a non existent username and the insert still worked (so long as you have a valid connection already).

If I login to the database via ODBC at the main menu using the SPUser name and password (no create or update rights), then the expected behaviour occurs. Namely, the insert fails. So I'm still faced with the confusion over what that connection string does. The following URL tells me the supposed purpose but my results do not support this at all:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefjetprovspec.asp

"Jet OLEDBass Through Query Connect String
(DBPROP_JETOLEDB_ODBCPASSTHROUGHCONNECTSTRING) Indicates the Jet connect string used to connect to a remote data store. This value is ignored unless Jet OLEDB:ODBC Pass-Through Statement is True. "

My contention is that something in my use of this is not working as intended (or I am using it wrong).


2) Possibly the explanation of 1) will also explain why a duplicate record is inserted into the target table. My testing shows that in the one execute on the ADODB.Command object, two inserts are happening. If I make an insert statement using CurrentProject.Connection.Execute then it works as expected in all cases. So I don't see the Oracle database being the root cause of the issue. The issue must be coming from the ODBC driver which is why I am posting here - in the hope that someone has some expertise on this very issue. "See KB xyz re: Oracle ODBC driver 9.0 and 10g databases" is quite possibly the response to my 1 hour of typing here If so I humbly thank you because I could not find anything in my various searches.

Some of the code in question is below.

Global Const gsPassThroughConnectString = "ODBC;DSN=TestDB;UID=SPUser;PWD=SPUserPW;"

...

Public Function OpenPassThroughQry(strSQL As String, Optional strODBCConnect As String) As ADODB.Recordset
  Dim cmd     As ADODB.Command
  Dim oRS As ADODB.Recordset
  Set cmd = New ADODB.Command

  If strODBCConnect = "" Then strODBCConnect = gsPassThroughConnectString

  With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = strSQL
    .Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
    .Properties("Jet OLEDB:Pass Through Query Connect String") = strODBCConnect
    
    Set oRS = cmd.Execute
  End With
  
  Set OpenPassThroughQry = oRS
  set cmd = Nothing 
End Function

CREATE OR REPLACE TRIGGER sample_table_trig
BEFORE INSERT ON testdb.sample_table
FOR EACH ROW
BEGIN
SELECT NVL(:new.id, testdb.sample_table_seq.NEXTVAL),ora_login_user 
INTO :new.id,:new.AdjustmentMadeBy 
FROM DUAL;
END;
/