Seek method - converting to FindFirst

[dhstein]

With the "seek" method I was seeking a record in the table that
matched a value read from a file like this:
rsProducts.seek "=", ![SKU]
This works fine

Now that I can't use this in a split database, I opened the table like this:
Set db = CurrentDb
Set rsProducts = db.OpenRecordset("tblProduct", dbOpenDynaset)

I'm trying to do the same thing with FindFirst like this:

FileSKU = ![SKU]
rsProducts.FindFirst "(ProductSKU) = FileSKU"

I got error 3070 - does not recognize FileSKU as a valid field name or
expression

Next I tried this:
FileSKU = ![SKU]
rsProducts.FindFirst "(ProductSKU) = 'FileSKU'"
this time there was no error, but the FindFirst did not find the record (the
record is definitely there - the seek works fine)

Any ideas how to do this? Thanks.

 

[Dirk Goldgar]

With the "seek" method I was seeking a record in the table that
matched a value read from a file like this:
rsProducts.seek "=", ![SKU]
This works fine

Now that I can't use this in a split database, I opened the table like
this:
Set db = CurrentDb
Set rsProducts = db.OpenRecordset("tblProduct", dbOpenDynaset)

I'm trying to do the same thing with FindFirst like this:

FileSKU = ![SKU]
rsProducts.FindFirst "(ProductSKU) = FileSKU"

I got error 3070 - does not recognize FileSKU as a valid field name or
expression

Next I tried this:
FileSKU = ![SKU]
rsProducts.FindFirst "(ProductSKU) = 'FileSKU'"
this time there was no error, but the FindFirst did not find the record
(the
record is definitely there - the seek works fine)

Any ideas how to do this? Thanks.

You might do it like this:

rsProducts.FindFirst "ProductSKU = '" & FileSKU & "'"

Note I have embedded single-quotes (') around the value of the variable
FileSKU, because I assume ProductSKU is a text field. You don't need the
parentheses around ProductSKU, so I've removed them.

If you are just opening this recordset to look up a single SKU, it will be
*much* more efficient to do it like this:

Set db = CurrentDb

Set rsProducts = db.OpenRecordset( _
"SELECT * FROM tblProduct " & _
"WHERE ProductSKU = '" & FileSKU & "'")

Now you have a recordset containing the one product (if any) for the given
SKU -- assuming ProductSKU is a unique key.

 

[Duane Hookom]

Seek is much faster. You can use it with linked tables with the solution from
http://www.mvps.org/access/tables/tbl0006.htm.

 

[Dirk Goldgar]

Seek is much faster. You can use it with linked tables with the solution
from
http://www.mvps.org/access/tables/tbl0006.htm.

I agree that Seek is faster, Duane, but I have to say that for most
practical purposes it makes no meaningful difference. I have written an
application that used Seek and a complex set of indexes, but I think most of
the time the best answer is just to open a recordset on a SQL statement that
returns the specific record wanted. I haven't had many occasions to hold a
recordset open and navigate extensively via either Seek *or* FindFirst --
except when working with a form's recordset, where Seek isn't an option.

 

[Duane Hookom]

I agree there may be times where it won't make much difference but since the
OP had used Seek with success in the past, it made sense to continue to use
it.

I did have an application that stored thousands of survey results in a very
normalized structure. The results were scanned in to an un-normalized
structure and then I used FindFirst to update records. The code took minutes
to run until I switched the code to use Seek. The Seek ran more than 100
times faster in my situation.

I also agree that if a single record is sought, open a filtered recordset.

 

[Dirk Goldgar]

I agree there may be times where it won't make much difference but since
the
OP had used Seek with success in the past, it made sense to continue to
use
it.

I see your point, though I'd be concerned that it might not really be the
best solution, even if more familiar.

I did have an application that stored thousands of survey results in a
very
normalized structure. The results were scanned in to an un-normalized
structure and then I used FindFirst to update records. The code took
minutes
to run until I switched the code to use Seek. The Seek ran more than 100
times faster in my situation.

That's an impressive speed gain, but I'm not surprised. My "Seek"
application involved successive attempts to find a best match, in a large
lookup table, on a variable set of key fields, and it was completely
impractical without Seek. There's no question that using an index to locate
records is enormously faster than a table scan. That's why we can both
agree:

[...] if a single record is sought, open a filtered recordset.

 

[dhstein]

With the "seek" method I was seeking a record in the table that
matched a value read from a file like this:
rsProducts.seek "=", ![SKU]
This works fine

Now that I can't use this in a split database, I opened the table like
this:
Set db = CurrentDb
Set rsProducts = db.OpenRecordset("tblProduct", dbOpenDynaset)

I'm trying to do the same thing with FindFirst like this:

FileSKU = ![SKU]
rsProducts.FindFirst "(ProductSKU) = FileSKU"

I got error 3070 - does not recognize FileSKU as a valid field name or
expression

Next I tried this:
FileSKU = ![SKU]
rsProducts.FindFirst "(ProductSKU) = 'FileSKU'"
this time there was no error, but the FindFirst did not find the record
(the
record is definitely there - the seek works fine)

Any ideas how to do this? Thanks.

You might do it like this:

rsProducts.FindFirst "ProductSKU = '" & FileSKU & "'"

Note I have embedded single-quotes (') around the value of the variable
FileSKU, because I assume ProductSKU is a text field. You don't need the
parentheses around ProductSKU, so I've removed them.

If you are just opening this recordset to look up a single SKU, it will be
*much* more efficient to do it like this:

Set db = CurrentDb

Set rsProducts = db.OpenRecordset( _
"SELECT * FROM tblProduct " & _
"WHERE ProductSKU = '" & FileSKU & "'")

Now you have a recordset containing the one product (if any) for the given
SKU -- assuming ProductSKU is a unique key.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dick and others,

Thank you for your help with this.

I have 8 modules which all read files and update the database from the input
data. They all use the seek method in some way, so I need to convert all of
them. In all cases, it isn't just one lookup, but an iterative lookup within
a loop. Ideally, I'd like to be able to keep the seek method by opening the
tables directly, but so far I've had no success with that. Dick, your syntax
on the FindFirst worked great ---- rsProducts.FindFirst "ProductSKU = '" &
FileSKU & "'" ----, so at this point I'm going to continue down that path.
The next syntax issue is how to convert seek to FindFirst when I have a
composite SKU like this:


rsTable2.Seek "=", FieldOne, FieldTwo

Thanks for any information

 

[dhstein]

With the "seek" method I was seeking a record in the table that
matched a value read from a file like this:
rsProducts.seek "=", ![SKU]
This works fine

Now that I can't use this in a split database, I opened the table like
this:
Set db = CurrentDb
Set rsProducts = db.OpenRecordset("tblProduct", dbOpenDynaset)

I'm trying to do the same thing with FindFirst like this:

FileSKU = ![SKU]
rsProducts.FindFirst "(ProductSKU) = FileSKU"

I got error 3070 - does not recognize FileSKU as a valid field name or
expression

Next I tried this:
FileSKU = ![SKU]
rsProducts.FindFirst "(ProductSKU) = 'FileSKU'"
this time there was no error, but the FindFirst did not find the record
(the
record is definitely there - the seek works fine)

Any ideas how to do this? Thanks.

You might do it like this:

rsProducts.FindFirst "ProductSKU = '" & FileSKU & "'"

Note I have embedded single-quotes (') around the value of the variable
FileSKU, because I assume ProductSKU is a text field. You don't need the
parentheses around ProductSKU, so I've removed them.

If you are just opening this recordset to look up a single SKU, it will be
*much* more efficient to do it like this:

Set db = CurrentDb

Set rsProducts = db.OpenRecordset( _
"SELECT * FROM tblProduct " & _
"WHERE ProductSKU = '" & FileSKU & "'")

Now you have a recordset containing the one product (if any) for the given
SKU -- assuming ProductSKU is a unique key.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Duane and others,

Thanks for the help. I tried the "OpenForSeek" solution from Michael
Walsh, but when I run it I get a window asking for me to select my data
source. This is the piece I can't figure out. I believe I have to set up
ACEODBC.DLL as my Access ODBC driver but I'm not having any success. If
anyone can shed any light on this I would appreciate it. Thanks.

 

[a a r o n . k e m p f]

----------------------------------------------------------------------------------------
Access, however, is not perfect. Performance degrades significantly as
the database size increases. The database is also prone to corruption.
Finally, starting with an Access database has tempted many developers
to do a dangerous thing. Sometimes a single-user application becomes
popular enough that there's a desire for it to be used by multiple
simultaneous users. The temptation is to just move the Access database
file to a network share, copy the application to multiple machines,
and let many users connect simultaneously. Access performance drops
off quickly with multiple users, and it's highly unlikely that an
application that was designed for a single user will work reliably
with concurrent users.

http://msdn.microsoft.com/en-us/library/aa730870(VS.80).aspx
----------------------------------------------------------------------------------------

           With the "seek" method I was seeking a record in the table that
matched a value read from a file like this:
           rsProducts.seek "=", ![SKU]
This works fine
Now that I can't use this in a split database, I opened the table like
this:
Set db = CurrentDb
Set rsProducts = db.OpenRecordset("tblProduct", dbOpenDynaset)
I'm trying to do the same thing with FindFirst like this:
           FileSKU = ![SKU]
           rsProducts.FindFirst "(ProductSKU) = FileSKU"
I got error 3070 - does not recognize FileSKU as a valid field name or
expression
Next I tried this:
FileSKU = ![SKU]
           rsProducts.FindFirst "(ProductSKU) = 'FileSKU'"
this time there was no error, but the FindFirst did not find the record
(the
record is definitely there - the seek works fine)
Any ideas how to do this?  Thanks.

You might do it like this:

    rsProducts.FindFirst "ProductSKU = '" & FileSKU & "'"

Note I have embedded single-quotes (') around the value of the variable
FileSKU, because I assume ProductSKU is a text field.  You don't needthe
parentheses around ProductSKU, so I've removed them.

If you are just opening this recordset to look up a single SKU, it willbe
*much* more efficient to do it like this:

    Set db = CurrentDb

    Set rsProducts = db.OpenRecordset( _
        "SELECT * FROM tblProduct " & _
        "WHERE ProductSKU = '" & FileSKU & "'")

Now you have a recordset containing the one product (if any) for the given
SKU -- assuming ProductSKU is a unique key.

(please reply to the newsgroup)

Duane and others,

  Thanks for the help.  I tried the "OpenForSeek" solution from Michael
Walsh, but when I run it I get a window asking for me to select my data
source.  This is the piece I can't figure out.  I believe I have to set up
ACEODBC.DLL as my Access ODBC driver but I'm not having any success.  If
anyone can shed any light on this I would appreciate it.  Thanks.- Hidequoted text -

- Show quoted text -