Re: Access violation - probably not the fault of Postgres

I wasn't able to reproduce your error on my side, but I used an Access
DB frontend connecting to an ODBC table, but take a look at my test case
and see if it fails on your side. Note, with Access you aren't allowed
to use dbOpenDynamic, so I went with the dbOpenDynaset.

Private Sub testPGDriver()

    Dim rs As Recordset

    Dim sSQL As String
    Dim l As Long

    sSQL = "SELECT * FROM public_testTable"

    Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, 0, dbOptimistic)

    rs.AddNew
    l = load_xxx_to_db(rs, "test23", 0)
    rs.update
    rs.AddNew
    l = load_xxx_to_db(rs, "test23", 1)
    rs.update

End Sub


Private Function load_xxx_to_db(table As Recordset, sData As String, i
As Integer) As Long

    If i = 0 Then
        table("testField").Value = sData
    Else
        table("testField").Value = Null
    End If
    load_xxx_to_db = 0

End Function

Paul Lambert wrote:
> David Gardner wrote:
>> Could you post the code in question? How are you initializing your
>> recordset object? Have you tried feeding the database object an
>> insert statement via the execute() function?
>>
>>
>
> Excuse the longwindedness of this... I've tried putting in all the
> relevant code and other information that I can.
>
> Recordset object is defined as thus:
>
> Private Debtor_table As Recordset
> About 30 odd times for the various different tables - this is then
> passed to the function whos code is below which receives it as
> variable name "table"
>
> Other relevant variable declarations:
>     Private autodrs_db                  As DAO.Database
>     Private autodrs_work                As DAO.Workspace
>
> Database is opened as follows:
>     Set autodrs_work = CreateWorkspace("autodrs", g_strUserName,
> g_strPWD, dbUseODBC)
>     Set autodrs_db = autodrs_work.OpenDatabase("autodrs", _
>             dbDriverNoPrompt, False, "ODBC;DATABASE=" & g_strDBName & _
>             ";UID=" & g_strUserName & ";PWD=" & g_strPWD & ";DSN=" &
> g_strDBDSN & ";")
>
>
> The code causing the error is as follows:
>
>         Call debug_message(60, "Criteria = " & criteria)
>         task = "Check for Update or Add"
>         criteria_orig = criteria
>         criteria = "Select * from " & table_name & " where " & criteria
>         Call debug_message(60, "Opening table with criteria=" & criteria)
>         Set table = autodrs_db.OpenRecordset _
>             (criteria, dbOpenDynamic, 0, dbOptimistic)
>         If table.RecordCount = 0 Then
>             Call debug_message(60, "Record not found, adding new")
>             task = "Add"
>             table.AddNew
>         Else
>             Call debug_message(60, "Record found, updating")
>             task = "Update"
>             table.Edit
>         End If
>
>         lngStatusDB = load_xxx_to_db(table_name, table, keyname,
> keyname2, keyname3, keyname4, keyname5)
>         Call debug_message(60, "  - load_xxx_to_db exit status " +
> Str(lngStatusDB))
>         If lngStatusDB = 0 Then
>             Call debug_message(60, "  + updating table")
>             table.Update
>             Call debug_message(60, "  - updating table")
>         Else
>             table.CancelUpdate
>             load_xxx = lngStatusDB
>             GoTo subroutine_exit
>         End If
>
> The line "table.Update" is where the access violation is occuring. As
> explained before the error only occurs if the update is adding a new
> record to the table, updating existing records works fine.
>
> The function load_xxx_to_db called just before the update basically
> loops through the message received and puts the data into the
> appropriate field in the "table" buffer - the code is as follows:
>
> Private Function load_xxx_to_db(table_name As String _
>                             , table As Recordset _
>                             , keyname As String _
>                             , keyname2 As String _
>                             , keyname3 As String _
>                             , keyname4 As String _
>                             , keyname5 As String) As Long
>
>     Dim ddmmyy                      As String
>
>     On Error GoTo error_trap
>
>     indexx = key_id_field + 1
>     Call debug_message(80, "  + load_xxx_to_db")
>     If table_name = "Employees" Then
>         'Last 60 fields of employee record are loaded to a different
> table, bypass them in this load.
>         item_count = item_count - 60
>     End If
>
>     ' The following section sets all the fields from the DMQ message
> into the appropriate fields in the database.
>     Do Until (indexx > item_count)
>         Select Case field_type(indexx)
>             'Straight text/string.
>             Case "T"
>                     Call debug_message(90, "     + load_xxx_to_db >
> Setting " & _
>                                             field_name(indexx) &
> ".value to " & _
>                                             field_contents(indexx))
>                     table(field_name(indexx)).value _
>                          = field_contents(indexx)
>             'Date in the formate dd-mmm-yyyy
>             Case "X", "J", "I", "E"
>                 If ((field_contents(indexx) = "") Or
> (field_contents(indexx) = "00000000000")) Then
>                     'Yes I know we shouldn't use Nulls, but this is
> replicating another database not designed/managed by me
>                     'and I can't change this fact.
>                     Call debug_message(90, "     + load_xxx_to_db >
> Setting " & _
>                                             field_name(indexx) &
> ".value to Null")
>                     table(field_name(indexx)).value = Null
>                 Else
>                     Call debug_message(90, "     + load_xxx_to_db >
> Setting " & _
>                                             field_name(indexx) &
> ".value to " & _
>                                             field_contents(indexx))
>                     table(field_name(indexx)).value _
>                         = field_contents(indexx)
>                 End If
>             'Time
>             Case "V"
>                 Call debug_message(90, "     + load_xxx_to_db >
> Setting " & _
>                                         field_name(indexx) & ".value
> to " & _
>                                         field_contents(indexx))
>                 table(field_name(indexx)).value _
>                         = cvt_time(field_contents(indexx))
>             'Numeric
>             Case "B", "W", "L", "F", "M", "1", "2", "3", "4", "5",
> "6", "7", "8", "9"
>                     If (IsNumeric(field_contents(indexx))) Then
>                         Call debug_message(90, "     + load_xxx_to_db
> > Setting " & _
>                                                 field_name(indexx) &
> ".value to " & _
>                                                 field_contents(indexx))
>                         table(field_name(indexx)).value _
>                                     = Val(field_contents(indexx))
>                     Else
>                         'Yes I know we shouldn't use Nulls, but this
> is replicating another database not designed/managed by me
>                         'and I can't change this fact.
>                         Call debug_message(90, "     + load_xxx_to_db
> > Setting " & _
>                                                 field_name(indexx) &
> ".value to Null")
>                         table(field_name(indexx)).value = Null
>                     End If
>             'Other unknown data type.
>             Case Else
>                 Call log_load_error(table_name, "Unsupported data type")
>                 load_xxx_to_db = -10
>                 GoTo subroutine_exit
>         End Select
>         indexx = indexx + 1
>     Loop
>     load_xxx_to_db = 0
> subroutine_exit:
>     Exit Function
>
> error_trap:
>       Dim MyError As Error
>       For Each MyError In DBEngine.Errors
>         With MyError
>           Call debug_message(10, "--ODBC update error, " +
> Str(.Number) + " : " + .Description)
>         End With
>       Next MyError
>
> End Function
>
> Relevant section of the resulting logfile: (I've added a lot more than
> normal debugging lines to try tracking down what is causing it.
>
>
> "9/03/2007 6:12:29 AM dbg 80- [+loading sundry product table]"
> "9/03/2007 6:12:29 AM dbg 70-
> [+load_table(Sundry_Product,Product_id,Dealer_id,Franchise,Workshop,Price_Type)]"
>
> "9/03/2007 6:12:29 AM dbg 60- [Criteria = Product_id = 'BULLBAR' and
> Dealer_id  = 'F65' and Franchise  = 'BLANK' and Workshop  = '0' and
> Price_Type  = '0']"
> "9/03/2007 6:12:29 AM dbg 60- [Opening table with criteria=Select *
> from Sundry_Product where Product_id = 'BULLBAR' and Dealer_id  =
> 'F65' and Franchise  = 'BLANK' and Workshop  = '0' and Price_Type  =
> '0']"
> "9/03/2007 6:12:46 AM dbg 60- [Record not found, adding new]"
> "9/03/2007 6:12:46 AM dbg 80- [  + load_xxx_to_db]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> DEALER_ID.value to F65]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> DATE_CHANGED.value to 06-Mar-2007]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> TIME_CHANGED.value to 1809]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> PRODUCT_ID.value to BULLBAR]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> DES_1.value to Bullbar]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> DES_2.value to ]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> DES_3.value to ]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> DES_4.value to ]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> PRODUCT_TYPE.value to S]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> PRODUCT_SALES_GROUP.value to 45]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> PRICE_1.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> PRICE_2.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> PRICE_3.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> PRICE_4.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> COST.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> PARTS_HANDLING.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> INCLUDING_SALES_TAX.value to ]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> PARTS_HANDLING_LIMIT.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> HANDLING_LIMIT_PER_PART.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> DISC_TYPE.value to ]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> DISC_PERCENTAGE.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> MARK_UP_PERCENTAGE.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> ROUND_UP_TO.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> SUBTRACT_FROM_ROUND_UP.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> DISC_MINIMUM.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> DISC_MAXIMUM.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> SUPPLIER_NO.value to 113]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> HANDLING_LIMIT_PER_RO.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> OBSOLETE.value to ]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> FRANCHISE.value to BLANK]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> WORKSHOP.value to 0]"
> "9/03/2007 6:12:46 AM dbg 90- [     + load_xxx_to_db > Setting
> PRICE_TYPE.value to 0]"
> "9/03/2007 6:12:46 AM dbg 60- [  - load_xxx_to_db exit status  0]"
> "9/03/2007 6:12:46 AM dbg 60- [  + updating table]"
> <logfile stops here everytime showing that the table.Update line is
> the point of failure>
>
> Apologies again for the length of this... but hey, you asked for it ;)
>
> Regards,
> Paul.
>