Using FindFirst on ODBC table

  • Thread starter Thread starter JimP
  • Start date Start date
J

JimP

The following code works on a linked mdb table but doesn't on an ODBC linked
table (SQL Server)

Dim rs AS DAO.Recordset
Set rs = CurrentDB.OpenRecordset("MyTable")
rs.FindFirst "CustomerID = '999'" 'CustomerID is a text
field
 
Jim

I seem to recall something (so my memory could be bad or it could have
changed) about FindFirst only working with "local" tables (but my memory
could be bad or ...).

You've described "how" you are trying to do something. If you'll describe a
bit more "why" you are doing this (i.e., what you will be able to do if you
get the "first" record where CustomerID=999), the folks here may be able to
offer more specific suggestions.

For example, if you are trying to "feed" a form with the record
corresponding to CustomerID = 999, there are other ways to accomplish this.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
If working against a SQL-Server, you should use the dbSeeChanges parameter:

Set rs = CurrentDB.OpenRecordset("MyTable", dbOpenDynaset, dbSeeChanges)

Choosing the second parameter is also mandatory but it's not necessarily
dbOpenDynaset.
 
Thank



Sylvain Lafontaine said:
If working against a SQL-Server, you should use the dbSeeChanges
parameter:

Set rs = CurrentDB.OpenRecordset("MyTable", dbOpenDynaset, dbSeeChanges)

Choosing the second parameter is also mandatory but it's not necessarily
dbOpenDynaset.
Click to expand...
 
Thank you - this worked..

Note to other posts, I'm updating a SQL Server record and plan to use the
(Edit & Update commands). I realized later that I could probably do this
with an update query as well.
 
JimP said:
Thank you - this worked..

Note to other posts, I'm updating a SQL Server record and plan to use
the (Edit & Update commands). I realized later that I could probably
do this with an update query as well.
Click to expand...

OR...you could open a RecordSet based on SQL that uses a WHERE clause so you
only retrieve the record(s) you want. Opening an unfiltered recordset and
then using Find is a big No-No when you go client/server.
 
Back
Top