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

Ie
Global cnn As New ADODB.Connection

Public Function Connect() As Integer

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

This function would open my connection when my users first enter my application
And then
Private Sub Command6_Click()
If Connect = 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

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

Private Sub Form_Close()
cnn.Close
Set cnn = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
Call Connect
End Sub
End Function


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

It would in fact stay open all the time.

This puts a burden on SQL and the network. If both are lightly loaded, that would be no concern, of course.

But one of the themes to keep in mind to produce the best programs is scalability. This is the idea of how your product will behave as it is called on to be used by a larger user base, and pressed into more service. A program with good scalability withstands this better.

If I were doing what you seem to be doing, I would open a connection at login to verify that the credentials are valid, and then close the connection. When you are going to be hitting the DB again and again, as in a loop that runs one query after another, I would open the connection before the loop, and close it after exiting the loop. This makes for good performance, but minimizes the program's effect overall on databse or network performance.

Don't rely on If Connect = 1 Then. The state of that variable is a snapshot of what was, not an indication of what is. (The server might have had a meteorite land on it since...)
Use If cnn.State = 1 Then or better, avoid "magic number" (the "1", in this case): If cnn.State = adStateOpen Then (if I have that constant right; working from memory here).

I realize that     is the typical algorithm you see all over the place, but, since the SQL is used just once, dimming and loading a variable, then using it just once, takes unnecessary time:     With is not associated with undertaking. So you can make things clearer than     with     Change Connect to a Sub, and use a routine name that isn't quite such plain English, Like OpenConnection().

Add a CloseConnection Sub if you like, or just use cnn.close.

If you maintain what you have, use parens when receiving the value: If Connect() = adStateOpen Then.

If you switch to using a Sub, then Call Connect would lose the "Call" qualifier.

You really should use more consistent indenting; it will help you as you return to previously developed code, and will make your postings easier to follow. (In this forum, preceding your listing with "code" in square brackets, and ending it in square brackets with a "/" will create fixed-pitch font that will retain the formating that you use in your post. Like this, but without the spaces inside the brackets:

[ code] Dim This As Variable[ /code]
Like this[ code] Dim This As Variable[ /code]period produces:
Like this period

(The line feeds are produced merely by using the [...][/...])

Does that help?

Yeah that was great, thanks very much for the tips :)