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?

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.

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".

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.

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.

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.

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:

'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>

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.

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>