Using FindFirst on ODBC table

[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

 

[Jeff Boyce]

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

 

[Guest]

You may want to try a rs.MoveFirst or even a rs.MoveLast before FindFirst.

Steve

 

[Sylvain Lafontaine]

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.

 

[JimP]

Thank

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.

 

[JimP]

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.

 

[Rick Brandt]

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.

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.