recordset editing linked tables

  • Thread starter Thread starter charles
  • Start date Start date
C

charles

using recordsets to access table data in VBA. This worked
great wen my tables were in the same database... now I
have them seperated...and the tables are linked to my APP
database.

but now none of the recordset tools work. My code is
below:

Dim rsParam As DAO.Recordset
Dim dbs As DAO.Database
Dim result As Integer

Set dbs = CurrentDb

Set rsParam = CurrentDb.OpenRecordset("tblParameter")

With rsParam
.Index = "PrimaryKey"

i get the error:
3251 operation is not supported.

what do i need to do to be able to search through my
tables that are linked?

thanks, in advance.
 
charles said:
using recordsets to access table data in VBA. This worked
great wen my tables were in the same database... now I
have them seperated...and the tables are linked to my APP
database.

but now none of the recordset tools work. My code is
below:

Dim rsParam As DAO.Recordset
Dim dbs As DAO.Database
Dim result As Integer

Set dbs = CurrentDb

Set rsParam = CurrentDb.OpenRecordset("tblParameter")

With rsParam
.Index = "PrimaryKey"

i get the error:
3251 operation is not supported.

what do i need to do to be able to search through my
tables that are linked?
Click to expand...


The issue is that you have not specified what type of
recordset you're opening. When you open a recordset based
on a local table without specifying the recordset type,
Access opens it as a table type that can use Index/Seek.
Otherwise the recordset is opened as a dynaset type that
uses FindFirst. Linked tables can not be opened as a table
type recordset, so they are opened as a dynaset type
recordset.

You can deal with this issue in either one of two ways. One
is to remove the Index/Seek and replace it with FindFIrst.
FindFirst is more versatile than Index/Seek and, in 99.9%
situations, the speed of locating the desired record is
insignificant so this is the usual way to go.

Dim rsParam As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Set rsParam = dbs.OpenRecordset("tblParameter", _
dbOpenDynaset)
With rsParam
.FindFirst "PrimaryKey = " & somevalue
If Not .NoMatch Then
. . .

If you have one of those rare situations where the speed of
Index/Seek is important, then the approach is to open the
backend database and open the recordset against that db
instead opening the recordset against CurrentDb.

Dim rsParam As DAO.Recordset
Dim dbFE As DAO.Database
Dim dbBE As DAO.Database
Dim tdf As DAO.TableDef

Set dbFE = CurrentDb()
Set tdf = dbFE.TableDefs("tblParameter")
Set dbBE = OpenDatabase(Mid(tdf.Connect, 11)
Set rsParam = dbBE.OpenRecordset(tdf.SourceTableName, _
dbOpenTable)
With rsParam
.Index = "PrimaryKey"
.Seek "=", somevalue
If Not .NoMatch Then
. . .
 
Back
Top