Connection Problem (Oracle and Access join)

  • Thread starter Thread starter zacharybass
  • Start date Start date
Z

zacharybass

Hello All:

My form collects Oracle UIDs and Passwords and runs a process of about
20 queries, most of which are either exclusively Oracle (SPT) or
exclusively Access. For the SQL Pass Throughs I connect using:

Sub Open_cnn1(uid As String, pwd As String)
'// Open connection to ED
cnn1.Provider = "OraOLEDB.Oracle"
cnn1.ConnectionString = "Data Source=DSN" & ";User ID=" & uid &
";Password=" & pwd
cnn1.Open
End Sub

'// Run Oracle SPT and append records to local table.

Sub equity_append(rpt_date As String)
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim recTable As Recordset
Dim strSQL As String

rst1.CursorLocation = adUseClient

strSQL = "SELECT a.MT_RCE_ORG_ID, a.MT_LOAN_SUB_TYPE, "
strSQL = strSQL & "DECODE(a.MT_LOAN_SUB_TYPE,'00','HEL',
'38','HELOC') as EQTY_PROD, "
strSQL = strSQL & "Count(a.CASE_NUMBER) as EQTY_COUNT, "
strSQL = strSQL & "Sum(a.MT_LOAN_AMOUNT) as EQTY_VOL, "
strSQL = strSQL &
"DECODE(a.MT_LOAN_SUB_TYPE,'00',Sum(a.MT_LOAN_AMOUNT *.0375),'38',
Sum(a.MT_LOAN_AMOUNT *.018)) as EQTY_REV, "
strSQL = strSQL & "a.REPORT_PERIOD "
strSQL = strSQL & "FROM PROFOWNER.PROF_MT_DAILY_PRICING_DATA_NEW
a, MTFOWNER.MTF_CUST_VIEW b, ICPOWNER.ICP_ED_ORG_MANAGERIAL_VW c "
strSQL = strSQL & "And b.MTF_PRODUCT_OPTION <> '130' "
strSQL = strSQL & "And a.REPORT_PERIOD = TO_DATE(" & rpt_date & ",
'MM/DD/YYYY') "
strSQL = strSQL & "GROUP BY a.MT_RCE_ORG_ID, a.MT_LOAN_SUB_TYPE,
a.REPORT_PERIOD "

rst1.CursorLocation = adUseClient
rst1.Open strSQL, cnn2

rst2.Open "tbl_equity_all", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
rst1.MoveFirst
Do While Not rst1.EOF
rst2.AddNew
For k = 0 To rst1.Fields.Count - 1
rst2.Fields(k).Value = rst1.Fields(k).Value
rst2.Update
Next k
rst1.MoveNext
Loop
End Sub

And for the Access queries I use (Docmd.OpenQuery "queryname").
However, I do have one update query late in the process where an
Oracle table is joined to a local Access table. As a result, the user
is prompted with the Oracle ODBC Driver Connect form thus interupting
the process. How can I avoid this? I want to create all necessary
connections through the form on the front end. I thought that either
using the existing connection or establishing a new one would work,
but I have not had any success. Any help/explanation would be very
much appreciated.

Zachary
 
hi Zach,

My form collects Oracle UIDs and Passwords and runs a process of about
20 queries, most of which are either exclusively Oracle (SPT) or
exclusively Access. For the SQL Pass Throughs I connect using:

Sub Open_cnn1(uid As String, pwd As String)
'// Open connection to ED
cnn1.Provider = "OraOLEDB.Oracle"
cnn1.ConnectionString = "Data Source=DSN" & ";User ID=" & uid &
Click to expand...
You are mixing two connection techniques, which requires two logins to
Oracle.

As you said, you need to JOIN a Jet table with an Oracle table, you
should use only ODBC/linked tables.

For direct Oracle access use passthrough queris. Set their Connect
property to a working linked table before executing/using it, and no
credentials are required as Jet uses the already established session:

CurrentDb.QueryDefs.Item("passthroug").Connect = _
CurrentDb.TableDefs.Item("linkedTable").Connect

Use appropriate Orcacle views to prefilter the data.

mfG
--> stefan <--
 
Hello All:

My form collects Oracle UIDs and Passwords and runs a process of about
20 queries, most of which are either exclusively Oracle (SPT) or
exclusively Access. For the SQL Pass Throughs I connect using:

Sub Open_cnn1(uid As String, pwd As String)
'// Open connection to ED
cnn1.Provider = "OraOLEDB.Oracle"
cnn1.ConnectionString = "Data Source=DSN" & ";User ID=" & uid &
";Password=" & pwd
cnn1.Open
End Sub

'// Run Oracle SPT and append records to local table.

Sub equity_append(rpt_date As String)
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim recTable As Recordset
Dim strSQL As String

rst1.CursorLocation = adUseClient

strSQL = "SELECT a.MT_RCE_ORG_ID, a.MT_LOAN_SUB_TYPE, "
strSQL = strSQL & "DECODE(a.MT_LOAN_SUB_TYPE,'00','HEL',
'38','HELOC') as EQTY_PROD, "
strSQL = strSQL & "Count(a.CASE_NUMBER) as EQTY_COUNT, "
strSQL = strSQL & "Sum(a.MT_LOAN_AMOUNT) as EQTY_VOL, "
strSQL = strSQL &
"DECODE(a.MT_LOAN_SUB_TYPE,'00',Sum(a.MT_LOAN_AMOUNT *.0375),'38',
Sum(a.MT_LOAN_AMOUNT *.018)) as EQTY_REV, "
strSQL = strSQL & "a.REPORT_PERIOD "
strSQL = strSQL & "FROM PROFOWNER.PROF_MT_DAILY_PRICING_DATA_NEW
a, MTFOWNER.MTF_CUST_VIEW b, ICPOWNER.ICP_ED_ORG_MANAGERIAL_VW c "
strSQL = strSQL & "And b.MTF_PRODUCT_OPTION <> '130' "
strSQL = strSQL & "And a.REPORT_PERIOD = TO_DATE(" & rpt_date & ",
'MM/DD/YYYY') "
strSQL = strSQL & "GROUP BY a.MT_RCE_ORG_ID, a.MT_LOAN_SUB_TYPE,
a.REPORT_PERIOD "

rst1.CursorLocation = adUseClient
rst1.Open strSQL, cnn2

rst2.Open "tbl_equity_all", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
rst1.MoveFirst
Do While Not rst1.EOF
rst2.AddNew
For k = 0 To rst1.Fields.Count - 1
rst2.Fields(k).Value = rst1.Fields(k).Value
rst2.Update
Next k
rst1.MoveNext
Loop
End Sub

And for the Access queries I use (Docmd.OpenQuery "queryname").
However, I do have one update query late in the process where an
Oracle table is joined to a local Access table. As a result, the user
is prompted with the Oracle ODBC Driver Connect form thus interupting
the process. How can I avoid this? I want to create all necessary
connections through the form on the front end. I thought that either
using the existing connection or establishing a new one would work,
but I have not had any success. Any help/explanation would be very
much appreciated.

Zachary
Click to expand...

When you first create the link to the oracle table make sure you check
off the save password box.
 
hi Zach,




You are mixing two connection techniques, which requires two logins to
Oracle.

As you said, you need to JOIN a Jet table with an Oracle table, you
should use only ODBC/linked tables.

For direct Oracle access use passthrough queris. Set their Connect
property to a working linked table before executing/using it, and no
credentials are required as Jet uses the already established session:

CurrentDb.QueryDefs.Item("passthroug").Connect = _
CurrentDb.TableDefs.Item("linkedTable").Connect

Use appropriate Orcacle views to prefilter the data.

mfG
--> stefan <--
Click to expand...

Hi Stefan: Thanks for the info. Yes, the Oracle tables are linked and
I understand your point that I'm mixing up the connections. The SPT
code listed above uses cnn2 as a public ADODB.Connection variable and
that works fine, but I'm not sure how to (or if I can) leverage the
cnn2 connection for querying a Jet table(s) that is/are joined to
Oracle(s) table. I'm not following your syntax above and where is
would occur and/or if I need to add something to the collection first.
Thanks...
 
hi Zach,

Hi Stefan: Thanks for the info. Yes, the Oracle tables are linked and
I understand your point that I'm mixing up the connections. The SPT
code listed above uses cnn2 as a public ADODB.Connection variable and
that works fine, but I'm not sure how to (or if I can) leverage the
cnn2 connection for querying a Jet table(s) that is/are joined to
Oracle(s) table. I'm not following your syntax above and where is
would occur and/or if I need to add something to the collection first.
Click to expand...
Maybe I was to short in explaining the basic idea:

You like to suppress the login screen for your user. This can you easily
do with an pass through query:


Credentials = ";pwd=..;uid=.."
Set qd = New DAO.QueryDef
With qd
.Connect = CurrentDb.TableDefs.Item("linkedTable").Connect + _
Credentials
.NAME = AName
.ReturnsRecords = AReturnsRecords
.SQL = Statement
End With
CurrentDbC.QueryDefs.Append qd
Set qd = Nothing

Before opening any tables execute the upper pt query. All linked tables
having the same connection string will use the session to Oracle with
these credentials, thus no more login screen.


mfG
--> stefan <--
 
you can get around this by:

a) getting rid of Oracle
b) gettign rid of MDB

and then you're left with Access Data Projects and SQL Server

it's a much much much better platform


using DAO against Oracle is just flat out retarded
 
Back
Top