Marvinq wrote:
[color=blue]
> 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?[/color]
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
