Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007

    Unanswered: Global ado connection from vb to sql

    I am new to using visual basic with SQL, previously I have used Access.
    I was wondering if anyone could answer these few questions for me.
    If I was to create a login form asking for username and password, and then once that
    Was supplied I then connect to my SQL Server using an ado connection, providing I didn’t close the form down (hid it instead), would my connection stay open all the time whilst my user is in my application?
    And if I declared the adodb connection as a global variable, whatever form I am in, could I then just use this to call the connection

    Global cnn As New ADODB.Connection

    Public Function Connect() As Integer

    cnn.Open "Provider=sqloledb;" & _
    "Data Source=SERVERNAME;" & _
    "Initial Catalog=DATABASENAME;" & _
    "User Id=USERID;" & _
    Connect = cnn.State

    This function would open my connection when my users first enter my application
    And then
    Private Sub Command6_Click()
    If cnn.State = 1 Then
    Dim rs As New ADODB.Recordset
    Dim strsql As String
    strsql = "Select * from Projects"
    rs.Open strsql, cnn
    With rs
    Me.projectid = rs.Fields("ProjectId")
    Me.txtname = rs.Fields("name")
    Me.organisation = rs.Fields("organisation")
    End With

    Set rs = Nothing
    'Unable to establish a connection
    End If
    End Sub

    Private Sub Form_Close()
    If cnn.State <> 0 Then
    Set cnn = Nothing
    End If

    End Sub

    Private Sub Form_Open(Cancel As Integer)
    If cnn.State <> 1 Then
    Call Connect
    End If
    End Sub

    Is this a good way of doing this rather than opening a new connection everytime I wanted to do something with SQL?

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 12
    There is nothing wrong with the global object declaration, however you should only have your connection open when communicating with the server.

    It may be a bit of a pain and require more coding, but it will make your application much more efficient in amulti user environment.

    Oh, and moved to VB topic.
    Home | Blog

Posting Permissions

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