I cut this example right out-a-the MS Access 2.0 help. I cannot get it to work.

If I substitute one of my own tables into the 4th line,
the code shown below still fails on that same line
at runtime. Any ideas why?

Sub Button0_Click ()
Dim MyDB As Database, MyTable As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("Products", DB_OPEN_TABLE)
MyTable.Index = "Supplier ID"
Do Until MyTable.NoMatch
MyTable.Seek "=", 1
If Not MyTable.NoMatch Then
MyTable.Edit
MyTable("Supplier ID") = 2
MyTable.Update
End If
Loop
MyTable.Close

End Sub
xxxxxxxxxx Bottom of Code Snippet xxxxxxxxxxxx

Here's the error message I get...
Invalid operation.

Possible causes:

You tried to write to a read-only property. See the Usage
section of the Help topic for the property to determine whether or not
it's read write.
You tried to use a method or property on a type of Recordset
object that the method or property doesn't apply to. See the
Recordset Object,
Recordsets Collection - Summary topic to determine which methods and
properties apply to a given type of Recordset object.
You tried to append a property to a Properties collection of
an object that doesn't support user-defined properties.

re: I cut this example right out-a-the MS Access 2.0 help. I cannot get it to work.

I failed to mention that the table on which I was operating
was an attached table in a frontend/backend setting. I have
had this problem before and am uncertain whether it has been
resolved.

re: I cut this example right out-a-the MS Access 2.0 help. I cannot get it to work.

MLH wrote:[color=blue]
> I failed to mention that the table on which I was operating
> was an attached table in a frontend/backend setting. I have
> had this problem before and am uncertain whether it has been
> resolved.[/color]

You cannot use DB_OPEN_TABLE on a linked table. Use DB_OPEN_DYNASET.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

re: I cut this example right out-a-the MS Access 2.0 help. I cannot get it to work.

You can't treat an attached table as table type recordset.

Either
Open as a dynaset and ditch the seek
Or
change the line
Set MyDB = DBEngine.Workspaces(0).Databases(0)
to open the db the table is in
e.g.
Set MyDB = OpenDatabase("Path to the table containing db")



--
Terry Kreft
MVP Microsoft Access


"MLH" <CRCI@NorthState.net> wrote in message
news:h3kt51tdpt4mq0aaduhfr5htdjh26bdj73@4ax.com...[color=blue]
> I failed to mention that the table on which I was operating
> was an attached table in a frontend/backend setting. I have
> had this problem before and am uncertain whether it has been
> resolved.[/color]

re: I cut this example right out-a-the MS Access 2.0 help. I cannot get it to work.

Thanks, Terry. I really like the latter option. That way, I
can use the SEEK method. That's a great idea! You are
a genius!
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

[color=blue]
>You can't treat an attached table as table type recordset.
>
>Either
> Open as a dynaset and ditch the seek
>Or
> change the line
> Set MyDB = DBEngine.Workspaces(0).Databases(0)
> to open the db the table is in
> e.g.
> Set MyDB = OpenDatabase("Path to the table containing db")[/color]

re: I cut this example right out-a-the MS Access 2.0 help. I cannot get it to work.

MLH <CRCI@NorthState.net> wrote in
news:aavv51pur6ml5pm2456d010o29od8n2871@4ax.com:
[color=blue][color=green]
>>You can't treat an attached table as table type recordset.
>>
>>Either
>> Open as a dynaset and ditch the seek
>>Or
>> change the line
>> Set MyDB = DBEngine.Workspaces(0).Databases(0)
>> to open the db the table is in
>> e.g.
>> Set MyDB = OpenDatabase("Path to the table containing db")[/color]
>
> Thanks, Terry. I really like the latter option. That way, I
> can use the SEEK method. That's a great idea! You are
> a genius![/color]

Why in the world do you need to use SEEK?

SEEK will only give a performance advantage when you *must* open the
entire table for navigation, and you need to jump around within that
table to locate data.

In the vast majority of cases, you need a particular value or set of
records, and in that case, it's vastly easier to use a dynaset and
use a WHERE clause to restrict the records returned to just what you
need. If you have multiple records, you can navigate with
..FindFirst/.FindNext. That operation is slower than SEEK, but
because save time (and resources) because you've retrieved far fewer
rows in the first place -- the net result is the same or faster than
the SEEK. Or, at least, it's close enough to make it not worth the
trouble of using SEEK.

I, for one, have never once used a SEEK, ever, in 10 years of Access
programming. Not even one time. And I can't think of a single
real-world scenario in any of the dozens of apps I've created where
SEEK would offer any kind of advantage that would be worth the
trouble of using it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc