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