Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Location
    NH
    Posts
    87

    Unanswered: ADODB.Recordset Seek/Index problem

    Hi All,

    I'm trying to call the ADODB.Rs 'seek' method and am having problems. Here's the situation:
    Code:
    Dim rs As ADODB.Recordset
        Dim con As ADODB.Connection
        Set rs = New ADODB.Recordset
        Set con = Application.CurrentProject.Connection
        
        rs.Open "SELECT * FROM t_cycle_time_original", con, adOpenKeyset, adLockReadOnly, adCmdTableDirect
        If rs.Supports(adSeek) And rs.Supports(adIndex) Then
            'Do something
        Else
            'Do something
        End If
    I get a run-time error '3251', with description "Current provider does not support the necessary interface for index functionality" when I try to access the index/seek methods and both support calls return false.

    I can utilize these methods outside of this database when I create a brand new connection in another database while this one is close, it looks like there is something wrong with utilizing the 'Application.currentproject.connection'.

    I have tried creating a new connection in this situation, rather than using the old one and I get a connection error stating 'User Admin on machine xxxx has placed this database in a state where it cannot be connected.'

    Any help would be appreciated...

    Thanks
    Warren
    Last edited by WarrenM; 08-30-04 at 18:07.

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250

  3. #3
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Maybe a better question is:
    Which is more efficient, Seek with a recordset or query with specified criteria?

    Also, the database is in 2k2 format and I also tried creating a brand new connection, i.e. con = New Adodb.Connection , con.open "provider='Microsoft.jet.oledb.4.0;..."; that still didn't work.

    It looks as though seek may not be supported when used with the currently open database?

    What I should probably do is just run a query which updates the data that I want (which is the end-result that I want).

    Thanks,
    Warren

  4. #4
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Thanks poliarci.

    This is a database I created on my machine and there hasn't been any changes to it. All my references are in-line and everything else looks good...

    -Warren

  5. #5
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    My thoughts are that there might be a system table that may give you the currently opened db/project. Perhaps something like "db.Connection.QueryDefs". What do you think?

  6. #6
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    That's what I thought 'Application.CurrentProject.Connection' was?
    I can't find anything else similar...

  7. #7
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Interesting...

    I got home and created a brand new database and a test table. I then wrote the same code to seek and it worked.

    Something's up...

    Anyway, once I do figure it out though, the question still remains, which is better to get at data and update? Grab it with a RecordSet and update it or write a query to update the data and pass in the values.

    -Warren

  8. #8
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Apparently the cause of the problem was the fact that I was using a SQL statement as a source in the recordset.open method and not just a table. I don't think it stated that anywhere in the documentation?

    At any rate, should I use recordsets to update data or update sql?

Posting Permissions

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