Maintaining SQL connection state between VB6 and VB.NET interop

Question

I am converting a complex VB6 application using VB.NET and the interop toolkit. The VB6 app does a lot of DAO calls and the initial SQL connection is set up in VB6 using SQL authentication with an individual username and password obtained from a login form.

I'm slowly converting VB6 forms to .NET and calling them through the COM interop in VB6 (this works). The problem comes in where a .NET form has to populate its controls with data from the database. The .NET form then has to pass data back to VB6 forms for other data manipulation. This means that VB6 and the .NET interop control should share the SQL connection state without having the user to re-authenticate or losing the context of the connection (i.e. the current authenticated user). (Eventually all DAO will be ADO through the interop and the authentication will be done through ADO.)

How do I share this connection state between VB6 and the .NET interop?

 

Answer 1

This is somewhat contrary to how ADO.NET operates. Typically persistent connections are not maintained and are not necessary as a result of connection pooling. A connection is opened, data is retrieved or updated and the connection is closed, which releases it to the connection pool of the appplication. When a connection with the same credentials is subsequently requested by the app, the connection pool mechanism looks in the connection pool for an available connection. If a connection is available it is returned to the application. If not, a new connection is created. The connection pool mechanism helps reduce some of the overhead with respect to creating new connections.

That being said, I don't see any reason why you can't use DAO via interop with .NET and maintain a persistent connection, however; you cannot interoperate DAO connections with Classic ADO or ADO.NET connections.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

Thanks, I get what you are saying.

I believe a possible solution might be then for DAO to establish the initial authentication in VB6 and then pass the creds onto the .NET interop which will then use it when required, using an ADO connection.

Answer 3

Yes, you will have to create a new connection since DAO and ADO are different architectures, but you can use the same user ID and password that was provided for the DAO connection.

---

Paul ~~~~ Microsoft MVP (Visual Basic)