Solved

Oracle Connection Passing to functions in MSAccess VBA

Posted on 2011-09-28
3
574 Views
Last Modified: 2012-06-22
I am running several functions that connect to an oracle db and run sql. I am trying to figure out how to open the ODBC connection to oracle once, and pass it to each of the functions that need to run sql on the oracle db.  Currently I am establishing the connection in each function, causing the user to need to enter a password with each new connection.

Thanks for any help,
Lynn
Sub RunReport(reportType As String, sTime As Variant)

Dim strTime As Variant

strTime = sTime
    
      If reportType = "Terminating" Then
    
        
      ' Pass ACSCon
          'Get terminating accounts from ACCOUNTS
          Call GetACSAccounts(strACSConnectionACS(strServerName))
         
         'Get Terminating dates from TRANS
          Call GetTRANS(strACSConnectionACS(strServerName))
          
        ' Pulling TRAN names of tranDate userid
          Call GetTranNames(strACSConnectionACS(strServerName))
      
      Else
         
         'Get exceptions accounts from ACCOUNTS
          Call GetACSExceptions(strACSConnectionACS(strServerName))
      
      End If

    'Update Regions
    Call getRegions(reportType)
    
    'get user filter selections on form
    Call GetUserFilter(reportType, strTime)

End Sub



''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Procedure:   GetACSAccounts
'
' Parameters:
'           strConnection - The ACS Oracle database Connection string
'
' PreConditions:
'           Requires that table [tblAccount] resides in database. ACS database records
'           are inserted into tblAccount.
'
' PostCondition:
'           Records from ACS tables are inserted into tblAccount
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Sub GetACSAccounts(strACSConnectionACS As String)

    Dim ACSCon As New ADODB.Connection
    Dim ACSrs As New ADODB.Recordset
    Dim strSQL As String
    
    varReturn = SysCmd(acSysCmdSetStatus, "Termination Report - Please wait...pulling ACS Account data")
    
    'clear table for new report data
    DoCmd.SetWarnings False
    DoCmd.runSQL ("DELETE tblAccount.* FROM tblAccount")
    DoCmd.SetWarnings True
    
    strSQL = "SELECT"
	‘big bunches of sql here....

    ACSCon = strACSConnectionACS
    ACSCon.Open
    ACSrs.CursorLocation = adUseClient
    
    ACSrs.Open strSQL, ACSCon, adOpenStatic, adLockReadOnly
    Set ACSCon = Nothing
    
    If Not ACSrs.BOF And Not ACSrs.EOF Then
        ACSrs.MoveFirst
        varReturn = SysCmd(acSysCmdSetStatus, "Termination Report - Please wait...inserting records into local table")
             
        Do Until ACSrs.EOF
            
            Call InsertAccount(ACSrs.Fields("InternalAccountNumber"), ACSrs.Fields("AccountKey"), _
            ACSrs.Fields("AccountSiteKey"), ACSrs.Fields("AccountShortName"), _
            ACSrs.Fields("AccountManagerKey"), ACSrs.Fields("AccountManagerFirstName"), _
            ACSrs.Fields("AccountManagerLastName"), ACSrs.Fields("AccountTFMKey"), ACSrs.Fields("TFMFirstName"), _
            ACSrs.Fields("TFMLastName"), ACSrs.Fields("AccountTrustReviewKey"), ACSrs.Fields("TrustReviewFirstName"), _
            ACSrs.Fields("TrustReviewLastName"), _
            ACSrs.Fields("TerminationFirstName"), ACSrs.Fields("TerminationLastName"), ACSrs.Fields("AccountStatus"), _
            ACSrs.Fields("AccountCondition"), _
            ACSrs.Fields("AccTermDate"), ACSrs.Fields("TermReason"), _
            ACSrs.Fields("ACTIVETICKLERCOUNT"), ACSrs.Fields("UNRELEASEDTICKLERS"))
            
         ACSrs.MoveNext
        Loop
    End If
    
     ACSCon.Close
    
    Set ACSCon = Nothing
    Set ACSrs = Nothing
    varReturn = SysCmd(acSysCmdSetStatus, "Termination Report - Done pulling ACS Account data")
End Sub

Open in new window

0
Comment
Question by:Lambel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 500 total points
ID: 36794880
This is how I do it in SQL Server .... Oracle will probably use the same concept.  You just need to find the format of the Oracle connection string.  

Basically in Access you will open a QueryDef for your SQL Query and set the connection string as shown below.

Dim qdef As QueryDef
Dim StrSQLConn as String
Set qdef = CurrentDb.QueryDefs("YourQueryNameHere")

strSQLConn = "ODBC;Driver={SQL Server};Server=SSSSS;Database=DDDDD;Uid=UUUUU;Pwd=PPPPP;"

qdef.Connect = strSQLConn
qdef.Close

Then run your query.

ET
0
 

Author Closing Comment

by:Lambel
ID: 36817599
Thanks, this got me on the right track.
Lynn
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 36817655
Appreciate the points....

ET
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
construct a query sql 11 42
microsoft Access form help 5 12
mysql workbecn having problems to export tables to cvs 4 27
Using a combo box to search a form. 3 35
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question