Style/Performance Question

[Anonymous]

I have declared a number of "global" variables at the top of a module for
use by sundry procedures. Should I declare them Private if I want to ensure
they are only accessed within that module?

Also, what happens to successive calls to open an ADODB.Connection? For
example, I would like to open a connection in a top-level procedure for use
by all procedures called after it and then close it when everything is done.
However, I'd also like to check that the connection has been made and
re-establish it in subsequent procedures. If I simply call .Open is the
connection broken and remade? Is that expensive performance-wise? I have a
small example included.

I know that this will work, but is it good style? Is there a better way to
do what I'm after? Should I pass the ADODB.Connection object down the call
graph as a parameter to the procedures that use it? Is it good practice to
declare the connection string the way I've done?

Cheers,
Rob

Option Explicit

Public cn As New ADODB.Connection
Public rsData As New ADODB.Recordset
Const strDBConnect = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=10.20.1.72;" & _
"DATABASE=caddb;" & _
"USER=autocad;" & _
"PASSWORD=drAw4mE;" & _
"OPTION=3;"

Public Sub TopLevelProcedure()

' Initialize Global Connection object
cn.ConnectionString = strDBConnect
cn.Open

NextLevelProcedure
'Call some other things here

cn.Close

Set rsData = Nothing
Set cn = Nothing
End Sub

Public Sub NextLevelProcedure()
' Don't assume that we have a connection object.
cn.ConnectionString = strDBConnect
cn.Open

strSQL = "INSERT INTO t1 (a, b, c) VALUES (1, 2, 3);"
Set rsData = cn.Execute(strSQL)
Set rsData = Nothing

End Sub