Seek Method on Network

  • Thread starter Thread starter dhstein
  • Start date Start date
D

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.
 
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 said:
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,
Click to expand...

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?
 
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."
 
Back
Top