none
Running an MS Access Update query which calls a public function RRS feed

  • Question

  • Hi

    I am running an MS Access Update query as below from vb.net as below;

            Dim strConn As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = F:\Events Data\Events Data.mdb;"
            Dim conn As OleDbConnection = New OleDbConnection(strConn)
            Dim cmd As New OleDbCommand("UpdateVenuePostcode", conn)
    
            cmd.CommandType = CommandType.StoredProcedure
    
            conn.Open()
    
            cmd.ExecuteNonQuery()
    
            conn.Close()

    The update query UpdateVenuePostcode is as below;

    UPDATE Events SET Events.EventVenuePostcode = GetPostcode([Event Venue])
    WHERE (((Events.EventVenuePostcode) Is Null Or (Events.EventVenuePostcode)=""));

    The public function GetPostcode is defined within an MS Access module. When I run vb.net code I get below error;

    System.Data.OleDb.OleDbException was unhandled
      ErrorCode=-2147217900
      HResult=-2147217900
      Message=Undefined function 'GetPostcode' in expression.
      Source=Microsoft JET Database Engine

    What is the way to correct this and run the query successfully?

    Thanks

    Regards
    Saturday, December 14, 2013 2:57 PM

Answers

  • Hello,

    As far as I know, in interactive Access, the Access Expression Service takes care of providing our access to user-defined and VBA functions, but the Access Expression Service is not available from outside Access. When accessing Jet/ACE data via ODBC or OLEDB, only a limited number of functions are available. Since GetPostcode is a custom function, it is not one of them.

    The solution for this, we could have a try to needed to set a registry key to turn off "Sandboxed Mode".

    http://office.microsoft.com/en-us/access-help/use-sandbox-mode-in-access-2007-HA010167429.aspx

    Hope that helps.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 16, 2013 3:36 AM
    Moderator

All replies

  • Hello,

    As far as I know, in interactive Access, the Access Expression Service takes care of providing our access to user-defined and VBA functions, but the Access Expression Service is not available from outside Access. When accessing Jet/ACE data via ODBC or OLEDB, only a limited number of functions are available. Since GetPostcode is a custom function, it is not one of them.

    The solution for this, we could have a try to needed to set a registry key to turn off "Sandboxed Mode".

    http://office.microsoft.com/en-us/access-help/use-sandbox-mode-in-access-2007-HA010167429.aspx

    Hope that helps.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 16, 2013 3:36 AM
    Moderator
  • It's pretty much what Fred said, you cannot run custom VBA functions through Jet or ACE OLEDB QueryDefs unless they execute from Microsoft Access. The below MS KB article describes what functions are available:

    How to configure Jet 4.0 to prevent unsafe functions from running in Access 2000 and Access 2002

    Not sure what GetPostcode does, but it may have to be rewritten as a nested SQL SELECT query instead of a VBA code function.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, December 16, 2013 12:12 PM
  • Hi Paul

    Thanks. GetPostcode extracts UK postcode from a string. Code below.

    Regards 

    Public Function GetPostcode(ByVal Str) As String
        Dim w       As String
        Dim j       As Long
        Dim Ptrn1
        Dim Ptrn2   As String
        Dim x
        Dim i As Long
        
        If (IsNull(Str)) Or (LTrim(Str) = "") Then
          GetPostcode = ""
          Exit Function
        End If
        
        x = Split(Replace(Str, ",", " "), " ")
        
        Ptrn1 = Array("[A-Z][0-9]", "[A-Z][0-9][0-9]", "[A-Z][A-Z][0-9]", "[A-Z][A-Z][0-9][0-9]", "[A-Z][0-9][A-Z]", "[A-Z][A-Z][0-9][A-Z]")
        
        Ptrn2 = "[0-9]*"
        
        Dim InpStr As String
        
        InpStr = Str
         
        On Error Resume Next
        For i = 0 To UBound(x)
            w = x(i)
            For j = LBound(Ptrn1) To UBound(Ptrn1)
                If Len(w) Then
                    If w Like Ptrn1(j) And x(i + 1) Like Ptrn2 Then
                        If Err.Number <> 0 Then
                            Err.Clear
                            If w Like Ptrn1(j) & Ptrn2 Then
                                GetPostcode = w
                            End If
                        Else
                            GetPostcode = w & Space(1) & x(i + 1)
                        End If
                    ElseIf w Like Ptrn1(j) Then
                        GetPostcode = w
                    End If
                End If
            Next
        Next
        
        GetPostcode = UCase(GetPostcode)
          
    End Function

    Monday, December 16, 2013 1:03 PM
  • Hi,

    This function is too complex and it is impossible to be translated to be a query sql statement.

    I recommend to use the SQLServer if you really want to use the SP function.

    Regards.

    Tuesday, December 17, 2013 7:43 AM