Seek Method on Network

[dhstein]

I have code like this:

Set rs1 = CurrentDb.OpenRecordset("MyTable")

rs1.Index = "MyCompositeIndex"
rs1.Seek "=", Field1, Field2, Field3

If Not rs1.NoMatch Then

' Calculations and Edits to fields are done here
..
..

End If

This all works fine on my stand-alone database. Now I've split the database
so it can run on the network and I get the error message:

Error 3251 Operation is not supported for this type of object.

So my question is, how do I seek - or find a record that satisfies a
composite group of criteria in a networked database. I need to get the
record and perform a number of calculations and then do an update to the
fields. Thanks for any help you can provide.

 

[Allen Browne]

As you found the Seek method doesn't work with attached tables: they open
dbOpenDynaset instead of dbOpenTable.

Instead:
a) Use a SQL statement to grab only the records you want, sorted as desired,
e.g.:

strSql = "SELECT MyTable.* FROM MyTable WHERE ((Field1 = 99) AND (Field2 =
88) AND (Field3 = 77)) ORDER BY Field1, Field2, Field3;"
Set rs1 = CurrntDb.OpenRecordset(strSql)

b) If you must load more records than you need initiallly, use the Find
method:
strWhere = "((Field1 = 99) AND (Field2 = 88) AND (Field3 = 77))"
rs1.Find strWhere

 

[dhstein]

As you found the Seek method doesn't work with attached tables: they open
dbOpenDynaset instead of dbOpenTable.

Instead:
a) Use a SQL statement to grab only the records you want, sorted as desired,
e.g.:

strSql = "SELECT MyTable.* FROM MyTable WHERE ((Field1 = 99) AND (Field2 =
88) AND (Field3 = 77)) ORDER BY Field1, Field2, Field3;"
Set rs1 = CurrntDb.OpenRecordset(strSql)

b) If you must load more records than you need initiallly, use the Find
method:
strWhere = "((Field1 = 99) AND (Field2 = 88) AND (Field3 = 77))"
rs1.Find strWhere

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.



Allen,

Thanks for your response. Where you've coded 77, 88 and 99 above, I
actually have values that I've read from an input table, but I think I can
figure out that syntax. The more pressing concern is that I need to know if
the "seek" was successful and code accordingly. The seek method allows me to
test for that - can I test some condition with the SQL string method that
you've shown?

 

[Allen Browne]

Yes, you can concatenate the values into the SQL string. (Be sure to include
the right delimiters for the field type: quotes for a Text field, or #
around dates.)

You can test whether any matching records were found.:
If rs1.RecordCount > 0 Then MsgBox "Yep: found."