Declararations --> Please set me straight :P

[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 'should' 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

 

[Al Edlund]

I use something like this because I've discovered that you often need a
little more information if something fails to execute.
al

' this subroutine creates a database record using a passed string
' GUID (usually from the pagObj_ShapeAdded event defined in ThisDocument)

Public Sub subCreateDbRecord(strDbTable As String, strIndex As String,
strGUID As String)

Dim intResult As Integer
Dim str_db_filename As String
Dim db As Database
Dim visDocument As Visio.Document
Dim visPage As Visio.Page
Dim SaveErr As Long
Dim errDB As ADODB.Error


Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim strProvider As String
Dim strSource As String
Dim strConn As String
Dim strSelect As String
strProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
strSource = "Data Source="
Dim strProviderED As String ' the end delimter
strProviderED = ";"

On Error GoTo CreateRecord_Err

' first we have to find out which database is associated with this
document
' it is stored in a custom property on the sheet named "project
definition"
Set visDocument = Visio.ActiveDocument
Set visPage = visDocument.Pages.item("Project Definition")
str_db_filename = visDocument.Path &
visPage.PageSheet.Cells("prop.database_file.value").ResultStr("")

' now create the connection string and open the database
strConn = strProvider & strSource & str_db_filename & strProviderED
cnn.Open strConn

' all shape properties will be saved in a table base on group
strSelect = "SELECT * FROM " & strDbTable
rst.Open strSelect, cnn, adOpenKeyset, adLockOptimistic

rst.AddNew
' when we create the record we only put the pointer into it
' we will update all of the fields in another routine
rst.Fields(strIndex) = strGUID

rst.Update

rst.Close
cnn.Close
DoEvents

CreateRecord_Exit:

Exit Sub

CreateRecord_Err:
SaveErr = Err.Number

If SaveErr > 0 Then
Debug.Print "Err in subCreateDbRecord is " & Err & " " & Err.Description
Resume Next
End If

For Each errDB In cnn.Errors
Debug.Print "DB Create"
Debug.Print "DB Description: " & errDB.Description
Debug.Print "DB Number: " & errDB.Number & " (" & _
Hex$(errDB.Number) & ")"
Debug.Print "JetErr: " & errDB.SQLState
Next

End Sub

 

[Matt B]

Al,

The error handling routine is a bit of a step up from my initial code
The extra information generated as a result of your example is helping my
coding to no end.
Following your example, I am changing my code to get the database path from
a custom property in my first page.
Why I did not think of this im not sure.. But thats the beauty of a
community / forum.

Thanks for your Post...!!