I have a problem which I have described as below.,

PROBLEM TOPIC:
ODBC - Creating a RecordSet

EXPECTED BEHAVIOR:
To create a dynaset using a parameterised querydef of attached {in Msaccess} table(s) of  
MSSQLServer.

PROBLEM  DESCRIPTION:
The application is a Client/Server app. using MSSQL Server as back-end DBMS, ODBC (Microsoft's
ODBC Driver for SQL Server),  and VB4 app as front-end.  The MSSQLServer Tables are attached to
MSAccess.  I am using a TList Outline control.  When I attempt to add a record,  i.e., a branch
the application goes through a set of function written by us.

One of those functions uses a QueryDef defined in MSAccess and a parameter,  an ID to create a
dynaset using CreateDynaset method.  The dynaset created is passed a Dynaset object
variable (Global) called 'gdynReq'.  

When adding a new record,  this particular function is called several times and dynaset created
successfully for next action in the process.  After a few times,  this query fails saying "Query
timeout".  The query timeout property in Query design window of the specific query is set to 10
seconds {default is 60seconds} in MSAccess.  

The code typically looks like this.,

 Set Q = db.OpenQueryDef(<Query Name>)
 Q.FieldName = <Parameter Variable Name>

 Set gdynReq = Q.CreateDynaset()    <--- At this point fails

We initially suspected that there is some conflict with 'gdynReq' as this obj. var is set several
times and it is not clean when accessed at the failure point.  To make doubly sure,  we have
explicitly set this to NOTHING prior to statement that creates the dynaset.  Still the same
problem when run next time.

We are using a product called SQL Inspector, using which a log can be maintained of the Jet
Engine as well as the ODBC calls.

In the log we noticed the following.,

 1. Log of failed process:
    Jet Engine process time 89%  9sec .. ms {milliseconds)
    ODBC process time       11%  2sec .. ms

 2. Log of succeeded process:
    Jet Engine process time 49%  0sec some 60 ms
    ODBC process time       51%  0sec some 70 ms

In the case 1,  I believe that eventhough, Jet time includes ODBC time
still it has taken about 7 seconds to process.  This means,  that thare is
some problem at the level of Jet itself.  We have been struggling with the
code for the past 3-4 days.

Also please note that increasing the QueryTimeOut property value only takes
additional time to wait and fail.  It does not help at all.

OTHER COMMENTS:
I presume that the problem I have described is of some help.

Can anyone throw some light?  What could be the possible cause for
this problem?  Can anyone please help me?

RAMAN RAO