Run-time error '3251' index problem ?

Discussion in 'Microsoft Access VBA Modules' started by, Feb 28, 2006.

  1. Guest

    I have the following segment in my code..

    Set rst1 = CurrentDb.OpenRecordset("tblSegments")
    rst1.Index = "nLinkID" ' the debugger points to this line
    rst1.Seek "=", nIDvariable

    It works fine and gives the result I want, until I split the DB and
    move the tables to a back-end file. When the tables are linked, I get
    the following message:

    Run-time error '3251'
    Operation is not supported for this type of object

    What am I missing ? If I don't have the tables linked, everything is
    fine. But when I link to a back end DB, (same tables that were in the
    single .mdb file), I get that error message. When I look at the
    indexes they are visible in both the _be file and the front end
    (linked of course). This is causing big problems for me, any help
    would be greatly appreciated. Thanks.
    , Feb 28, 2006
    1. Advertisements

  2. Allen Browne Guest

    For a local table, OpenRecordset() defaults to the dbOpenTable type. For an
    attached table, it defaults to dbOpenDynaset, and dbOpenTable is not
    available. The Seek method can only be used with a dbOpenTable type
    recordset, i.e. you cannot use this with an attached table.

    So, even though Seek is very fast, many of us have given up using it.
    Although it is possible to OpenDatabase directly on the back end file and
    use Seek, it is usually much simpler to use a SQL statement to OpenRecordset
    on only the records and fields you actually want, sorted the way you need.

    You will therefore code something like this:
    Dim strSql As String
    strSql = "SELECT SegmentID, LinkID, SegmentName FROM tblSegments WHERE
    SegmentID = " & nlDvariable & " ORDER BY LinkID;"
    Set rst1 = Currentdb.OpenRecordset(strSql)
    Allen Browne, Feb 28, 2006
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.