Very Computer

Hi,
  My problem occurs while connecting to DB2 via DAO 3.51
  My application opens an ODBC DB2 database. After I open several recordsets
on this database. As follows:
 set db=OpenDatabase("", 0,  0, strConnectionString)
 set rc1= db.opendatabase("SELECT * FROM TB1",dbopendynaset)
 set rc2= db.opendatabase("SELECT * FROM TB1",dbopendynaset)
 set rc3= db.opendatabase("SELECT * FROM TB1",dbopendynaset)
....

 Connection string is a regular connection string. However, when I look to
DB2's connection count, I see a new connection to each recordset. This
causes the server to halt. I've checked the ODBC trace, I can see that each
recordset opens a new connection to database using "SQLConnect". How can I
stop this?

Jan Devrim

First, I'm assuming you mean "OpenRecordset" and not "OpenDatabase" when
executing your queries.

Unfortunately, what you're seeing is the default behavior of an ODBC data
source with DAO. To my knowledge, there is no way to control how many
connections DAO opens for you.

I would highly recommend using ADO, which gives you much more control over
these factors, and, with OLEDB, features sophisticated connection pooling
that has better control over the connections opened by the data access
layer.

Hope this helps!

Steven Bras, MCSD
Microsoft Developer Support/Visual Basic WebData

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Hi,
  Yes. I wrote the post wrong. It should be OpenRecordset. Sorry

  When we use map connections to one connection using DB2 Client tools,
application started locking recordsets. We are opening them as dynasets,
there should be no locking I guess. But, It locks the table, so that you
cannot even just execute a select query using any tool.

  Is there an explanation for this? I'm stuck in this problem.
  BTW, there is no way to convert all the application to ADO. It will take
so much time and will need so many tests. We have offered client to move to
SQLServer, which the application works perfect. However, they deny to do
this.

Quote:> First, I'm assuming you mean "OpenRecordset" and not "OpenDatabase" when
> executing your queries.

> Unfortunately, what you're seeing is the default behavior of an ODBC data
> source with DAO. To my knowledge, there is no way to control how many
> connections DAO opens for you.

> I would highly recommend using ADO, which gives you much more control over
> these factors, and, with OLEDB, features sophisticated connection pooling
> that has better control over the connections opened by the data access
> layer.

> Hope this helps!

> Steven Bras, MCSD
> Microsoft Developer Support/Visual Basic WebData

> This posting is provided "AS IS" with no warranties, and confers no
rights.

> Are you secure? For information about the Microsoft Strategic Technology
> Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.

Unfortunately there is no converter that I know of that will convert your
DAO code; you will have to write that from scratch, I'm afraid.

What are "Map Connections". You state that when you use "map connections"
you see table locking. Please explain.

I highly recommend using ADO; DAO was primarily designed for use with
Access, and while it can use ODBC data sources, it is certainly not
optimized for them. Bear in mind you can combine DAO and ADO in your
application, so you can make the transition slowly if need be.

Steven Bras, MCSD
Microsoft Developer Support/Visual Basic WebData

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.