Don't reinvent the wheel - writing reusable code in VB6

One common mistake among new programmers is that a major part of the learning process is doing. But this leads to one fundamental problem. They don't learn the flexibility of the language that makes it easy to write larger programs faster, with less errors, in less time.

In this example we will be creating a reuseable Module in VB6 *.bas file that you will be able to use in many database applications written in VB6. Our module will create and destroy a connection and recordset object to an Access database using ADO. (ActiveX Data Objects)

In this tutorial we WILL NOT be drawing controls on a form and setting properties. It will be done entirly at code level. So if you want to point click together a program, this isnt for you.

-----------------------------------
Pre Requisites
-----------------------------------
MS Access 97 or Later.
MS Visual Basic 6
Working knowlege of the VB IDE.


Step 1: Creating the sample database. (this will be breif as this is not an access tutorial)

create a new directory to hold project files (this is important if your following this tutorial)

c:\tutorial

Open MS access and create a new blank database. Name the database 'testDB'
Next create a table in the new blank database named 'info'.
Open the new table in design mode.

create column named "firstname"
create column named "lastname"

Save your new sample database to 'c:\tutorial'.


Step 2: Visual Basic

Open VB and select new Standard EXE.
Go to the toolbar and select 'Project > add Module'
Name your form 'frmMain"
Name your Module 'modPubs'
Go to 'Project > References - then check the "Microsoft ActiveX Data Objects 2.0 Library"'
Save your VB project to the tutorials directory: c:\tutorials


******************************SIDE NOTE***************************************************************************
It is good programming practice and essential to debugging to give each control a unique name. Most VB programmers will use a standard naming convention for different controls.

Form Control: frmMain
^--------------Convention 'frm" indicating it's a form.
^----------Name of control 'Main'

TextBox Control: txtName
^-----------Convention 'txt' Indicating a textbox.
^-------Name of Control 'Name'
***********************************************************************************************************************


Step 3: On to writing some code! Woohoo!

Select your module from the project explorer window.

From Beyond this line all text and code will be in VB compliant format.
Meaning that my comments and text will be commented using the preceding '
to denote non-code text for all you lazy people who want to cut-n-paste.


''''''''''''''''''''''''''''''''''''''''''''''''''CUT HERE'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'On the first line type:

Option Explicit

'This keyword tells the Visual Basic compiler that all variables used MUST be declaired.
'This is also good 'programming practice because if you misspell a variable it will be 100% easier to find 'the problem.

'Next lest define a couple variables that we will be using.

'Define a variable with public scope to hold the ADO connection object reference.
Public dbObj As New ADODB.Connection

'Define a variable with public scope to hold the ADO recordset object reference.
Global rsObj As New ADODB.Recordset

'Define a variable of public scope to hold our DSNless connection string. I won't go into the details of
'using a DSNless connection, but trust me, its better because of it's portability.

'Define a constant of public scop to hold the connection string that will be used to open the database
'connection, here we also set the value of 'dbq' to the path where we saved out database.

Public Const DBSTR = "Driver={Microsoft Access Driver (*.mdb)};" & _
"dbq=c:\tutorial\testDB.mdb;" & _
"Uid=;" & "Pwd="


'Lets write the first reusable function in our Module.

'This function opens a connection to the database by calling the connection object we created earlier.
'We will use this function later on in the tutorial.

public Function dbConOpen()

'open database connection
dbObj.Open DBSTR

'set some initial properties. These are a generic almost default setting but,
'work fine for many database 'uses.

rsObj.ActiveConnection = dbObj
rsObj.CursorLocation = adUseClient
rsObj.CursorType = adOpenDynamic
rsObj.LockType = adLockOptimistic

End Function

'This function closes the recordset and database objects.
'We will use this later on in the tutoiral.

Public Function dbConClose()
'close recordset object.
rsObj.Close
'close connection object.
dbObj.Close
End Function


'Next Hit the save button to save the work you've done already.

'Now using the project explorer open your form 'frmMain' and go to the code
'window. You should have a blank code window for your form.

'cun-n-paste the following code (or type it if your some kind of weirdo)


'set that debug flag.
option explicit

Private Sub form_Load()

'Declair those variables

dim fName as string
dim lName as string
dim strSQL as string

fname=inputbox("Enter Your First Name")
if(fname="")then fname=inputbox("Enter Your First Name")
lname=inputbox("Enter Your Last Name")
if(lname="")then lname=inputbox("Enter your Last Name")

strSQL="SELECT * FROM info" '<---Assign variable a string value.


'Here is where the wonderful reuseability comes in. =)
'Lets call the functions we created in our module and pass it some parameters.


call dbConOpen '<----Call function to open database connection.

rsobj.open strSql '<----Open Recordset.

rsobj.addnew '<----Tell the database you want to add a record.
rsobj.fields("firstname")=fName '<--add a new record to the firstname column.
rsobj.fields("lastname")=lName '<--add a new record to the lastname column.
rsobj.update '<----Update the table.

call dbConClose '<----Close the database and recordset.



'Give some feedback to the user.

msgbox "The following record was added to the database: " & vbCrlf & _
"FirstName - " & fName & vbCrlf & "LastName - " & lName

'exit app.
unload me

end sub



'----------------------------END CUT---------------------------------------------


Conclusion: What the heck do I do with this mess?

Well not to much, you can keep the module, and scrap the rest. Any time you need
to access a MS Access database you can add this module to your project and and
change your path to the database i.e. DBSTR.

Or you can hold onto it all and I will write another tutorial on accessing the data
that we just wrote to the database. We can call it part 2. haha.

Welp, I'm tired of typing so c-ya.