Re: ADO data control, proper use

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Zap (Zappa_at_141.com.removethis)
Date: 08/08/04 

Date: Sun, 8 Aug 2004 15:52:27 -0700

Thanks for the input Val. I knew that advice was coming. I just
apprehensive about eliminating the data controls and switching over to the
proper method, that you demonstrated. So do I close the connection after I
get the recordset that I want from the database or do I wait to close the
connection when the program closes? With loConnection and I get multiple
recordsets from that ex. rs1, rs2, etc.? Thanks again

"Val Mazur" <group51a@hotmail.com> wrote in message
news:%23aw2hmXfEHA.3612@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> 1,2. It is not a good idea to open multiple connections from the same
> application and keep them alive. First of all, you usually need just one
> connection, which could be used to get data. Second, opening of the
multiple
> connections is expensive and requires additional resources from the server
> to maintain them. Third, in a multi-user environment you could face
> situation, when other users will experience problem to connect to the
server
> if you reached limit for the number of opened connection. Fourth, you
would
> experience performance impact because of it. My personal opinion is to
avoid
> using ANY bound controls and use ADO library directly. In this case you
> could open connection, get data and close it. It would give you much
> flexibility, then using data controls. Since ADO supports pooling of the
> connections, you should not experience any issues opening and closing
> connections several times and it would work fast. In a case of bound
> controls, you cannot control opening of the connections and each control
> will open its own connection
>
> 3. In a case if you use ADO library directly, then you would need to close
> connection and set connection variable to Nothing. Also never declare and
> instantiate ADO variable in a declaration part, because it could lead to
the
> memory leaks. Your code should look like
>
> Dim loConnection as ADODB.Connection
> .....
> Set loConnection = New ADODB.Connection
> loConnection.Open .........
> .......
>
> loConnection.Close
> Set loConnection=Nothing
>
> --
> Val Mazur
> Microsoft MVP
>
>
> "Zap" <Zappa@141.com.removethis> wrote in message
> news:H4adnS87wOQe7IvcRVn-hA@buckeye-express.com...
> >I am in the process of converting a VB program to backend MS SQL from
> > Access. The program had used the DAO data control to communicate with
> > Access. This program has many data controls (8-10) incorporated into
it.
> > Switching over to the ADO control is moving along but I have encountered
> > several questions along the way.
> > 1. When opening the program there is currently five connections to
SQL
> > Server that remain open and sleeping. There will probably be another
5-8
> > more connections by the time the program is complete. Is this alot of
> > connections for a single program to have open with SQL Server. There
will
> > probably be a max of 6 users connected at any one time which would yield
> > about 60 connections to SQL Server, any problems with this?
> > 2. Is there a way to close the ado data control connection after a
> > recordset is retreived from the server and then open it up when needed
> > again.
> > 3. Is there a proper way to close the connection and set the
> > connection to nothing upon exiting the program?
> >
> > I'll think of some more questions, I'm sure but answers to these will
help
> > for now. And yes I know that I should hard code the connections to SQL
> > Server and not use the data control but those were in place and I'm not
> > advanced enough to tackle someone elses project that way. I think I can
> > handle the minor syntax changes between ADO and SQL Server.
> >
> > VB 6 on Win XP
> > SQL Server 2000 on Windows 2000 Server, Database size about 1gig.
Adding
> > records for a radiolog, contacts, and reports in a police department.
> >
> > Thanks,
> > Zap
> >
> >
>
>

Relevant Pages

  • Re: Remoting from a control hosted in IE
    ... But when server tries to establish the callback connection, the control is ...
    (microsoft.public.dotnet.security)
  • Re: Remote Web Workplace on locked-down public computer
    ... server, but nothing else. ... The Active X control is required to connect to desktops with RWW, but not to read mail with OWA. ... One workaround would be to forward incoming connections to port 3389 to the user's PC, and instruct him to use the Remote Desktop Connection program directly. ... I am not sure if an Internet cafe would permit the creation of the VPN client though. ...
    (microsoft.public.windows.server.sbs)
  • Re: Preventing login as NT AUTHORITYANONYMOUS LOGON
    ... I'm thinking that one control may be closing the connection while another ... It runs fine on one server, ... > There is no reason it should even be trying to login to using NT ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: 20 sec delay opening tcp connection from within IE hosted cont
    ... > end of the TCP connection (in the control)? ... >> control which connects to the server. ... >> connection, and the control connects to it when initialized. ...
    (microsoft.public.win32.programmer.networks)
  • Re: ADO data control, proper use
    ... recordset and close connection. ... you still be able to work with the disconnected recordset on a client side. ... >> flexibility, then using data controls. ...
    (microsoft.public.vb.database.ado)