Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time delay with ADP/SQL functions (A2002/SQL7)

    Greetings everyone!

    I've run into a curious problem. I have 2 forms in an ADP (connected to SQL 7 backend) - one is single-record (form-view), one is data-sheet. Both forms share the same data source - a simple table with about 2600 records. The database structure seems to be pretty efficient.

    I'm trying to allow the user to "synchronize" the two forms without filters - basically using the Bookmark properties to allow them to open the Datasheet view to the same record that's selected in the Single-record form and vice versa. I've tried several methods (including Bookmarks and ADO's Find method with a RecordsetClone).

    The problem is this: Everything works perfectly if the record comes very early in the table. However, it fails if the record is too far down in the recordset. If I add a breakpoint and step through the code, it works every time no matter where the record is located. My guess is that there is a time lag for the server finding the record if it's too far down in the recordset. (For the record, there are no sorts or filters - just natural order of all records.) Stepping through with a debug seems to work because SQL has time to do its thing, whereas running the code normally fails because the code runs faster than SQL...(I guess... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>)

    I tried using Bookmarks at first, then tried making a recordsetclone and the Find method of the ADO Recordset object. Still no luck (even using an indexed field).

    What's worse is that the SQL server and Access are currently running on the SAME machine (for development purposes). I imagine the delay would be even worse when they're across a network.

    Am I trying to make an ADP behave like an MDB? Am I missing something important?

    Thanks in advance! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

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

    Re: Time delay with ADP/SQL functions (A2002/SQL7)

    You need to use the Seek method instead of Find. That will give you a radical speed boost, but it means you need to open your recordset with the adCmdTableDirect option so you can make use of Seek. Seek uses indexes, so it doesn't have to examine each record to find the match.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time delay with ADP/SQL functions (A2002/SQL7)

    Hi Charlotte,

    Thanks for your great advice about using the Seek command. That's a winner!

    I'm having some issues getting Seek to work. I've found several past Lounge conversations that discuss different aspects of using this, however they don't seem to address the problem I'm having. Also, since Microsoft has made some drastic Knowledge Base changes, I can not get to the great article you referred to in another post (ACC2000: Using seek Method with ADO Against a Jet Recordset [Q243465]).

    I'm using an Access 2002 ADP front-end, connected to SQL Server 7 backend. My table (tbl_Pieces) has a primary key index on the field "pieceID".

    My code is as follows:<pre>Dim rst As ADODB.Recordset
    Dim lngPieceID as Long
    lngPieceID = Me.pieceID
    Set rst = CreateObject("ADODB.Recordset")
    DoCmd.OpenForm "frmPieces", acNormal
    rst.CursorLocation = adUseServer

    'Crashes HERE: ("Specified Index does not exist.")
    rst.Index = "PK_tbl_Pieces"

    rst.Open "tbl_Pieces", CurrentProject.Connection, adOpenKeyset, adLockReadOnly, adCmdTableDirect
    rst.Seek Array(lngPieceID), adSeekFirstEQ
    '....More code below to close and destroy the recordset</pre>

    I've tried using creating and using several different Index names. Do I have to refer to it with an ADOX.Catalog?

    If I leave out the rst.Index... line, I get the message that the "Current Provider does not support the necessary interface for Index functionality."
    Could it be that SQL 7 does not support this feature?? I have also checked the rst.Supports(adIndex) and rst.Supports(adSeek) properties, which both return False.

    I imagine I'm missing some small detail, but I've checked out examples from several sources which all seem to work with very similar methods to mine.

    As always, thanks for your patient help. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Time delay with ADP/SQL functions (A2002/SQL7)

    Hi Gary,
    I dug this up from Access 97 help,

    You can't use the Seek method on a linked table because you can't open linked tables as table-type Recordset objects. However, if you use the OpenDatabase method to directly open an installable ISAM (non-ODBC) database, you can use Seek on tables in that database.

    In an ODBCDirect workspace, the Find and Seek methods are not available on any type of Recordset object, because executing a Find or Seek through an ODBC connection is not very efficient over the network. Instead, you should design the query (that is, using the source argument to the OpenRecordset method) with an appropriate WHERE clause that restricts the returned records to only those that meet the criteria you would otherwise use in a Find or Seek.


    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seek method fails... (A2002/SQL7)

    Hi Patt,

    I agree with the thought that it's not terribly efficient to use a Client-side Seek or Find through a network. It would be much better to pull this off at the Server-side. The only problem is that the intent of my earlier design was to have a Single-record form and a 2nd (slightly different) form in a datasheet format. I wanted the user to be able to navigate to any record on either form, then click a button to open the other form to the selected record (but without filtering - so all other records would still be available).
    After much thought, I decided to change my design. It won't be quite as friendly to the user, but it's a better and more efficient design.

    I still don't quite understand why I can't run a simple Seek command with an ADO recordset - especially when I'm using the same techniques I've found in several different examples... Fortunately, it's a non-issue for the current project. Maybe I'll come across it again in the future.

    Thanks for the information.

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

    Re: Seek method fails... (A2002/SQL7)

    You can't use Seek because it only works on a table, so your recordset has to be opened properly. The article I referenced has apparently been retired, since the number now applies to a different article; but here's the code from the original. You'll see even with Jet you have to specify a table:

    <pre> Function SeekRecord()
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set conn = New ADODB.Connection
    Set rst = New ADODB.Recordset

    'Set the connection properties and open the connection.
    With conn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "C:Program FilesMicrosoft " _
    & "OfficeOfficeSamplesNorthwind.mdb"
    .Open
    End With

    With rst
    'Select the index used in the recordset.
    .Index = "PrimaryKey"

    'Set the location of the cursor service.
    .CursorLocation = adUseServer

    'Open the recordset.
    .Open "Order Details", conn, adOpenKeyset, _
    adLockOptimistic, adCmdTableDirect

    'Find the customer order where OrderId = 10255 and ProductId = 16.
    .Seek Array(10255, 16), adSeekFirstEQ

    'If a match is found, print the quantity of the customer order.
    If Not rst.EOF Then
    Debug.Print rst.Fields("Quantity").Value
    End If
    End With
    End Function</pre>

    I'm not quite sure what you were trying to do with that first recordset but you normally instantiate a recordset object by using the New keyword, not CreateObject. CreateObject is used for automation between applications, so I don't understand why you would try to use it to open a recordset. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

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

    Re: Time delay with ADP/SQL functions (A2002/SQL7)

    That only applies to ODBCDirect, not to ADO recordsets, Pat.
    Charlotte

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Time delay with ADP/SQL functions (A2002/SQL7)

    Thanks Charlotte, I haven't been into ADO yet.
    Pat

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seek method fails... (A2002/SQL7)

    Hi Charlotte,

    Thanks for the code snippet. That's basically the same thing I'm doing (including the acCmdTableDirect - see code sample above), except that I'm using Currentproject.Connection to directly open a recordset (rather than using a connection object). I'm going through SQL Server (with an ADP) instead of using Jet. I wonder if SQL Server 7 doesn't support this feature (although, you'd think it would).

    Regarding the CreateObject - tha'ts just a habit from writing way too much ASP lately. I read somewhere that was an alternate way to create ADO objects in Access as well. Thanks for the tip - I'll go back to using the better method.

    But, the good news is not to worry. As I mentioned before, I've changed my design for the better and no longer need this technique. At this point, it would just give me peace of mind, which isn't really worth anyone else wasting a great deal of time.

    As always, thank you very much. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

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

    Re: Seek method fails... (A2002/SQL7)

    It might be an alternate way to create a recordset, but you you still have to provide the rest of the information in order to open the recordset before you start trying to use it. And seek still only works on a table-type recordset. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

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