Re: Re-logging into An Oracle Database

Marvinq wrote:

Hi Tim,
      Thanks for your reply.  Yes, I am talking about a single Access
Application that uses two user accounts.  One account has access to a
certain set of tables, the other has access to another set of tables.
And there is some table overlap.
	Right now, I have the process almost completely automated, except for
the part of relogging back in with a different userid so this process
can finish.  I've done it in MS SQL but never in Oracle.  So I'm a bit
of a fish out of water here and I'm not sure what I'm doing wrong.
	My connection string seems to work (no detectable error message), but
the app seems to keep the old userid so I can't see the tables I need
to see.
	Anything you can tell me to help?


Of course, there will be two separate DSNs, one for user1, the other for user2. If you use linked tables (I rarely do anymore, preferring pass through queries or PTQs), then it should be a simple matter of having the correct tables links.

What I prefer to do is use PTQs is to run updates via VBA, something like this (sounds like you have some experience with VBA, if not let me know). This is longish, but go through it slowly if you're relatively new to this:

'code start AIR CODE ALERT! ****************************

Function fRunPtq(strS as string, strConnect as string) as Boolean

'strS is the ORACLE action query (update, delete, insert)
'Expressed in Oraclese, not Jet.
'
'strConnect is a connect string constant

  dim dbs as DAO.database
  dim qdf1 as DAO.querydef

  set dbs = access.currentdb

  'Make a temporary querydef, ie, name = ""

  set qdf = dbs.createquerydef("")

  with qdf

    .connect = strConnect

    .SQL = strS

    .returnsrecords = false

    .execute dbfailonerror

    .close

  end with

Exit_Proc:

  Set qdf = nothing

  dbs.close

  Set dbs = nothing

  Exit Function

Err_Proc:

'no error handling included here for this post, but error procs can be included to return a false for this function of the query does not execute.

End Function

'Code end*******************************

In a standard module, I would have the connect strings as public constants as follows (watch wrap) which is copied from one of my own apps and passwords, user names changed(!). Note each string is for either of the two different users, user1 and user2:

'COde start *******************************

Option Compare Database
Option Explicit

'Connect Strings

'For USER1 Oracle user

Public Const cUser1Connect = "ODBC;DSN=OOGA;UID=USER1;PWD=whatever;DBQ=tma;DBA=W;APA=T;PFC=1;TLO=0;DATABASE="

'For USER2 Oracle user

Public Const cUser2Connect = "ODBC;DSN=Ooga2 XXX;UID=USER2;PWD=whatever;DBQ=TMA;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=0;"

'Code End*******************************

To get the proper DSNs, all you need do is create a pass through query on the query design screen and set the connect string property there. Then copy and paste as above.

Now, as an example, to run two separate update statements (queries) in the two separate users, I generally write the Oracle SQL code as part of the VBA function. The following is air code.

'code start AIR CODE ALERT! ****************************

Sub sExample()

'No error handling shown here

  dim strSql as string 'for the Oracle SQL

  'The first will be an update against a table in USer1

  strsql = "UPDATE MyOracleTableA Set MyColumn = 'A different value'

  'Now call the above function and run this sql.  Note the use
  'of cUser1Connect:

  fRunPtq strSql, cUser1Connect

  'Now, an update against a table in USer2

strsql = "UPDATE SomeOtherOracleTable Set AnotherColumn = 'A different value'

  'Again, the above function runs this different statement.
  'Note the use of cUser2Connect this time:

  fRunPtq strSql, cUser2Connect

  'And so on.

end Sub

'Code End*******************************

Note that with different users, you may need to express a user name in front of the table name.

Hopefully you are able to follow this. If you have difficulty, go slowly, I've used a considerable amount of VBA.

Let me know if I can clarify or help further.
--
Tim    http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto  "TIM-MAY!!" - Me
.

Relevant Pages

  • Re: Form Advice Relating to Queries
    ... books, Mastering Access 2002 Premium Edition, Access InsideOut 2002 by Helen ... For more complex queries, use vba programming..... ... I haven't quite worked out how to use the Query Builder via ... ... The form's RecordSource property is a string. ...
    (microsoft.public.access.forms)
  • Re: "You canceled the previous operation."
    ... You can build the SQL string in VBA and then assign the string (if it is not TOO ... named query as the record source and edit its SQL. ...
    (microsoft.public.access.queries)
  • Excel cannot FIND data imported from oracle
    ... You can try modifying the query you're using to get the ... data out of Oracle to cast the data as interger, real, ... to string or double, etc. ... >sheet I want to perform the VLOOKUP in. ...
    (microsoft.public.excel.programming)
  • Re: SQL query fails
    ... but the second operator in the string has 3 ... that is acceptable to Oracle, SQL Server, and Access. ... >> I have a query that works in Oracle and SQL Server, ...
    (microsoft.public.access.queries)
  • Re: Fastest String search
    ... I have to query the database with the string from text file. ... By this I mean writing your own Binary File Access method and reading the Oracle database records without using any database engine. ...
    (microsoft.public.dotnet.languages.vb)