Solved

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

Posted on 2001-07-11
5
278 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
ID: 6275091
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
ID: 6277538
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
ID: 6277588
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
ID: 6277765
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
ID: 6277817
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

914 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

18 Experts available now in Live!

Get 1:1 Help Now