Stored procedure and Oracle (1 Viewer)

[tranchemontaigne]

I have a block of VBA code that provides search functionality within an MS Access 2000 database. The end user needs to query a linked Oracle data table that I cannot access. I was able to create link to the Oracle 11g table, then copy the table structure and data to a local MS Access table for development purposes. At my desk and at the end user's desk we can get the search function to run perfectly when creating and running a simple query against the local MS Access table. When the same code block is run against the linked Oracle table it fails without catching an error.

Though I'm not sure if this is relevant, the search function does employ wild cards and uses them to define the results recordset. As you would expect, within the VBA code I am using percentage signs (%) for wild card characters. This decision was made because I have never been able to get the MS Access engine to create a recordset through VBA code using an asterisk (*) character. Attempting to define a recordset in VBA using an asterisk (*) as a wild card character in a SQL statement has always failed for me without generating an error.

Has anyone any tips for resolving this problem? So long as I'm unable to directly test any calls to Oracle, I want to make sure that I have thoroughly investigated solutions before I attempt to deliver an untested fix.



NOTE: Though I am physicaly located in the same complex as the customer and authorized to view the contents of the Oracle table, I am not able to receive permissions to connect to the actual data store, and am also prevented from creating a local Oracle Express instance.

 

[mdlueck]

As for the wild card character difference, even in Access you will encounter the difference. DAO type DB objects use the * character while ADO type DB objects use the % character.

It sounds like you are correctly intending to send the Oracle DB a pass-through query. Access will not be able to process the exact query which Oracle will execute, and the other way around is equally true. It is similar to the difference between USA English and UK English.

 

[tranchemontaigne]

Thanks for the response. I fear I may have mistitled this post. I'm not using an Oracle stored procedure and did not create a pass through query. I create a dynamic SQL statement and then execute it. Here are my code snippets:

Form module declarations

Option Compare Database
Option Explicit
 
Const gstrObject As String = "frmDeathRecordsByFileNumberSearch"

Form module click event
(1) performs error checking
(2) builds a dynamic SQL statement
(3) passes SQL statement through to another function

Private Sub cmdCustomSearch_Any_Click()
'//////////////////////////////////////////////////////////////////
'// Revision History
'// Date Editor Description
'// ---------------------------------------------------
'// 14 Nov 2012 Tranchemontaingne -Added field validation checks
'// 28 Dec 2012 Tranchemontaingne -Added error handling
'//
'//////////////////////////////////////////////////////////////////
 
On Error GoTo Err_cmdCustomSearch_Any_Click
 
'Dim strCriteria As String
Dim strFirstName As String
Dim strLastName As String
Dim strDOB As String
Dim strSSN As String
Dim lngCheck As Long
Dim strSQL As String
Dim stDocName As String
Dim strError As String
 
 
'strCriteria = "Like " & Chr(147) & "*" & [Forms]![frmMainMenu]![frmSubform]![txtLastName] & "*" & Chr(147)
'MsgBox strCriteria
'check for search criteria
lngCheck = 0
If IsNull(Forms![frmMainMenu]![frmSubform].Form![txtFirstName].Value) = False Then
 
If [Forms]![frmMainMenu]![frmSubform].Form![chkFirstName].Value = -1 Then
lngCheck = lngCheck + 1
strFirstName = Forms![frmMainMenu]![frmSubform].Form![txtFirstName].Value
End If
 
End If
 
If IsNull(Forms![frmMainMenu]![frmSubform].Form![txtLastName].Value) = False Then
 
If [Forms]![frmMainMenu]![frmSubform].Form![chkLastName].Value = -1 Then
lngCheck = lngCheck + 1
strLastName = Forms![frmMainMenu]![frmSubform].Form![txtLastName].Value
End If
 
End If
 
If IsNull(Forms![frmMainMenu]![frmSubform].Form![txtDOB].Value) = False Then
 
 
If [Forms]![frmMainMenu]![frmSubform].Form![chkDOB].Value = -1 Then
lngCheck = lngCheck + 1
strDOB = Forms![frmMainMenu]![frmSubform].Form![txtDOB].Value
End If
End If
 
If IsNull(Forms![frmMainMenu]![frmSubform].Form![txtSSN].Value) = False Then
 
 
If [Forms]![frmMainMenu]![frmSubform].Form![chkSSN].Value = -1 Then
lngCheck = lngCheck + 1
strSSN = Forms![frmMainMenu]![frmSubform].Form![txtSSN].Value
End If
End If
 
'warn if no criteria has been specified
If lngCheck = 0 Then
MsgBox "Please enter search criteria", vbExclamation, "Missing Information"
Else
'check for Search matches
'direct output from MS Access report to MS Excel file
strSQL = "SELECT"
strSQL = strSQL & " "
strSQL = strSQL & "BVRODS_BVR_DEATH_TBL.ST_FILE_NBR"
strSQL = strSQL & ", "
strSQL = strSQL & "BVRODS_BVR_DEATH_TBL.DECD_FRST_NME"
strSQL = strSQL & ", "
strSQL = strSQL & "BVRODS_BVR_DEATH_TBL.DECD_MIDD_NME"
strSQL = strSQL & ", "
strSQL = strSQL & "BVRODS_BVR_DEATH_TBL.DECD_LST_NME"
strSQL = strSQL & ", "
strSQL = strSQL & "BVRODS_BVR_DEATH_TBL.ALIAS_NME_FL"
strSQL = strSQL & ", "
strSQL = strSQL & "BVRODS_BVR_DEATH_TBL.ALIAS_FRST_NME"
strSQL = strSQL & ", "
strSQL = strSQL & "BVRODS_BVR_DEATH_TBL.ALIAS_MIDD_NME"
strSQL = strSQL & ", "
strSQL = strSQL & "BVRODS_BVR_DEATH_TBL.ALIAS_LST_NME"
strSQL = strSQL & ", "
strSQL = strSQL & "BVRODS_BVR_DEATH_TBL.DECD_SEX"
strSQL = strSQL & ", "
strSQL = strSQL & "BVRODS_BVR_DEATH_TBL.DECD_SSN"
strSQL = strSQL & ", "
strSQL = strSQL & "BVRODS_BVR_DEATH_TBL.DECD_AGE_YR"
strSQL = strSQL & ", "
strSQL = strSQL & "BVRODS_BVR_DEATH_TBL.DECD_BRTH_DT"
strSQL = strSQL & " "
strSQL = strSQL & "FROM"
strSQL = strSQL & " "
strSQL = strSQL & "BVRODS_BVR_DEATH_TBL"
strSQL = strSQL & " "
strSQL = strSQL & "WHERE"
strSQL = strSQL & " "
If Me![chkDOB].Value = -1 Then
strSQL = strSQL & "("
strSQL = strSQL & "("
strSQL = strSQL & "(BVRODS_BVR_DEATH_TBL.DECD_BRTH_DT)="
strSQL = strSQL & Chr(35) & [Forms]![frmMainMenu]![frmSubform]![txtDOB].Value & Chr(35)
strSQL = strSQL & ")"
strSQL = strSQL & ")"
strSQL = strSQL & " "
strSQL = strSQL & "OR"
strSQL = strSQL & " "
End If
If Me![chkLastName].Value = -1 Then
strSQL = strSQL & "("
strSQL = strSQL & "("
strSQL = strSQL & "(BVRODS_BVR_DEATH_TBL.DECD_LST_NME) Like "
strSQL = strSQL & Chr(34) & [Forms]![frmMainMenu]![frmSubform]![txtLastName] & Chr(37) & Chr(34)
strSQL = strSQL & ")"
strSQL = strSQL & ")"
strSQL = strSQL & " "
strSQL = strSQL & "OR"
strSQL = strSQL & " "
End If
If Me![chkFirstName].Value = -1 Then
strSQL = strSQL & "("
strSQL = strSQL & "("
strSQL = strSQL & "(BVRODS_BVR_DEATH_TBL.DECD_FRST_NME) Like "
strSQL = strSQL & Chr(34) & [Forms]![frmMainMenu]![frmSubform]![txtFirstName] & Chr(37) & Chr(34)
strSQL = strSQL & ")"
strSQL = strSQL & ")"
strSQL = strSQL & " "
strSQL = strSQL & "OR"
strSQL = strSQL & " "
End If
If Me![chkSSN].Value = -1 Then
strSQL = strSQL & "("
strSQL = strSQL & "("
strSQL = strSQL & "(BVRODS_BVR_DEATH_TBL.DECD_SSN) Like "
strSQL = strSQL & Chr(34) & [Forms]![frmMainMenu]![frmSubform]![txtSSN] & Chr(37) & Chr(34)
strSQL = strSQL & ")"
strSQL = strSQL & ")"
End If
If Right(strSQL, 3) = "OR " Then
strSQL = Left(strSQL, Len(strSQL) - 3)
End If
strSQL = strSQL & ";"
 
Call fnExportToExcel_ADO(strSQL)
 
End If
 
 
Exit_cmdCustomSearch_Any_Click:
Exit Sub
 
Err_cmdCustomSearch_Any_Click:
strError = "cmdCustomSearch_Any_Click encountered an error" & Chr(10) & Chr(13) & _
"Error: " & Err.Number & ": " & Err.Description
 
Debug.Print strError
 
MsgBox strError, vbCritical, gstrObject & " " & "Error"
 
Call fnLogError(gstrObject, "cmdCustomSearch_Any_Click", strError)
Resume Exit_cmdCustomSearch_Any_Click
 
End Sub

second modules declarations

Option Compare Database
Option Explicit
 
Const gstrObject As String = "Excel_Module"

fnExportToExcel_ADO(strSQL)
uses the received SQL statement to build an ADODB recordset
and then use that recordset to populate a MS Excel spreadsheet

Public Function fnExportToExcel_ADO(strSQL As String, _
Optional ByRef lnX As Long = 1, _
Optional ByRef lnY As Long = 1, _
Optional ByRef lnN As Long = 1, _
Optional ByRef lnM As Long = 1, _
Optional blHeaders As Boolean = True) As Worksheet
'//////////////////////////////////////////////////////////////////
'// Function: fnExportToExcel_ADO
'//////////////////////////////////////////////////////////////////
'// Author: Andrew Semenov
'// Created:
'// 2003 [URL]http://www.zmey.1977.ru/Access_To_Excel.htm[/URL]
'// Modified:
'// Date Editor Description
'// ==========================================
'// 25 Aug 2009 Tranchemontaigne Adopted for Death database
'// 28 Dec 2012 Tranchemontaigne Added error handling
'//
'//////////////////////////////////////////////////////////////////
'// Description:
'// ADVANTAGE: Very fast, reliable and adjustable
'// DISADVANTAGE: This method is much slowed by the necessity
'// to transpose matrix received by getrows. Unfortunately,
'// getrows puts values in transposed way. If it can be
'// avoided some way, speed will increase much.
'// FEATURES: you have to specify X and y - top left cell, and
'// in N and M variables you receive the height and width of
'// range received. Set Headers variable to true if you need
'// column headers.
'// ERRORS: This method is error independent - error values
'// just ignored. The components of this solution are ADODB
'// recordset - used to retrieve records values from query and
'// put them into array, and ten array is being transposed and
'// put into MS Excel Range.
'//
'//////////////////////////////////////////////////////////////////
'// Requirements:
'// Microsoft Visual Basic for Applications
'// Microsoft Access 9.0 Object Library
'// Microsoft Excel 9.0 Object Library
'// Microsoft ActiveX 2.8 Data Objects Library
'//
'//////////////////////////////////////////////////////////////////
'// Input:
'// Variable Type Description
'// ===========================================================
'// strSQL string sql statement describing record source
'// lnX long top left cell X position
'// lnY long top left cell Y position
'// lnN long height of range received
'// lnM long width of range received
'// blHeaders boolean yes/no setting for column headers
'//
'//////////////////////////////////////////////////////////////////
On Error GoTo Err_fnExportToExcel_ADO
 
'Error tracking variable
Dim strCodeBlock As String
Dim strError As String
 
'Create Excel spreadsheet variables
Dim XL As Object
Dim WB As Workbook
Dim WS As Worksheet
 
'Export to Excel variables
Dim rst1 As New ADODB.Recordset
Dim rsCon As New ADODB.Connection
Dim lngRSLoopCount As Long
Dim varA As Variant
Dim varC() As Variant
'Dim intI As Integer
Dim intJ As Integer
Dim intK As Integer
'Dim intL As Integer
 
strCodeBlock = "define recordset and check for results"
Debug.Print Chr(10) & Chr(13) & "strSQL: " & strSQL
Set rst1 = New ADODB.Recordset
rst1.Open strSQL, CurrentProject.Connection ', adOpenForwardOnly, adLockOptimistic
 
lngRSLoopCount = 0
While rst1.EOF = False
lngRSLoopCount = lngRSLoopCount + 1
rst1.MoveNext
Wend
Debug.Print Chr(10) & Chr(13) & "RecordCount: " & rst1.RecordCount
 
If lngRSLoopCount = 0 Then
rst1.Close
MsgBox "No records detected"
Exit Function
Else
rst1.MoveFirst
End If
 
 
strCodeBlock = "Create MS Excel document only if matching records are found"
'create MS Excel file
'If WS = Nothing Then
Set XL = CreateObject("excel.application")
 
'set number of worksheets in workbook
XL.SheetsInNewWorkbook = 1
 
'make MS Excel file visible
XL.Visible = True
 
'add defined number of worksheet pages to workbook
Set WB = XL.Workbooks.Add
 
Set WS = WB.Worksheets(1)
'End If
 
 
strCodeBlock = "determine result set size"
varA = rst1.GetRows()
ReDim varC(UBound(varA, 2), UBound(varA, 1))
 
strCodeBlock = "populate an array with cell values"
For intK = 0 To UBound(varA, 1)
For intJ = 0 To UBound(varA, 2)
varC(intJ, intK) = varA(intK, intJ)
Next intJ
Next intK
 
strCodeBlock = "matrix transposition"
lnN = UBound(varA, 2) + 1
lnM = UBound(varA, 1) + 1
 
WS.Range(WS.Cells(lnY, lnX), WS.Cells(lnN + lnY - 1, lnM + lnX - 1)) = varC
 
strCodeBlock = "column headers inserted if necessary"
If blHeaders = True Then
WS.Range(WS.Cells(lnY, lnX), WS.Cells(lnN + lnY - 1, lnM + lnX - 1)).Rows(1).Insert
For intJ = 0 To lnM - 1
WS.Cells(lnY, intJ + lnX).Value = rst1.Fields(intJ).Name
Next intJ
 
End If
 
 
Exit_fnExportToExcel_ADO:
rst1.Close
Set rst1 = Nothing
Exit Function
 
Err_fnExportToExcel_ADO:
strError = "Error experienced within " & gstrObject & ": fnExportToExcel_ADO" & Chr(10) & _
"CodeBlock: " & strCodeBlock & Chr(10) & _
Err.Number & ": " & Err.Description
 
Debug.Print strError
 
MsgBox strError, vbCritical, gstrObject & " " & "Error"
 
Call fnLogError(gstrObject, "fnExportToExcel_ADO Function", strError)
 
Resume Next
 
End Function

And for completion, here are functions called in the previous functions

Option Compare Database
Option Explicit
 
Const gstrObject As String = "ErrorLog Module"
 
Public Function fnLogError(strObject As String, strCode As String, _
strMessage As String)
'//////////////////////////////////////////////////////////////////
'// Function: fnLogError
'//////////////////////////////////////////////////////////////////
'// Author: Tranchemontaigne
'//////////////////////////////////////////////////////////////////
'// Description:
'// If a function, procedure, or subroutine produces an error
'// record the error in an error log table
'//
'//////////////////////////////////////////////////////////////////
'// Revision History
'// Date Editor Description
'// ---------------------------------------------------
'// 28 Dec 2012 Tranchemontaigne -Modified error handling
'//
'//////////////////////////////////////////////////////////////////
'// Requirements:
'// Microsoft Visual Basic for Applications
'// Microsoft Access 9.0 Object Library
'// fnGet_UserID (Windows API module)
'// fnRunSQL (Error Log module)
'//
'//////////////////////////////////////////////////////////////////
'// Input:
'// Variable Description
'// ========================================
'// gstrObject Module object producing the error
'// gstrCode Code block within module producing error
'// gstrMessage Error message
'//
'//////////////////////////////////////////////////////////////////
On Error GoTo Err_fnLogError
 
 
Dim strlogin As String
Dim dtTime As Date
Dim strINSERT As String
Dim strVALUES As String
Dim strSQL As String
Dim strError As String
 
strlogin = fnGet_userID
dtTime = Now()
strINSERT = "INSERT INTO "
strINSERT = strINSERT & "t99_ErrorLog "
strINSERT = strINSERT & "( "
strINSERT = strINSERT & "[t99_Time], "
strINSERT = strINSERT & "[t99_Login], "
strINSERT = strINSERT & "[t99_Object], "
strINSERT = strINSERT & "[t99_Code], "
strINSERT = strINSERT & "[t99_Message] "
strINSERT = strINSERT & ") "
 
strVALUES = "VALUES "
strVALUES = strVALUES & "("
strVALUES = strVALUES & "#" & dtTime & "#"
strVALUES = strVALUES & ", "
strVALUES = strVALUES & Chr(34) & strlogin & Chr(34)
strVALUES = strVALUES & ", "
strVALUES = strVALUES & Chr(34) & strObject & Chr(34)
strVALUES = strVALUES & ", "
strVALUES = strVALUES & Chr(34) & strCode & Chr(34)
strVALUES = strVALUES & ", "
strVALUES = strVALUES & Chr(34) & strMessage & Chr(34)
strVALUES = strVALUES & ")"
strVALUES = strVALUES & ";"
 
strSQL = strINSERT & " " & strVALUES
 
'DoCmd.RunSQL strSQL
 
Call fnRunSQL(strSQL)
 
Exit_fnLogError:
Exit Function
 
Err_fnLogError:
strError = "fnLogError encountered an error" & Chr(10) & Chr(13) & _
"Error: " & Err.Number & ": " & Err.Description & Chr(10) & Chr(13) & _
"strSQL: " & strSQL
 
Debug.Print strError
 
MsgBox strError, vbCritical, gstrObject & " " & "Error"
 
Call fnLogError(gstrObject, "ErrorLog Function", strError)
 
Resume Exit_fnLogError
 
End Function

Option Compare Database
Option Explicit
 
Const gstrObject As String = "RunSQL Module"
 
Public Function fnRunSQL(strSQL As String)
'//////////////////////////////////////////////////////////////////
'// Function: fnRunSQL
'//////////////////////////////////////////////////////////////////
'// Description:
'// simplifies number of characters needed to be typed to
'// execute an SQL statement
'//
'//////////////////////////////////////////////////////////////////
'// Revision History
'// Date Editor Description
'// ---------------------------------------------------
'// 06 Jun 2008 Tranchemontaigne -Created
'// 28 Dec 2012 Tranchemontaigne -Added error handling
'//
'//////////////////////////////////////////////////////////////////
'// Requirements:
'// Microsoft Visual Basic for Applications
'// Microsoft Access 9.0 Object Library
'// fnLogError (ErrorLog module)
'//
'//////////////////////////////////////////////////////////////////
On Error GoTo Err_fnRunSQL
 
Dim strError As String
 
With DoCmd
.SetWarnings False
.RunSQL strSQL
.SetWarnings True
End With
 
Exit_fnRunSQL:
Exit Function
 
Err_fnRunSQL:
strError = "fnRunSQL encountered an error" & Chr(10) & Chr(13) & _
"Error: " & Err.Number & ": " & Err.Description & _
"strSQL: " & strSQL
 
Debug.Print strSQL
 
MsgBox strError, vbCritical, gstrObject & " " & "Error"
 
Call fnLogError(gstrObject, "fnRunSQL", strError)
Resume Exit_fnRunSQL
 
End Function

Option Compare Database
Option Explicit
 
Const gstrObject As String = "Windows API Module"
 
Function fnGet_userID()
On Error GoTo Err_fnGet_userID
'//////////////////////////////////////////////////////////
'// Revision History
'// Date Editor Description
'// ---------------------------------------------------
'// 28 Dec 2012 Tranchemontaigne -Added error handling
'//
'//////////////////////////////////////////////////////////
 
Dim user_id As String
Dim strError As String
 
user_id = Environ("UserName")
 
fnGet_userID = user_id
 
 
Exit_fnGet_userID:
Exit Function
 
Err_fnGet_userID:
strError = "fnGet_userID encountered an error" & Chr(10) & Chr(13) & _
"Error: " & Err.Description
 
MsgBox strError, vbCritical, "fnGet_userID"
 
Call fnLogError(gstrObject, "fnGet_userID", strError)
Resume Exit_fnGet_userID
 
 
fnGet_userID = "Failure"
Resume Exit_fnGet_userID
 
End Function

 

[mdlueck]

I'm not using an Oracle stored procedure and did not create a pass through query.

You should at the very least be using a pass-through query. Otherwise there is no telling what sort of SQL will be handed to Oracle by Access. It will not be the SQL you entered as Access's query optimizer will be invoked before the query gets handed off to Oracle.

My comment remains the same... you are going to have a very difficult time arriving at a SQL string which would possibly work correctly when communicating with both databases.

 

[jdraw]

I agree with Michael - use a pass through query. And you must use the Oracle sql syntax in the pass through query.

Also, the code tags for this forum use [ and ] .

 

[tranchemontaigne]

Michael,

Why would the query optimizer allow the SQL statement to be run from a query created at design time (QUERIES ==> NEW QUERY ==> ==> SQL VIEW), but not when the SQL statement is run from within a VBA module?

I'm still a little confused.

 

[tranchemontaigne]

any tips on how to define a recordset using a pass through query?

 

[mdlueck]

In your prior posting, you were using ADO objects, so I will stick to the same for my examples:

First you will need an ADO.Connection object to connect to the SQL BE DB:

ADO.Connection object to SQL BE DB
http://www.access-programmers.co.uk/forums/showthread.php?t=231923#post1184259

Then you may use that object to pass to the ADO objects which actually run the query in place of passing CurrentProject.Connection, which is for the Access FE DB.

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

The different ADO.Connection object instructs Access / ADO which database to execute the SQL in the context of.