Optimizing Ado conections

[herci]

Hi.. I am kind of new using ADO


I developed an application that need to connect to a SQL Server database using ODBC. I made many connections to the database.. when I run one module that have a conection it runs fine, but when made another conection to run another module i can get the conection the database.. and can not run the queries.


How can I optimize the conectivity, in order, to conect an reconect properly to the server?


I will appreciate any kind of help
thanks

 

[Kendel]

Everytime you open a new connection, you need to set the Connection & Recordset to new.

Set Cnn = New ADODB.Connection
Set Rs = New ADODB.Recordset

 

[sunaj]

Hi,

There are basically 2 approaches:
1) Only have a connection open when you need data from the database. Remember to close the connection at set the object to nothing when you don't need it. Alternatively don't use the connection object at all, but use a connection string with the .open method of the recordset.
2)open a connection when you start the program and keep it open until the program is done. If you need more that one type of access permission you must open more that one connection.

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'