Solved

How can I force an RDO and ADO connection to share the same ODBC connection handle?

Posted on 2001-07-11
5
276 Views
Last Modified: 2013-11-23
I am working on a database program that utilizes over a hundred forms, many of which contain RDO Data Controls.  We would like to be able to migrate the RDO code to ADO code, particularly since Visual Basic.Net is going to have reduced RDO functionality, and the next Visual Basic release may not even support RDO anymore.  However, the process of converting the code and data controls is going to be extremely time consuming.

The problem is that even though ADO and RDO are both going through ODBC (the ADO provider is MSDASQL, at least until we can phase out RDO completely), they cannot share the same ODBC connection handle.  This means that we either have to upgrade the entire program all at once (which means that we cannot issue program updates until we remove all of the RDO connection objects and data control objects), or we need to double the number of connection handles going to the server (not an acceptable solution, since we do not want to increase our site license).

I have come up with a temporary solution: in the ADO WillConnect event I can close the user's RDO connection and refresh the ADO recordset, and in the RDO BeforeConnect event I can close the user's ADO connection and refresh the RDO resultset.  This would allow us to continue to issue program updates between the time that we begin the RDO->ADO migration and the time that the migration is completed.  However, this is not an ideal solution, since it will slow down the program considerably (even using ODBC connection pooling or saving recordsets), and it will also prevent the user from manipulating data on two different forms at once if those forms happen to use different connections.

Is there some way to create a single ODBC connection handle and force my RDO and ADO connections to share that handle between them?
0
Comment
Question by:JimBarron
  • 3
  • 2
5 Comments
 
LVL 4

Expert Comment

by:VincentLawlor
Comment Utility
What error are you seeing ?

Am I correct in assumin you are using SQL Server ?

If you are using ODBC there shouldn't be a concurrency problem. Have you checked the number of concurrent connections on SQL ?

Have you tried changing your Provider to SQLOLEDB and connect directly to the database rather than going through ODBC.

Vin.
0
 

Author Comment

by:JimBarron
Comment Utility
The error that I am seeing is:
Run-time error '40002':
IM005:[Microsoft][ODBC Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_DBC failed.

This error is generated after I create a new ADO connection, a new RDO connection, and give the ADO.ConnectionString and RDO.Connect properties the same connection strings.  When I attempt to open both connections then this error is issued on the second connection attempt.  The problem is that each user is only going to be allowed to have one open connection handle to the server, so for my test program I went to the ODBC Data Source Administrator and limited the number of open connection handles to '1'; if not for this my code would work, but in a real-life situation the server would overload with connection handles.

Yes, I am using SQL Server.

The trouble is not that one connection locks the other connection out, the trouble is that the two connections generate two different ODBC connection handles despite the fact that they are both going through identical ODBC connections.  I'd like to be able to put my ADO and RDO connections on the same ODBC connection handle.

If I change the provider to SQLOLEDB then the error does not occur, but I still have the problem of having two connection handles per user (except that now only one of the connections is ODBC, while the other is OLEDB).  The reason that I'm using MSDASQL for the time being is because the ODBC connection is the closest shared connection point that my ADO and RDO connections have; if I can get them both to use the same ODBC connection then my problem is solved.  SQLOLEDB has no shared connection point with RDO since it never touches ODBC, so I'm pretty sure that if what I want to do is even possible then I'll have to se MSDASQL as my provider.  However, once all of the RDO code is removed from the program, I will be changing the ADO connection provider to SQLOLEDB.
0
 

Author Comment

by:JimBarron
Comment Utility
The error that I am seeing is:
Run-time error '40002':
IM005:[Microsoft][ODBC Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_DBC failed.

This error is generated after I create a new ADO connection, a new RDO connection, and give the ADO.ConnectionString and RDO.Connect properties the same connection strings.  When I attempt to open both connections then this error is issued on the second connection attempt.  The problem is that each user is only going to be allowed to have one open connection handle to the server, so for my test program I went to the ODBC Data Source Administrator and limited the number of open connection handles to '1'; if not for this my code would work, but in a real-life situation the server would overload with connection handles.

Yes, I am using SQL Server.

The trouble is not that one connection locks the other connection out, the trouble is that the two connections generate two different ODBC connection handles despite the fact that they are both going through identical ODBC connections.  I'd like to be able to put my ADO and RDO connections on the same ODBC connection handle.

If I change the provider to SQLOLEDB then the error does not occur, but I still have the problem of having two connection handles per user (except that now only one of the connections is ODBC, while the other is OLEDB).  The reason that I'm using MSDASQL for the time being is because the ODBC connection is the closest shared connection point that my ADO and RDO connections have; if I can get them both to use the same ODBC connection then my problem is solved.  SQLOLEDB has no shared connection point with RDO since it never touches ODBC, so I'm pretty sure that if what I want to do is even possible then I'll have to se MSDASQL as my provider.  However, once all of the RDO code is removed from the program, I will be changing the ADO connection provider to SQLOLEDB.
0
 
LVL 4

Accepted Solution

by:
VincentLawlor earned 150 total points
Comment Utility
Sorry don't know what else you can do

Why have you limited the number of connection handles. This does not violate your licence agreement.

If you look at SQL Server you should see that the Maximum number of Concurrent user connections is 0 by default i.e. infinite.

If this voilated your license agreement then there will be thousands of companies in trouble.

Most of our systems are TCP/IP based and accept incoming requests that must be logged to a SQL Server Database this resides on another Server. All our connections go through the same ODBC DSN with connection pooling swithed on.

Technically this is the same user connecting to the database everytime.

As far as I am aware your licence agreement extends to the number of Users not connections to the database.

Vin.

 
0
 

Author Comment

by:JimBarron
Comment Utility
Thanks for your help.  I suspect that my boss doesn't know what he wants and has sent me on a wild goose chase.  Oh well; at least I get paid by the hour. :)
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now