ADO Question - Please help - How To Keep one connection open

ADO Question - Please help - How To Keep one connection open

Post by Lior Bara » Mon, 04 Oct 1999 04:00:00



I'm using ADO 2.1 in Visual Basic 5, against SQL Server 6.5.
Every time I Open a recordset (or execute a command),
a new connection to the Database is opened.
I think it reduce performances and I want to keep only one connection to the
DB open for
every Instance of the application (or the fewer connections possible).
Anyone?
Help will be appreciated. Quick help will be just Great...
Lior Baram
 
 
 

ADO Question - Please help - How To Keep one connection open

Post by <grma.. » Fri, 08 Oct 1999 04:00:00


Lior -

You just need to point the recordset to a connection object that you've
opened explicitly by using the ActiveConnection property of the recordset,
as in :
Dim dbConnection as new Connection
dbconnection.open "connect string here"
dim rs as new recordset
rs.activeconnection = dbConnection
OR:
rs.open "SQL string here", dbConnection

All recordset and command objects can be used against the same connection to
save on connections.

- Gregory MacKay


>I'm using ADO 2.1 in Visual Basic 5, against SQL Server 6.5.
>Every time I Open a recordset (or execute a command),
>a new connection to the Database is opened.
>I think it reduce performances and I want to keep only one connection to
the
>DB open for
>every Instance of the application (or the fewer connections possible).
>Anyone?
>Help will be appreciated. Quick help will be just Great...
>Lior Baram


 
 
 

1. ADO Connection: Do you ever keep ONE open; rather than open/close constantly???

It would be nice to just keep one connection always open. In practice that
creates a memory leak that will eat your resources. We have a very large app
that kept one connection open. This caused all kinds of misleading
problems/errors. It tooks us about 4 months to track it down to a database
connection that was constantly open.

All my new projects use the open, get data, close sequence. I haven't had
this type of problem again, I'm happy to say :-).

One work around is that ADO lets you keep a recorset open even when you
close the database. This recordset will reside in memory. This works well
when you only require a snapshot of your data. It is preferable (and
quicker!) to open the database if you are going to update/edit/append to the
database.

HTH,

Hector Sosa, Jr
Pacific Communications


2. Q: FoxPro and ADO Connection collision... (related to Q260856)

3. keeping more than one database open at a time

4. Restoring Databases

5. One Open Connection + Many Open ResultSets

6. ANNOUNCING Intranets Unleashed

7. Questions about refreshing datas (VB6+ADO+SQLSERVER2000) - please help (Newb question)

8. Can I use sql2000 enterprise manager with sql 7 databases?

9. Newbie ADO Question on opening connection

10. Questions about refreshing datas (VB6+ADO+SQLSERVER2000) - please help (Newb question)

11. 1'st step Help please - connection->open

12. FMP 4 keeping ISDN connection open??