none
Undefined function 'Mid' in expression when calling stored query in separate database. RRS feed

  • Question

  • I have a stored query in a database that uses the Mid function in the query. When I am opening the query within the database it works perfectly.

    I am writing the application separate from that ACCDB file that contains the data and using ADODB connection to manipulate the data.

    I have a class to work with the ADODB connection object.

    The GetRecordSet:

    Public Function GetRecordset(ByVal SQLStatement As String, _
        Optional ByVal CurType As CursorTypeEnum = adOpenDynamic, _
        Optional ByVal CurLocation As CursorLocationEnum = adUseServer, _
        Optional ByVal LockType As LockTypeEnum = adLockReadOnly _
    ) As ADODB.Recordset
    '    On Error GoTo HandleErrors    'Setup how to handle any errors.
        If madodbConnection.State <> adStateOpen Then GoTo ExitHere    ' Not open
        Set GetRecordset = New ADODB.Recordset
        With GetRecordset
            Set .ActiveConnection = madodbConnection
            .CursorLocation = CurLocation
            .CursorType = CurType
            .LockType = LockType
            .Source = SQLStatement
            .Open SQLStatement
        End With
    ExitHere:        'Label to Goto to exit the function from
        Exit Function    'Exit the function
    HandleErrors:        'Label to start the ErrorHandler section
        Set GetRecordset = Nothing
        GoTo ExitHere    ' Resume at the ExitHere label

    End Function

    And the Execute function:

    Public Function Execute(ByVal Command As String, _
        Optional ByRef RecordsEffected As Long = 0, _
        Optional ByVal CommandType As CommandTypeEnum = adCmdText, _
        Optional ByVal ExecuteOption As ExecuteOptionEnum = adExecuteNoRecords) _
    As ADODB.Recordset
        On Error GoTo HandleErrors    'Setup how to handle any errors.
       
        If madodbConnection.State <> adStateOpen Then madodbConnection.OpenConnection
       
        Set Execute = madodbConnection.Execute(Command, RecordsEffected, CommandType And ExecuteOption)
       
        If Execute.RecordCount = -1 Then RecordsEffected = CountRecs(Execute)
       
    ExitHere:        'Label to Goto to exit the function from
        Exit Function    'Exit the function
    HandleErrors:        'Label to start the ErrorHandler section
        GoTo ExitHere    ' Resume at the ExitHere label

    End Function

    work properly with all of the other queries I have.

    The query in question is:

    SELECT tblReps_ID, Mid([tblReps_esmName],InStr(1,[tblReps_esmName], " ", 1) + 1 ) & ", " & Left([tblReps_esmName], InStr(1, [tblReps_esmName], " ", 1) - 1) AS SortName
    FROM tblReps
    ORDER BY Mid([tblReps_esmName], InStr(1, [tblReps_esmName], " ", 1) + 1) & ", " & Left([tblReps_esmName], InStr(1, [tblReps_esmName], " ", 1) - 1);

    The only time the error occurs when trying to return the query as the ADODB.RecordSet object

    Thursday, February 8, 2018 4:42 PM

Answers

  • Trying to find other ways to solve the problem and I used another method I made in my connection class.

    The function is SP (StoredProcedure):

    Public Function SP(StoredProcName As String, _
        Optional ByVal StoredProcedure As Boolean = True _
    ) As ADODB.Recordset
        On Error GoTo HandleErrors 'Setup how to handle any errors.
        Dim adoCommand As New ADODB.Command
        Dim cmdType As CommandTypeEnum
        On Error GoTo ESPError
        If madodbConnection.State = adStateOpen Then
            Set adoCommand = New ADODB.Command
            With adoCommand
                .ActiveConnection = madodbConnection
                .CommandText = StoredProcName
                .CommandType = adCmdStoredProc
                .CommandTimeout = 0
                 'Execute the stored procedure
                Set SP = .Execute
            End With
        Else
            'No database connection could be established, but no error
            ' was raised (should never happen).
            Set SP = Nothing
        End If
    ESPResume:
        Set adoCommand = Nothing
        Exit Function
    ESPError:
        Set SP = Nothing
        MsgBox Err, "SPwInputs"
        Resume ESPResume
    ExitHere: 'Label to Goto to exit from
        Exit Function 'Exit the procedure
    HandleErrors: 'Label to start the ErrorHandler section
        GoTo ExitHere ' Resume at the ExitHere label
    End Function

    This actually executed the query and returned everything properly.

    • Marked as answer by RSquared64 Thursday, February 8, 2018 8:40 PM
    Thursday, February 8, 2018 6:58 PM

All replies

  • You might want to check to see if you have the correct Sandbox mode setting in order to execute these functions in a SQL query outside of the Microsoft Access environment:

    Functions and properties in Access blocked by sandbox mode


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, February 8, 2018 5:06 PM
  • Paul, thanks for the hint. I did change the registry key, but it didn't help at all.

    Trust settings are also set properly.

    Everything is inside Access as well, it is just that the code and data are in separate files.

    The connectionstring is Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePath.accdb;Persist Security Info=False; if it helps any.

    Thursday, February 8, 2018 5:54 PM
  • Paul, thanks for the hint. I did change the registry key, but it didn't help at all.

    Trust settings are also set properly.

    Everything is inside Access as well, it is just that the code and data are in separate files.

    The connectionstring is Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePath.accdb;Persist Security Info=False; if it helps any.


    I'm not sure what your other application is, but have you check all of the References in the database to make sure they are OK? Sometimes bad References can cause VBA functions to fail. Also, are you using the latest version of ADO or an old version like 2.5?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, February 8, 2018 6:04 PM
  • I am using 2.8, Access 2016.

    I am using 2 MS Access ACCDB files. One contains only the program code, VBA. The second ACCDB is strictly data and queries.

    That way I can keep code and data completely separate.

    I use a ADODB.Connection variable that is public to run all the data access through. It is contained in a class which has the Execute and GetRecordSet functions in it.

    I have used the class for several years, but have never had an issue like this before.

    Like I explained, the query run fine within the Data ACCDB file. It is only when I call the Execute or GetRecordSet functions to open the stored query in the other file and store it to a ADODB.RecordSet object.

    Thursday, February 8, 2018 6:16 PM
  • Are you getting an error when you call Execute as well? If so, what is the error?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, February 8, 2018 6:21 PM
  • Yes, the same exact error - Undefined function 'Mid' in expression.

    Execute of course is just the simple command.

    The GetRecordSet  function:

    Set GetRecordset = New ADODB.Recordset
        With GetRecordset
            Set .ActiveConnection = madodbConnection
            .CursorLocation = CurLocation
            .CursorType = CurType
            .LockType = LockType
            .Source = SQLStatement
            .Open SQLStatement
        End With

    Uses the Open of the Recordset variable.

    Same error, different ways of getting the recordset.

    Thursday, February 8, 2018 6:29 PM
  • Trying to find other ways to solve the problem and I used another method I made in my connection class.

    The function is SP (StoredProcedure):

    Public Function SP(StoredProcName As String, _
        Optional ByVal StoredProcedure As Boolean = True _
    ) As ADODB.Recordset
        On Error GoTo HandleErrors 'Setup how to handle any errors.
        Dim adoCommand As New ADODB.Command
        Dim cmdType As CommandTypeEnum
        On Error GoTo ESPError
        If madodbConnection.State = adStateOpen Then
            Set adoCommand = New ADODB.Command
            With adoCommand
                .ActiveConnection = madodbConnection
                .CommandText = StoredProcName
                .CommandType = adCmdStoredProc
                .CommandTimeout = 0
                 'Execute the stored procedure
                Set SP = .Execute
            End With
        Else
            'No database connection could be established, but no error
            ' was raised (should never happen).
            Set SP = Nothing
        End If
    ESPResume:
        Set adoCommand = Nothing
        Exit Function
    ESPError:
        Set SP = Nothing
        MsgBox Err, "SPwInputs"
        Resume ESPResume
    ExitHere: 'Label to Goto to exit from
        Exit Function 'Exit the procedure
    HandleErrors: 'Label to start the ErrorHandler section
        GoTo ExitHere ' Resume at the ExitHere label
    End Function

    This actually executed the query and returned everything properly.

    • Marked as answer by RSquared64 Thursday, February 8, 2018 8:40 PM
    Thursday, February 8, 2018 6:58 PM