VB4 - ODBC - MS SQLServer Data Access Problem 
Author Message
 VB4 - ODBC - MS SQLServer Data Access Problem

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

ODBC - Creating a RecordSet

To create a dynaset using a parameterised querydef of attached {in Msaccess} table(s) of  

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.

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?


Sat, 18 Jul 1998 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. VB4 - ODBC - MS SQLServer Data Access Related Problem

2. VB4 - ODBC - MS SQLServer 6.x Related Problem

3. Migrating 16-bit ODBC DLL (SQLServer) to 32-bit ODBC DLL (MS Access)

4. MS Access data via ODBC in VB4?

5. MS Access data via ODBC in VB4?

6. Database Programmer Position (ODBC, MS Access, SqlServer, Oracle, Sybase)

7. ADO-ODBC and MS SQLServer problem...

8. Help: VB4.0, ODBC, SQLServer 6.5 - problem

9. ODBC 3.5 may slow down the VB4 or VB5 to access MS SQL6.5

10. NEWBIE HELP!!! VB4, SQLServer 6.5, and ODBC

11. VB4, ODBC, and SqlServer Store Procedure Result Codes

12. VB4/16 Connect to SQLSERVER using ODBC API Functions


Powered by phpBB® Forum Software