Re: Access violation - probably not the fault of Postgres

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.

--
Paul Lambert
Database Administrator
AutoLedgers