Access violation - probably not the fault of Postgres

I've got an application written in VB which pipes updates to a database
in Postgres. The application is written in MS Visual Basic 6.0 using
DAO3.6 via ODBC.

If a record exists in the PG database and my VB app therefore does an
Edit/Update, it works without a problem.

If the record doesn't exist and I therefore need to do an AddNew/Update
my client application crashes with an access violation on the update.

I'm relatively sure it's something on the VB side that is causing this
and nothing to do with PG, but I'm just posting here in case anyone has
some thoughts about potential causes within PG or its ODBC driver.

Any thoughts would be welcome.

Thanks,
Paul.

--
Paul Lambert
Database Administrator
AutoLedgers

Paul Lambert wrote:

> I've got an application written in VB which pipes updates to a
database in Postgres. The application is written in MS Visual Basic 6.0
using DAO3.6 via ODBC.
> If a record exists in the PG database and my VB app therefore does an
Edit/Update, it works without a problem.
> If the record doesn't exist and I therefore need to do an
AddNew/Update my client application crashes with an access violation on
the update.
> I'm relatively sure it's something on the VB side that is causing
this and nothing to do with PG, but I'm just posting here in case anyone
has some thoughts about potential causes within PG or its ODBC driver.

Could you try the snapshot driver at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?

regards,
Hiroshi Inoue

Hiroshi Inoue wrote:
> Paul Lambert wrote:
>
> > I've got an application written in VB which pipes updates to a
> database in Postgres. The application is written in MS Visual Basic 6.0
> using DAO3.6 via ODBC.
> > If a record exists in the PG database and my VB app therefore does an
> Edit/Update, it works without a problem.
> > If the record doesn't exist and I therefore need to do an
> AddNew/Update my client application crashes with an access violation on
> the update.
> > I'm relatively sure it's something on the VB side that is causing
> this and nothing to do with PG, but I'm just posting here in case anyone
> has some thoughts about potential causes within PG or its ODBC driver.
>
> Could you try the snapshot driver at
> http://www.geocities.jp/inocchichichi/psqlodbc/index.html
> ?
>
> regards,
> Hiroshi Inoue
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
>

Still crashing.

Regards,
Paul.

--
Paul Lambert
Technical Support Team Leader and Database Administrator
AutoLedgers
Level 3, 823 Wellington Street, West Perth, W.A. 6005
Postal: P.O. Box 106, West Perth, W.A. 6872
Ph: 08 9217 5086 Fax: 08 9217 5055
AutoLedgers Technical Support Desk: 1800 649 987 (Free call) 08 9217
5050 (Perth local and mobile)
Email: paul(dot)lambert(at)autoledgers(dot)com(dot)au <http://www.reynolds.com.au>
------------------------------------------------------------------------------------
For AutoLedgers technical support, please send an email to
helpdesk(at)autoledgers(dot)com(dot)au(dot)

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?

Paul Lambert wrote:
> I've got an application written in VB which pipes updates to a
> database in Postgres. The application is written in MS Visual Basic
> 6.0 using DAO3.6 via ODBC.
>
> If a record exists in the PG database and my VB app therefore does an
> Edit/Update, it works without a problem.
>
> If the record doesn't exist and I therefore need to do an
> AddNew/Update my client application crashes with an access violation
> on the update.
>
> I'm relatively sure it's something on the VB side that is causing this
> and nothing to do with PG, but I'm just posting here in case anyone
> has some thoughts about potential causes within PG or its ODBC driver.
>
> Any thoughts would be welcome.
>
> Thanks,
> Paul.
>

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

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

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

A few more case examples to go with the previously posted code...

Record does not exist in the database, the following is therefore trying
to add it.

"12/03/2007 7:04:36 AM dbg 80- [+loading sundry product table]"
"12/03/2007 7:04:36 AM dbg 70-
[+load_table(Sundry_Product,Product_id,Dealer_id,Franchise,Workshop,Price_Type)]"
"12/03/2007 7:04:36 AM dbg 60- [Criteria = Product_id = 'BULLBAR' and
Dealer_id = 'F65' and Franchise = 'BLANK' and Workshop = '0' and
Price_Type = '0']"
"12/03/2007 7:04:36 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']"
"12/03/2007 7:04:53 AM dbg 60- [Record not found, adding new]"
"12/03/2007 7:04:53 AM dbg 80- [ + load_xxx_to_db]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
DEALER_ID.value to F65]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
DATE_CHANGED.value to 06-Mar-2007]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
TIME_CHANGED.value to 1809]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
PRODUCT_ID.value to BULLBAR]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
DES_1.value to Bullbar]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
DES_2.value to ]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
DES_3.value to ]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
DES_4.value to ]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
PRODUCT_TYPE.value to S]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
PRODUCT_SALES_GROUP.value to 45]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_1.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_2.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_3.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_4.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
COST.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
PARTS_HANDLING.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
INCLUDING_SALES_TAX.value to ]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
PARTS_HANDLING_LIMIT.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
HANDLING_LIMIT_PER_PART.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
DISC_TYPE.value to ]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
DISC_PERCENTAGE.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
MARK_UP_PERCENTAGE.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
ROUND_UP_TO.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
SUBTRACT_FROM_ROUND_UP.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
DISC_MINIMUM.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
DISC_MAXIMUM.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
SUPPLIER_NO.value to 113]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
HANDLING_LIMIT_PER_RO.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
OBSOLETE.value to ]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
FRANCHISE.value to BLANK]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
WORKSHOP.value to 0]"
"12/03/2007 7:04:53 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_TYPE.value to 0]"
"12/03/2007 7:04:53 AM dbg 60- [ - load_xxx_to_db exit status 0]"
"12/03/2007 7:04:53 AM dbg 60- [ + updating table]"
<Access violation terminates program>

I then manually added an empty record with the 5 primary key fields
having the same value as
what my program has been trying to add and then piped the data into my
program to try
again.

"12/03/2007 7:06:26 AM dbg 80- [+loading sundry product table]"
"12/03/2007 7:06:26 AM dbg 70-
[+load_table(Sundry_Product,Product_id,Dealer_id,Franchise,Workshop,Price_Type)]"
"12/03/2007 7:06:26 AM dbg 60- [Criteria = Product_id = 'BULLBAR' and
Dealer_id = 'F65' and Franchise = 'BLANK' and Workshop = '0' and
Price_Type = '0']"
"12/03/2007 7:06:26 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']"
"12/03/2007 7:06:43 AM dbg 60- [Record found, updating]"
"12/03/2007 7:06:43 AM dbg 80- [ + load_xxx_to_db]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
DEALER_ID.value to F65]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
DATE_CHANGED.value to 06-Mar-2007]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
TIME_CHANGED.value to 1809]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
PRODUCT_ID.value to BULLBAR]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
DES_1.value to Bullbar]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
DES_2.value to ]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
DES_3.value to ]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
DES_4.value to ]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
PRODUCT_TYPE.value to S]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
PRODUCT_SALES_GROUP.value to 45]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_1.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_2.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_3.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_4.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
COST.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
PARTS_HANDLING.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
INCLUDING_SALES_TAX.value to ]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
PARTS_HANDLING_LIMIT.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
HANDLING_LIMIT_PER_PART.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
DISC_TYPE.value to ]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
DISC_PERCENTAGE.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
MARK_UP_PERCENTAGE.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
ROUND_UP_TO.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
SUBTRACT_FROM_ROUND_UP.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
DISC_MINIMUM.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
DISC_MAXIMUM.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
SUPPLIER_NO.value to 113]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
HANDLING_LIMIT_PER_RO.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
OBSOLETE.value to ]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
FRANCHISE.value to BLANK]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
WORKSHOP.value to 0]"
"12/03/2007 7:06:43 AM dbg 90- [ + load_xxx_to_db > Setting
PRICE_TYPE.value to 0]"
"12/03/2007 7:06:43 AM dbg 60- [ - load_xxx_to_db exit status 0]"
"12/03/2007 7:06:43 AM dbg 60- [ + updating table]"
"12/03/2007 7:06:46 AM dbg 60- [ - updating table]"
"12/03/2007 7:06:46 AM dbg 60- [-load_table]"

Success!

Would this be some sort of permissions problem?

To make matters more interesting, my program was adding two records into
another table, said table was empty, thus adding new records in both
cases. The first one worked but the second one caused an access violation.

"12/03/2007 7:07:30 AM dbg 80- [+loading deal line table]"
"12/03/2007 7:07:30 AM dbg 70-
[+load_table(Deal_lines,Address,Dealer_id,,,)]"
"12/03/2007 7:07:30 AM dbg 60- [Criteria = Address = '73969' and
Dealer_id = 'F65']"
"12/03/2007 7:07:30 AM dbg 60- [Opening table with criteria=Select *
from Deal_lines where Address = '73969' and Dealer_id = 'F65']"
"12/03/2007 7:07:41 AM dbg 60- [Record not found, adding new]"
"12/03/2007 7:07:41 AM dbg 80- [ + load_xxx_to_db]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
DEALER_ID.value to F65]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
DATE_CHANGED.value to 06-Mar-2007]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
TIME_CHANGED.value to 1813]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
DEAL_ADDRESS.value to 0]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
LINE_TYPE.value to V]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
ADDRESS.value to 73969]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
DELETED.value to 30]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
VEHICLE_ADDRESS.value to 1001]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
DEAL_NO.value to 1]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
ORDER_CODE.value to 99COST_ADJUST]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
REFERENCE.value to ]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
ORDER_DESC.value to Vehicle Cost Adjust]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
INVOICE_PRICE.value to 0]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
INVOICE_TAX.value to 0]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
INVOICE_COST.value to 1290.56]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
INVOICE_PAYMENT.value to 0]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
FORMAT_CODE.value to ]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
INVOICE_CODE.value to C]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
OPTION_CODE.value to ]"
"12/03/2007 7:07:41 AM dbg 90- [ + load_xxx_to_db > Setting
OPTION_SEQUENCE.value to ]"
"12/03/2007 7:07:41 AM dbg 60- [ - load_xxx_to_db exit status 0]"
"12/03/2007 7:07:41 AM dbg 60- [ + updating table]"
"12/03/2007 7:07:42 AM dbg 60- [ - updating table]"
"12/03/2007 7:07:42 AM dbg 60- [-load_table]"
<this indicates it completed succesfully>
"12/03/2007 7:07:42 AM dbg 10- [-process_dmq_message]"
"12/03/2007 7:07:42 AM dbg 80- [+read_dmq_message: Processed message,
ODBC State 0]"
"12/03/2007 7:07:42 AM dbg 80- [confirming message]"
"12/03/2007 7:07:42 AM dbg 80- [-read_dmq_message]"
"12/03/2007 7:07:42 AM dbg 80- [+read_dmq_message]"
"12/03/2007 7:07:42 AM dbg 80- [+read_dmq_message: Message Read with
Status = 1]"
"12/03/2007 7:07:42 AM dbg 80- [validating DmQ header]"
"12/03/2007 7:07:42 AM dbg 10- [+process_dmq_message]"
"12/03/2007 7:07:42 AM dbg 80- [+extract_items]"
"12/03/2007 7:07:42 AM dbg 80- [-extract_items]"
"12/03/2007 7:07:42 AM dbg 80- [+unpack_items]"
"12/03/2007 7:07:42 AM dbg 80- [-unpack_items]"
"12/03/2007 7:07:42 AM dbg 80- [+loading deal line table]"
"12/03/2007 7:07:42 AM dbg 70-
[+load_table(Deal_lines,Address,Dealer_id,,,)]"
"12/03/2007 7:07:42 AM dbg 60- [Criteria = Address = '73970' and
Dealer_id = 'F65']"
"12/03/2007 7:07:42 AM dbg 60- [Opening table with criteria=Select *
from Deal_lines where Address = '73970' and Dealer_id = 'F65']"
"12/03/2007 7:07:46 AM dbg 60- [Record not found, adding new]"
"12/03/2007 7:07:46 AM dbg 80- [ + load_xxx_to_db]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
DEALER_ID.value to F65]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
DATE_CHANGED.value to 06-Mar-2007]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
TIME_CHANGED.value to 1813]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
DEAL_ADDRESS.value to 0]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
LINE_TYPE.value to P]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
ADDRESS.value to 73970]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
DELETED.value to 30]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
VEHICLE_ADDRESS.value to 1001]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
DEAL_NO.value to 1]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
ORDER_CODE.value to ABULLBAR GEN]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
REFERENCE.value to ]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
ORDER_DESC.value to Bullbar.]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
INVOICE_PRICE.value to 1500]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
INVOICE_TAX.value to 136.36]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
INVOICE_COST.value to 1363.64]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
INVOICE_PAYMENT.value to 1500]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
FORMAT_CODE.value to ]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
INVOICE_CODE.value to C]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
OPTION_CODE.value to ]"
"12/03/2007 7:07:46 AM dbg 90- [ + load_xxx_to_db > Setting
OPTION_SEQUENCE.value to ]"
"12/03/2007 7:07:46 AM dbg 60- [ - load_xxx_to_db exit status 0]"
"12/03/2007 7:07:46 AM dbg 60- [ + updating table]"
<Access violation terminates program>

The first example points to a possible permissions problem, can update
but not add - but the second example counters that by being able to add
one record but not another which would point to something in the data...
both completely contradicting each other. :(

I'm tearing my hair out here (which is not really a problem since I'm
shaving it all off for charity on Friday) trying to get this thing
working... it's got me completely boggled.

I'm in the process of setting up a small test case to rule out or
confirm the data as being the problem. But that will have to wait until
tomorrow to finish so I'll let you know what I find then, but if anyone
has any other thoughts between now and then, I'd love to hear them.

Thanks,
Paul.

--
Paul Lambert
Database Administrator
AutoLedgers

Paul,
Some questions come to mind.
1) Do these errors appear to be coming from the PG server, the odbc
driver, VB or DAO?
2) When you manually create the data, what are you using?

3) Have you tried replacing the Recordset.AddNew() function with the
database object's .execute() function? What I am basicly asking is, is
it possible to change the load_xxx_to_db() subroutine to return a SQL
query string.

4) Could you turn on the CommLog option in the ODBC driver, and send
that log to the mailing list along with your current VB logs?

David

Paul Lambert wrote:
> 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?
>>>
>>>
>>
>
> A few more case examples to go with the previously posted code...
>
>
> Record does not exist in the database, the following is therefore trying
> to add it.
>

> I then manually added an empty record with the 5 primary key fields
> having the same value as
> what my program has been trying to add and then piped the data into my
> program to try
> again.

>
> Success!
>
> Would this be some sort of permissions problem?
>
> To make matters more interesting, my program was adding two records into
> another table, said table was empty, thus adding new records in both
> cases. The first one worked but the second one caused an access violation.
>
>

> The first example points to a possible permissions problem, can update
> but not add - but the second example counters that by being able to add
> one record but not another which would point to something in the data...
> both completely contradicting each other. :(
>
> I'm tearing my hair out here (which is not really a problem since I'm
> shaving it all off for charity on Friday) trying to get this thing
> working... it's got me completely boggled.
>
> I'm in the process of setting up a small test case to rule out or
> confirm the data as being the problem. But that will have to wait until
> tomorrow to finish so I'll let you know what I find then, but if anyone
> has any other thoughts between now and then, I'd love to hear them.
>
> Thanks,
> Paul.
>
> --
> Paul Lambert
> Database Administrator
> AutoLedgers
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

More on this whole story...

I've found that in some instances I was referencing table and fieldnames
with cased names, where all items in my db are lowercase. I changed that.

I also found that when checking for primary keys the program was
surrounding the data values with square brackets [] - a legacy from sql
server.

I also found that when passing strings to the database they weren't
enclosed in apostrophes. I.e. Joe Bloggs instead of 'Joe Bloggs'

I noted in some cases my program was trying to do an AddNew when the
record did in fact exist in the database, which I assume to be due to
the casing of field names in the primary key. However in those cases the
ODBC driver was receiving an error rather than crashing the program.

I don't know if any of these would have been the central problem, but I
changed them all to more appropriate behavior and the incidences of
crashes have somewhat dropped. Rather than crashing with every second or
third update it is now able to carry on for a couple of dozen or more
before it goes kaput....

Now I'm more intrigued.

I am in the process of converting the updates and inserts to executes on
the database object rather than using an intermediary recordset, so I'll
see if that changes anything - it'll let me get rid of a significant
block of code anyway.

P.

--
Paul Lambert
Database Administrator
AutoLedgers

Per the suggestion of David Gardner, I changed the update and addnews to
perform an execute on the database object.

This appears to have resolved my problem - so far so good anyway, no
crashes after a few hundred updates.

Thanks very much for the suggestion David.

Regards,
Paul.

--
Paul Lambert
Database Administrator
AutoLedgers