Results 1 to 15 of 15

Thread: ADO connection

  1. #1
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70

    Question Unanswered: ADO connection

    In VB6 I use more Forms where users can Add, Edit, Delete Records from more tables.

    WHICH IS THE BEST METHOD

    1. To open a connection when user start the application and I close the connection when user leave the application.

    In Login form (Public cn As ADODB.Connection)

    SirConectare_SQL = "Provider=SQLOLEDB.1" & _
    ";Password='" & Pass & "'" & _
    ";Persist Security Info=False" & _
    ";User ID='" & UserName & "'" & _
    ";Initial Catalog='" & DataBaseName & "'" & _
    ";Data Source='" & ServerName & "'"

    Set cn = New ADODB.Connection

    With cn
    .ConnectionString = SirConectare_SQL
    .Open
    End With

    OR

    2. To Open a connection in each Form and close the connection when Form is UnLoadet

  2. #2
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    The first case is not possible because

    Set cn = New ADODB.Connection (this is in Form Login)
    In other form it is necesary to set the connection again.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    The rule is that you only keep the connection open as long as needed - and no longer. What are your concerns ?

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    How many clients will use this application ?

  5. #5
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    5-8 users

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Will the security credentials be different for each user ? What is the purpose for the application and the multiple forms ? Are you thinking about opening 1 connection object that will be used for multiple recordset/command objects ?

  7. #7
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    How I can open a connection when user start the application and kepp open until user leave the application ?

    In module:
    Public cn As ADODB.Connection

    In first form when is load:
    SirConectare_SQL = "Provider=SQLOLEDB.1" & _
    ";Password='" & Pass & "'" & _
    ";Persist Security Info=False" & _
    ";User ID='" & UserName & "'" & _
    ";Initial Catalog='" & DataBaseName & "'" & _
    ";Data Source='" & ServerName & "'"

    Set cn = New ADODB.Connection

    With cn
    .ConnectionString = SirConectare_SQL
    .Open
    End With

    If I want to use "cn" in other forms is not possible (is not open)

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Are you destroying/closing the connection before the 2nd form is loaded ?

  9. #9
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    I don`t close the connection, but I close (Unload) the first form.
    The connection is closed ?

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    Where are you declaring - Public cn As ADODB.Connection ?

  11. #11
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    This declaration is made on a Module

    Public cn As ADODB.Connection

    In the Startup Form (where user input UserName and Password)

    Set cn = New ADODB.Connection

    With cn
    .ConnectionString = SirConectare_SQL
    .Open
    End With

    If I want to use this connection on other forms I can't because is Closed.
    I want to open the connection only one time(when application startup), and if it possible to use the connection in all form.

  12. #12
    Join Date
    Nov 2003
    Posts
    76
    Open the connection in a module or class when login in form successfull. Close it when application main form is closed.

  13. #13
    Join Date
    Oct 2003
    Location
    Arkansas
    Posts
    15
    This Looks Like VB code If it is Do not forget to set the DBconnection ( and all all objects associated to it) = nothing

    set cn = nothing

    I personnally like to use 1 public DB connection instead of multiple connections per app.

    Hope this helps.
    LJ
    Hope is the feeling that the feeling that you have will not last very long.

  14. #14
    Join Date
    Oct 2003
    Location
    Manila, Philippines
    Posts
    20

    Follow up question

    Hello Gurus out there!

    I want to know what would be the best method in opening a connection to server:

    1. a connection (declared globally) that is open once during Login and access thru all forms and be closed only when application is terminated
    What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

    2. a connection is opened only when needed but everytime i execute a query against the database i have to open also that connection and terminate when it is not used....What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

    Secondly, how can we know the resources used by the users that are connected to my SQL SERVER in terms of memory usage and CPU?

    Im using VB/FOxPro and I want to know the best practice in terms of opening a connection to the database coz Im expecting to have 20 or more users online simultanously and hook to my server as soon as we are finished with our system.

    I hope you can light up our minds with these concerns.

    Thanks,

    Bernie

  15. #15
    Join Date
    Feb 2002
    Posts
    2,232
    Bernie - What driver are you using ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •