Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What recordsets support .Index and .Seek? (2000 SP3)

    I'm using ADO to open a recordset, use the .Index and .Seek properties to find an index, see if it exists if it doesn't, I'm adding a new record to the recordset. This is my code.

    ============================================
    rs.Open "tablename", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    If rs.Supports(adIndex) And rs.Supports(adSeek) Then
    .Index = "primarykey"
    .Seek Array("field1", "field2", "field3")
    'some other stuff
    else
    msgbox "recordset doesn't support .index and .seek"
    end if
    =============================================

    I'm going into the "Else" statement because the recordset does not support .Index and .Seek. Why is that? I think I have my options set correctly when opening the recordset....

    The table is a linked SQL Server table. Can't I use the .Index and .Seek methods on this type of table?

    Sarah

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: What recordsets support .Index and .Seek? (200

    Hi,
    Are you using a table linked to an mdb or a Data Project? If it's a linked table, I don't believe you can use Seek on it. You will need to open a new connection to the database where the table resides and then open your table type recordset. It should then support Seek. If it's an ADP, then I'm not sure the Access OLEDB provider supports the Seek method, whereas the Jet OLEDB provider does.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What recordsets support .Index and .Seek? (200

    The table is linked into a .mdb file.

    How would I open a new connection to the database where it resides? I know how to open a connection to the current database (currentproject.connection), but I haven't opened a connection to another database outside of the .mdb I'm working in.

    And then the database is an SQL Server 2000 database. Does that make a difference?

    Sarah

  4. #4
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What recordsets support .Index and .Seek? (2000 SP3)

    I think I've found the answer to my question.

    I was looking through the book "Access Cookbook" by Ken Getz, Paul Litwin and Andy Barron (great book by the way!) and found the answer in the article "Quickly Find a Record in a Linked Table" on page 265.

    The last sentence in the article is this: "you can't perform a seek on text, spreadsheet, or ODBC data sources."

    My SQL table is linked using ODBC so I guess that answers that .

    Sarah

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What recordsets support .Index and .Seek? (2000 SP3)

    Actually, that isn't the answer to your question. Rory already gave you the answer. Linked MDBs are not ODBC connections, although linked SQL Server tables are. I thought you said you were linked to an MDB?

    You can use code to open another Access database and use seek to locate a record in a table in that database. However, depending on what you want to do with that information, that might not be especially useful. The bottom line is that you can only use Seek on a local table. But if you open the other database as current, then the table *is* local to that database and therefore supports Seek.
    Charlotte

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: What recordsets support .Index and .Seek? (200

    Hi Sarah,
    The SQL Server OLEDB provider does not appear to support the Seek method, so you would have to use Find instead or else rework your db (e.g. so it uses a stored procedure to return the record(s) you are interested in.) It's probably easier to take the performance hit of using Find rather than recreate the whole db though!
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •