[VCOM] VB6 Querying table after many inserts, fails in multiple ways

[rbundock at bluementor.com]

Hi

Im trying to transfer a DBase5 database into Valentina via VB6. Im
typically looping through the DBase5 tables and executing insert statements
against Valentina. My first challenge was that after 1137 records this
statement would error with 'Field not found':

  ...
  Set rstCheck = objVDatabase.SqlSelect("SELECT RecID FROM words WHERE word
= '" & strIWord & "'")
  GetWordID = CLng(rstCheck.Field(1).Value)
  ...
  
I discovered that if I trapped this and then tried the query a different
way, it would continue to work:

  ...
  Set rstCheck = objVDatabase.SqlSelect("SELECT RecID FROM words WHERE word
= '" & strIWord & "'")
  If rstCheck.FieldCount > 0 Then
    GetWordID = CLng(rstCheck.Field(1).Value)
  Else
    ' Try query different way
    Set rstCheck = objVDatabase.SqlSelect("SELECT RecID FROM words WHERE
word LIKE '\A" & strIWord & "'")
    GetWordID = CLng(rstCheck.Field(1).Value)
  End If
  ...

Only certain values of strIWord would give this problem (eg. 'UNION').
However, now after 13650 records instead of rstCheck not containing any
fields it simply becomes unstable and errors with 'Object not referenced'.
Ive tried closing and re-opening the database, using Flush and the SQL
'DEFRAGMENT' and 'COMPACT'. The database is left in a damaged state. 

Any help would be appreciated.

Thanks

Rich

--------------------------------------------------------------------
mail2web - Check your email from the web at
http://mail2web.com/ .