Declarations --> Looking to get declarations right & a great ADO database example for those interest

[Matt B]

Hey,

I am currently developing a Visio solution which is linked to an Access
Database.
As a bit of a newbie to VB I would like to get my declarations in the
correct scope, particularly with variables I am using over and over in
forms, to access the database.

I have attached some sample code of a typical sub which accesses the
database (using ADO), executes an SQL string and returns a resulting record
set. I have a number of Modules and Forms with this type of code.

I am using a variable 'dbFullName' to hold a string which is the full path
of the database I wish to access.
Currently I am repeating this string in each sub.... which is bad for all
the reasons you can think of.
I am also repeating the MyConn and MyRecSet place holders in each sub,
declaring them with Dim.

Now I know I can declare 'MyConn' and 'MyRecSet' in 'ThisDocument' as Public
Variables thus they will be available all the time and I thought about
assigning the database path to the variable dbFullName in
ThisDocment.InitializeDrawing() but is this good programming practice?
Also when I use the 'Set' statement to create a connection to the database
(Set MyConn = ........) should I do this once, say in
ThisDocument.InitializeDrawing() or should it be set in each sub / function?

So looking at my sub below and taking into account there will be many subs /
functions which will access the database what is the correct way to setup my
project so as variables are declared in the correct places, with the correct
scope?

Many Thanks,
M@

Public Sub NewCableNumber(intCableNumber, strCableDescription,
intCableLength, strCableTypeID)
On Error GoTo NewCableNumber_Error

' Sub to Add a New Cable to the database.

Dim MyConn As ADODB.Connection ' ADODB Connection Object
Dim dbFullName As String ' Full Database Path & Name
dbFullName = "H:\Visio\Cable Record - Development 3\Database\cable.mdb"

' Create the Connetion to the Database
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" _
& dbFullName & ";"

' Open the Database
MyConn.Open

' Execuite the SQL Statement Note the use of Variables in the SQL String
MyConn.Execute ("INSERT INTO tblCable(CableID, CableDescription,
CableLength, CableTypeID) VALUES('" & intCableNumber & "', '" &
strCableDescription & "', '" & intCableLength & "', '" & strCableTypeID &
"')")

' Close the Database
MyConn.Close

Exit Sub

NewCableNumber_Error:
MsgBox "Error is " & Err.Description & " in sub NewCableNumber.",
vbCritical, "System Error"

End Sub