FindFirst vs. Query->OpenRecordset
Posted on 1998-09-01
I have a database that gets updates in the form of a raw text file. The program reads in the first line, parses the data into correct array, then calls the update sub. The update sub looks at the first record, "PrimaryKey", and opens a recordset based on "Where PK= " & arr(0)'PrimaryKey
If nothing is returned, it is assumed this is a new record, and adds it. If a record is returned it is the edited. The code for this is simple, but very slow!! I am using DBJet btw. Would it make since to open the recordset as a whole and do a findfirst? Originally when I wrote this, I was opening the table using the dbOpenTable option, and using a seek. Very fast, but I plan on upsizing someday to SQL, so I must use code structure that will allow this upsize. That is at least 2 years from now, though, and I need to optimize speed for dbJet. Thanks.
Info: approxx 145,000 records with 19 fields are updated every day. Using DAO v2.0, VB6.