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.
>