Using OpenRecordset with a linked table

[ersouthard]

I have a program that has a front end and a backend database. The tables in
the front end are linked to tables in the back end.

I have the following lines of code (along with other code) in the
AfterUpdate property of a field.

Set rstWeekData = dbs.OpenRecordset("Fiscal Year Table")

rstWeekData.Index = idxWeekNum.Name

The form this field is in is in the front end and the table I'm referring to
is linked to a table in the back end. When the code tries to run, I get an
error message when it gets to the 2nd line listed above. The error message
is:

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

Prior to creating the back end database I didn't have any problems. Is
there anything I can do to get it to work with the linked table? Thanks.

 

[Jeff Boyce]

It may just be a matter of semantics, but it pays to make sure...

When you split an Access database, the tables are in the back-end, and they
are NOT in the front-end. What's in the front-end is a link to the tables
(but not the tables themselves).

Are you saying you have links from the front-end to the back-end tables?

Do you still have this error after dropping the link and re-linking?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[RoyVidar]

I have a program that has a front end and a backend database. The
tables in the front end are linked to tables in the back end.

I have the following lines of code (along with other code) in the
AfterUpdate property of a field.

Set rstWeekData = dbs.OpenRecordset("Fiscal Year Table")

rstWeekData.Index = idxWeekNum.Name

The form this field is in is in the front end and the table I'm
referring to is linked to a table in the back end. When the code
tries to run, I get an error message when it gets to the 2nd line
listed above. The error message is:

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

Prior to creating the back end database I didn't have any problems.
Is there anything I can do to get it to work with the linked table?
Thanks.

Why are you using the index?

If it is because you wish to do a .Seek, then that doesn't work with
linked tables.

Either rewrite to use the .FindFirst method, or open the recordset
with a where clause

 

[Dirk Goldgar]

I have a program that has a front end and a backend database. The tables
in
the front end are linked to tables in the back end.

I have the following lines of code (along with other code) in the
AfterUpdate property of a field.

Set rstWeekData = dbs.OpenRecordset("Fiscal Year Table")

rstWeekData.Index = idxWeekNum.Name

The form this field is in is in the front end and the table I'm referring
to
is linked to a table in the back end. When the code tries to run, I get
an
error message when it gets to the 2nd line listed above. The error
message
is:

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

Prior to creating the back end database I didn't have any problems. Is
there anything I can do to get it to work with the linked table? Thanks.

You can only use the Index property on table-type recordsets, and you can't
open a table-type recordset on a linked table. When the table was local,
before the split, OpenRecordset on the table would give you a table-type
recordset by default. That's why it worked before.

 

[Amy]

If I use a WHERE clause, will I be able to make changes to the underlying
table? I have a combo box where the list is driven by a booth table where
the booth is marked both active and available. However, when the guys
selling the booths want to add a booth (usually because they are cutting a
booth up), I want Access to check and see if the booth already exists as an
inactive booth (usually because it was combined into another booth) because
the booth table is indexed to only have a booth number used once unless the
booth type is Subsidiary. If the booth exists as an inactive booth, I want
the system to mark it active and available. Can I do that it I open the
recordset as a SELECT with a WHERE clause?