Search query, not select

[J Shrimps, Jr.]

Need to search a linked text table of about
50,000 rows for a string (user
is going to select the string from cmbbox limited
by a group query), then append every record
directly below the search criteria record ( the
search criteria record is always unique),
until the a specific (terminating) string is found in one
of the records below.
Specific string could be found in
the next twenty records, the next 120, or 220 records,
but I need to append Field1 of every record
(there's only one field) found until that termination string.
Can do everything with rst.MoveNext, Insert into, etc.
except the original SQL statement i.e..
strSQL = "Select TblTextFile.Field1 from TblTxtFile where
(((tblTxtFile.Field1)=[forms]![frmParseCode]![Cmbcode]));"
However, this returns the correct record, but ONLY one
record. I need the cursor to Seek, NOT select the record selected
in the cmbBox, start there and append every record (only
one Char80 field in the table so that's easy to do) into another tmptable.
Linked text files can't be indexed and can't add an autonumber field.
How do I position the cursor at a specific record without an
index or autonumber?

 

[Tom Ellison]

Dear J:

You want to add rows to tmptable changing Field1 in a selected subset of all
rows in TblTextFile where some key value is between your lower and upper
bounds. The lower bound is determined by the "search criteria" in Cmbcode.
The upper bound is where Field1 is TerminatingString. Do I have this
correct?

Your sample SQL code is a select query. You could SELECT INTO once this
query is showing the proper rows. That's a good idea. You just need to see
the proper results in the SELECT query first, then transform it. You won't
need to use a cursor at all that way. The whole process can be performed in
a query, and probably more quickly.

The lack of an index can affect performance, but won't prevent you from
querying this. It may be slower than you'd like.

So, the SQL sounds like:

SELECT Field1
FROM TblTextFile
WHERE Field1 >= [forms]![frmParseCode]![Cmbcode]
AND Field1 <= [Enter Terminating String]

I don't know from where you are to obtain the value of the Terminating
String. If it is in a control, you can substitute that above.

The finished product might look like the following:

SELECT Field1 & "String to be appended"
INTO temptable
FROM TblTextFile
WHERE Field1 >= [forms]![frmParseCode]![Cmbcode]
AND Field1 <= [Enter Terminating String]

The string to be appended may also reference a control on your form, if
that's where it is found.

An autonumber column in the source table would not be of any assistance
unless it is guaranteed to index the table in ascending order of Field1. To
index the table in any other order would not assist in your process.

You could still perform this using a cursor, although I can't imagine why
that would be desirable. The query would look like the first one above.
Your could add:

ORDER BY Field1

to this query to create the effect of an index. Your cursor could then seek
this position and proceed until reaching the end of file, which is defined
by the query as the terminating string. This, in effect, indexes the table
in Field1 sequence, but that index is built at run time, every time, instead
of being retained in the database. There is an obvious performance penalty
for doing this.

Finally, there is the option to have the cursor "scan" the entire table and
perform the insertion into temptable conditionally, only when the
pre-existing value of Field1 lies between the bounds.

There are many ways to get this done. The most efficient would be the
purely query approach shown second in my post.

Tom Ellison

Need to search a linked text table of about
50,000 rows for a string (user
is going to select the string from cmbbox limited
by a group query), then append every record
directly below the search criteria record ( the
search criteria record is always unique),
until the a specific (terminating) string is found in one
of the records below.
Specific string could be found in
the next twenty records, the next 120, or 220 records,
but I need to append Field1 of every record
(there's only one field) found until that termination string.
Can do everything with rst.MoveNext, Insert into, etc.
except the original SQL statement i.e..
strSQL = "Select TblTextFile.Field1 from TblTxtFile where
(((tblTxtFile.Field1)=[forms]![frmParseCode]![Cmbcode]));"
However, this returns the correct record, but ONLY one
record. I need the cursor to Seek, NOT select the record selected
in the cmbBox, start there and append every record (only
one Char80 field in the table so that's easy to do) into another tmptable.
Linked text files can't be indexed and can't add an autonumber field.
How do I position the cursor at a specific record without an
index or autonumber?