Recordsets and Stored Procedures

Roy0 · December 28th, 2005, 02:25 PM

(I posted the following on the SQL forum, but am reposting it here more appropriately. rm)

I'm designing an Access Project with a Microsoft SQL backend, in which I give the users several thousand records for browsing. My problems began when I took this advice to heart: "It is better to use ADO Recordsets only for retrieving data, and to use SQL stored procedures via ADO Command objects to perform insert, update, and delete operations."

I have three stored procedures: spGet, spAdd(parms), spUpdate(parms) corresponding to which I've made the Command objects: comGet, comAdd, comUpdate. I'm able to retrieve records into a recordset and use that recordset as a source for my form. I'm also able to invoke each command and change the backend table.

My problem is how to get the Add/Update stored procedures to mesh with the recordset approach. (RecordSet.AddNew and .Update methods seem clearly to clash with the stored procedure approach.)

I've been able to Add and Update by programmatically unbinding the controls in the form, gathering new values, then calling spAdd or spUpdate, then reinvoking spGet to retrieve several thousand records. But surely there must be a more efficient way. Below is some shortened code from my Form (I show only Add, but Update works similarly).

' initialize command objects and recordset, retrieve initial records
Private Sub Form_Load()
   With comAdd ' module variable for command object
      .ActiveConnection = CurrentProject.Connection
      .CommandType = adCmdStoredProc
      .CommandText = "spAdd"
      .Parameters.Append spAdd.CreateParameter("@name", adVarChar, adParamInput, 50)
   End With
       '-- SIMILAR CODE GOES HERE FOR comGet and comUpdate
   Set mRst = New ADODB.Recordset ' module level recordset
   mRst.Open comGet, , adOpenStatic ' retrieve data
   Set Me.Recordset = mRst ' link form to recordset
   Me.txtName.ControlSource = "Name" ' bind control
End Sub

' add button - unbind and blank control so new value can be gathered
Private Sub cmdAdd_Click()
   With Me.txtName
      .SetFocus
      .ControlSource = "" ' unbind control so I can assign a value
      .Text = ""
   End With
End Sub

' save button - call stored procedure and retrieve records
Private Sub cmdSave_Click()
   With comAdd
         .Parameters("@name") = Me.txtName
         .Execute , , adExecuteNoRecords
      End With
   End If
   Set mRst = comGet.Execute ' reexecute stored proc to retrieve records - again!!
   Set Me.Recordset = mRst ' relink to form recordset
   Me.txtName.ControlSource = "Name" ' rebind control
   mRst.MoveLast
End Sub

As I say, this approach works, but I'm not happy having to retrieve all those records I already had everytime I make a single change to the data. Is there a better way to make stored procedures cooperate with recordsets?

Roy

Bob Bedell · December 28th, 2005, 10:46 PM

Quote:

quote:But surely there must be a more efficient way.

Actually, thatâs pretty much exactly how the pros do it. After an insert, you execute a second stored procedure that does a select on the table to retrieve the new value. Hereâs the âstate-of-the-artâ very general approach to the problem. Code follows. Youâre absolutely right. It takes a lot of code to do it right. Your (great) huge question needs a huge reply.

First, break all of your data access code out of your user interface. UI code, from the data access standpoint, should do nothing but call functions that execute stored procedures. And each function called has one purpose in life: to execute a single stored procedure.

The stored procedure executing functions are members of class objects. Each class object has two purposes in life: data validation, and executing stored procedures that execute only one of the CRUD functions: CREATE (Insert), READ (select), UPDATE, DELETE. One class, one type of sproc.

The UI code and data access classes communicate via global connection and recordset objects variables that live in a VBA standard module.

Iâve written out the bare essentials of this kind of 3-tier approach below. (Took about 2 hours to write 2 classes, a standard module, two stored procedures, and a form module. Its time consuming upfront, but saves you hours down the road as you add stored procedures and class methods. That, along with easier code maintenance and reuseability, along with easier distribution, is where the "efficieny" comes into play).

I didnât include any error handling which is essential to this kind of set up but is a topic in and of itself. Errors are handled a bit differently in classes.

----------------------------------------------------------------------
1. Create 2 stored procedures in the âpubsâ data base on SQL Server. Here are the scripts:
----------------------------------------------------------------------

"CREATE PROC procInsertPublisher(@PubID CHAR(4), " & _
                  "@PubName VARCHAR(40), " & _
                  "@City VARCHAR(20), " & _
                  "@State VARCHAR(2), " & _
                  "@Country VARCHAR(30)) " & _
               "AS " & _
               "INSERT INTO publishers (pub_id, pub_name, city, state, country) " & _
               "Values (@PubID, @PubName, @City, @State, @Country);"

"CREATE PROC procSelectPublishers AS SELECT * FROM Publishers;"

My pubs database has a column check constraint on the pub_id field, so I need to enter values in the format 99[0-9][0-9] like 9901. Canât remember if I put the constraint there myself or not.
-----------------------------------------------------
2. Create a form with the following controls:
-----------------------------------------------------

txtPubID
txtPubName
txtCity
txtState
txtCountry
cmdInsert
cboPublishers (RowSourceType set to Value list)

----------------------------------------------------------------
3. cmdInsert_Click event and sub LoadPublisher
----------------------------------------------------------------
(One handles the insert, the other the select)

(Notice no data access code in the UI. A recordset is instantiatied, but sent to a class for processing.)

Private m_objInsertData As clsInsertData
Private m_objSelectData As clsSelectData

Private Sub cmdInsert_Click()

   Dim lngReturnCode As Long

   'Opend global connection objectr
   Call EstablishConnection

   'Set a reference to the insert data class
   Set m_objInsertData = New clsInsertData

   'Insert the new publisher. Call InsertPublisher function
   'in the claInsertData object.
      lngReturnCode = m_objInsertData.InsertPublisher(g_cnn, _
                        txtPubID.Value, _
                        txtPubName.Value, _
                        txtCity.Value, _
                        txtState.Value, _
                        txtCountry.Value)

   'Ensure we were successful with a uer-defined error.
   'If lngReturnCode <> 0 Then
   ' Some error handling code.
   'End If

   'ReLoad Employees
   Call LoadPublishers

   'Remove reference to object
   Set m_objInsertData = Nothing

End Sub

Sub LoadPublishers()

   Dim lngReturnCode As Long

   'Set a reference to the global recordset object
   Set g_rst = New ADODB.Recordset

   'Set a reference to a clsSelectData object
   Set m_objSelectData = New clsSelectData

   'Open the recordset
   lngReturnCode = m_objSelectData.SelectPublishers(g_cnn, g_rst)

   'Was function call successful.
   'If lngReturnCode <> 0 Then
   ' Some error handling code.
   'End If

   'Load cboPublishers
   Do While Not g_rst.EOF
      Me.cboPublishers.AddItem g_rst!pub_id
      g_rst.MoveNext
   Loop

   'Close the recordset
   g_rst.Close

   'Remove objects references
   Set g_rst = Nothing
   Set m_objSelectData = Nothing

   Exit Sub

End Sub

-------------------------------------------------------------------------
4. Create the data-tier classes: clsInsertData and clsSelectData
-------------------------------------------------------------------------

(The class methods return a long (0 for success, 1 for failure. 1 would be returned by your error handler along with strMessage. Data validation happens in the classes as well).

-------------------------
Name: clsInsertData
-------------------------

Public Function InsertPublisher( _
   ByRef cnn As ADODB.Connection, _
   ByVal strPubID As String, _
   ByVal strPubName As String, _
   ByVal strCity As String, _
   ByVal strState As String, _
   ByRef strCountry As String) As Long

   Dim blnValidated As Boolean
   Dim cmd As ADODB.Command

   blnValidated = True

   'Validate data to insert. Validate every item to be inserted.
   'I just did the PubID field here.
   If RTrim(Len(strPubID)) = 0 Then
      strMessage = "Pub ID is zero length" & vbCrLf & vbCrLf
      blnValidated = False
   End If
   If RTrim(Len(strPubID)) > 4 Then
      strMessage = "Pub ID is greater than 4 characters" & vbCrLf & vbCrLf
      blnValidated = False
   End If

   'Check validation variable
   'If Not blnValidated Then
   ' Some error handling code.
   'End If

   'Set a reference to the command object
   Set cmd = New ADODB.Command

   'Set the command object properties
   Set cmd.ActiveConnection = cnn
   cmd.CommandText = "procInsertPublisher"
   cmd.CommandType = adCmdStoredProc

   'Create and append the parameters to the parameters collection
   cmd.Parameters.Append cmd.CreateParameter("@PubID", _
      adVarChar, adParamInput, 4, strPubID)
   cmd.Parameters.Append cmd.CreateParameter("@PubName", _
      adVarChar, adParamInput, 40, strPubName)
   cmd.Parameters.Append cmd.CreateParameter("@City", _
      adVarChar, adParamInput, 15, strCity)
   cmd.Parameters.Append cmd.CreateParameter("@State", _
      adVarChar, adParamInput, 20, strState)
      cmd.Parameters.Append cmd.CreateParameter("@Country", _
      adVarChar, adParamInput, 20, strCountry)

   'Execute the command object
   cmd.Execute

   'Remove references to objects
   Set cmd = Nothing

   'Set the return code, return 1 in an error handler.
   InsertPublisher = 0

   Exit Function

End Function

------------------------------------
Name: clsSelectData
------------------------------------

Public Function SelectPublishers( _
   ByRef cnn As ADODB.Connection, _
   ByRef rst As ADODB.Recordset) As Long

   rst.Open "procSelectPublishers", cnn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

   'Set the return code
   SelectPublishers = 0

   Exit Function

End Function

SEE!!! ALL THE DATA ACCESS CODE IS IN THE CLASSES!!! WOO HOO!!!

----------------------------------------------------------------------
5. Create a standard module to open a global connection
----------------------------------------------------------------------

Iâm opening the connection in the form, but typically youâd open it in a login dialog when the app starts.

--------------------------------------------
Name: basConnection Management
--------------------------------------------

Global g_cnn As ADODB.Connection
Global g_rst As ADODB.Recordset

Public Function EstablishConnection() As Boolean

   Dim strConnectString As String

   Set g_cnn = New ADODB.Connection

   strConnectString = "Provider='sqloledb';Data Source='ELIOT';" & _
      "Initial Catalog='Pubs';Integrated Security='SSPI';"
   g_cnn.Open strConnectString

   'Check connection state
   If g_cnn.State <> adStateOpen Then
      EstablishConnection = False
   Else
      EstablishConnection = True
   End If

   Exit Function

End Function

Public Sub TerminateConnection()
   On Error Resume Next
   g_cnn.Close
   g_rst.Close
   Set g_cnn = Nothing
   Set g_err = Nothing
   Set g_rst = Nothing
End Sub

THERE YOU GO!! BASIC N-TIER APP. (VERY BASIC ). Runs great for me. Hope it runs for you too!!

HTH,

Bob

Bob Bedell · December 28th, 2005, 10:58 PM

'ReLoad Employees
Call LoadPublishers

should read:

'ReLoad Publishers
Call LoadPublishers

Got my databases mixed up.

LoadPublishers does the SELECT after the INSERT.

Bob Bedell · December 28th, 2005, 11:02 PM

Yes, my SQL Server installation's name is ELIOT (after T.homas S.tearns). Be nice. ;)

Bob Bedell · December 28th, 2005, 11:39 PM

Quote:

quote:My problems began when I took this advice to heart: "It is better to use ADO Recordsets only for retrieving data, and to use SQL stored procedures via ADO Command objects to perform insert, update, and delete operations."

Quote:

Final thought. You'll see that me code does exactly that, and its the best way to go. The only place a recordset object actually gets opend is in clsSelectData::SelectPublishers. The insert is done using a command object, and deletes and updates would be too, in there respective classes.

Also note that the recordset is opened forward-only, readonly. You should find, using these techniques, that that's the only type of recordset you're ever likely to need from SQL Server which is super efficient.

If you ever need a record count on a forward-only, readonly recordset, you can get it with one if the following:

Code:

Dim rst As New ADODB.Recordset
Dim arr() As Variant
Dim lngRecordCount As Long

rst.Open "SELECT * FROM Publishers", "DSN=pubs", , , adCmdText
arr() = rs.GetRows()
lngRecordCount = UBound(arr, 2) + 1

or

Code:

Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset
Dim lngRecordCount As Long

cnn.Open "DSN=pubs"
rst.Open "SELECT COUNT(*) FROM publishers", cn, , , adCmdText
lngRecordCount = rs(0)
rs.Close
rst.Open "SELECT * FROM Publishers", cn, , , adCmdText

Bob

Roy0 · December 29th, 2005, 01:28 PM

Bob,
Thanks for the detailed response. I'll give your classy approach a try.

First a couple of questions on your code:
-- Since InsertPublisher and SelectPublishers return 0 or 1, why not make them return a Boolean value rather than Long? Alternatively, to make your code more self-documenting, you could add a method, Success() As Boolean, to your classes to return the status of the last action.

-- Why make the recordset a global rather than a module-level variable?

-- Do you also have to jump through the hoop of unbinding your controls to gather new or modified data, and then rebinding them after getting the new recordset so as to allow navigation?

-- I guess I'm a little unclear on the RecordSet forward-only value for the CursorType property. I thought this would prevent me from using Access's navigation buttons to browse backwards, but I see now I can.

Why I'm still unhappy

Quote:

quote:After an insert, you execute a second stored procedure that does a select on the table to retrieve the new value.

If all I had to do was retrieve the new value, I'd be happy. The reason I'm troubled is that I appear to need to re-fetch [u]all</u> the recordset rows after [u]every</u> insert. For instance, what if your procSelectPublishers returned a million records? Not good.

Consider how Recordset.Addnew and RecordSet.Update presumably work. AddNew creates a new row in the 'local' recordset. Update writes the changes to the underlying data source â presumably without having to re-fetch the records it already has. One way to emulate this functionality would be to select just the newly inserted row and then 'merge' it with the current recordset. But I don't see that the ADO.RecordSet class allows for this.
Roy

Bob Bedell · December 29th, 2005, 07:27 PM

Hi Roy,

1. About the return value: Can be done in a variety of ways. You could even include a parameter in a command objectâs parameterâs collection with a parameter direction of adParamReturnValue, and have the stored procedure return the return value using RETURN. You could even do error checking in the stored procedure itself with @@Error.

2. Global recordset object: In the code I posted a module level recordset object would work fine, but as your processing gets more complicated, a global recordset object becomes a handy way of making a recordset object available to multiple classes without having to declare one in every class. For example, say you are inserting a new publisher record, and you need a value for a field in the publisherâs table that is a foreign key field. Your procedure may need to open a quick recordset to look in the primary table to see if the value you need already exists, or if you need to insert it into the primary table, say a look up table. The class can just make quick use of the global recordset variable and close it when it is done with it. Its basically a design decision. Nothing is carved in stone.

3. I always use unbound controls and donât use conventional navigation buttons of the First, Last, Previous, Next variety. I use list controls of one sort or another to display multiple records (filtered at times by option buttons, combo boxes, etc.) Double clicking the list displays the relevant record in a separate form for editing. But then I donât use Access for front end development much either, though I do like to push its âunboundâ capabilities from time to time. Primarily interested in Jet as a backend for small apps. Again, its just a design choice. Feel like unbound controls give me more control, and the approach translates well to VB6, VB.NET, C# apps.

4. Read-Only, Forward-Only: The old âfire-hoseâ. Fastest cursor there is. Perfect for populating lists. Problematic in bound apps using standard Access navigation features.

5. Retrieving the new value/selecting large recordsets: Add another SELECT function to your class, only include an argument that receives a parameter. Write a stored procedure that receives that parameter(s) and only returns the records you want. Or use @@Identity in a sproc to retrieve the newly inserted identity value, then use the identity value in a parameterized sproc to retrieve the new record. You can always retrieve as little or as much as you need. The basic concept still holds: one method, one sproc, though it may be a parameterized select sproc.

Wish I had more time to post some more code but Iâm crazy busy right now. I really love the techniques I was describing in my last post and use them all the time. One has to settle for some sort of design process, both for easy code reuseability and in order to preserve oneâs sanity.

I got turned on to the approach I described by Theron Willisâs book âBeginning SQL Server 2000 for Visual Basic Developersâ (a Wrox title) and have been using it, in a variety of incarnations, ever since.

HTH,

Bob

Bob Bedell · December 29th, 2005, 07:42 PM

Foolow up on the navigation buttons:

First, Last, Previous, Next buttons have alsways seemed like a fairly clumsy way of getting to the data you are really interested in. Why do all that scrolling? I concentrate a lot on good, multi-parameter, search interfaces and functionality, and as I mentioned, list controls, or textboxes populated by combo box selections, or TreeViews (love TreeView controls!), anything to let you get to the data you want by selecting it in some way.

Roy0 · December 30th, 2005, 01:13 PM

Following is the code from a Form (frmDepartment) and a from a Class Module (DepartmentProcs) that represents my first attempt at getting an Access Project front end to work with a SQL Server backend. Just a single table for now. The Form uses Access navigation controls.

Thanks to Bob Belkin for suggesting this approach. (My scheme differs from his in that I wrap all the SQL interface code into one class rather than two.)

The code below is abridged in that error trapping is removed.
Roy

Code:

'====================================================
' MODULE:      frmDepartment
' DESCRIPTION: Code for Department Form
'              User interface to view and modify Department table
' NOTES:       All the calls to stored procedures are accomplished by
'               the DepartmentProcs object
' AUTHOR:      Roy Mash
' CREATED:     12/29/05
' REVISED:     12/30/05
' TODO:        validation
'====================================================

Option Compare Database
Option Explicit

   'Form modes
Private Const VIEW = 0
Private Const EDIT = 1
Private Const ADD = 2
   ' Module level variables
Private mRst As ADODB.Recordset
Private mCurrentMode As Integer
Private mDepartmentProcs As New DepartmentProcs

'================ On first loading the Form  ===================
Private Sub Form_Load()
   Set mRst = New ADODB.Recordset
   mRst.CursorLocation = adUseClient
   mRst.CursorType = adOpenForwardOnly
   mRst.LockType = adLockReadOnly

   FetchRecords   ' gets recordset
   ChangeModeTo (VIEW)
End Sub

'=================== Button Click Procedures ==========================

Private Sub btnAdd_Click()
   ChangeModeTo (ADD)   ' changes control enables
   Unbind_controls      ' unbinds data gathering controls and blanks them
End Sub

Private Sub btnEdit_Click()
   ChangeModeTo (EDIT)  ' changes control enables
   Unbind_controls      ' unbinds data gathering controls and fills w/previous vals
End Sub

' Calls DepartmentProcs methods to executes stored procedures corresponding
'   to the action required
Private Sub btnSave_Click()
   Dim nPK As Integer     'Primary Key
   nPK = mRst!DepartmentID

   ' depending on button pushed, call the appropriate DepartmentProcs method
   ' and pass the gathered values as parameters
   Select Case mCurrentMode
      Case ADD
         mDepartmentProcs.InsertDepartment name:=Me.txtName, _
            phone:=Me.txtPhone, fax:=Me.txtFax
         MsgBox (IIf(mDepartmentProcs.Success, "record added", "record not added"))
         FetchRecords
         mRst.MoveLast                           ' assumes record added at end
      Case EDIT
         mDepartmentProcs.EditDepartment _
            PK:=Me.txtDepartmentID, name:=Me.txtName, _
            phone:=Me.txtPhone, fax:=Me.txtFax
         MsgBox (IIf(mDepartmentProcs.Success, "record updated", "record not updated"))
         FetchRecords
         mRst.Find ("DepartmentID = " & nPK)
   End Select 
   ChangeModeTo (VIEW)
End Sub

' Cancel works by simply repopulating the controls with data from the current recordset
Private Sub btnCancel_Click()
On Error GoTo Err_btnCancel_Click
   Bind_controls
   ChangeModeTo (VIEW)
End Sub

'Exit the form
Private Sub btnClose_Click()
    DoCmd.Close
End Sub

' Confirm delete first 
Private Sub btnDelete_Click()
   Dim result As VbMsgBoxResult
   result = MsgBox("Are you sure you want to delete this record?", _
            vbYesNo, "Delete Clearance")
   If result = vbYes Then
      mDepartmentProcs.DeleteDepartment PK:=mRst!DepartmentID
      If mDepartmentProcs.Success Then
         MsgBox "record deleted"
         FetchRecords
         mRst.MoveLast
      Else
         MsgBox "record not deleted"
      End If
   End If
End Sub

'=================== End Button Click Procedures ==========================

' Mode mostly changes the enable status of various buttons
' Must change focus so as to disable controls
Function ChangeModeTo(mode As Integer)
   Select Case mode
      Case VIEW
         mCurrentMode = VIEW
         Me.txtName.SetFocus
         Me.btnAdd.Enabled = True
         Me.btnEdit.Enabled = True
         Me.btnSave.Enabled = False
         Me.btnCancel.Enabled = False
         Me.btnClose.Enabled = True
      Case EDIT
         mCurrentMode = EDIT
         Me.txtName.SetFocus
         Me.btnAdd.Enabled = False
         Me.btnEdit.Enabled = False
         Me.btnSave.Enabled = True
         Me.btnCancel.Enabled = True
         Me.btnClose.Enabled = False
      Case ADD
         mCurrentMode = ADD
         Me.txtName.SetFocus
         Me.btnAdd.Enabled = False
         Me.btnEdit.Enabled = False
         Me.btnSave.Enabled = True
         Me.btnCancel.Enabled = True
         Me.btnClose.Enabled = False
   End Select
End Function


'=================== Other Procedures ==========================
'Associate the controls with fields from the underlying recordset
Private Sub Bind_controls()
   Me.txtName.ControlSource = "DepartmentName"
   Me.txtPhone.ControlSource = "Phone"
   Me.txtFax.ControlSource = "Fax"
End Sub

' Disassociate the controls from the recordset and either blank out the
' controls (for Adding) or seed them with the previous values (for Editing)
Private Sub Unbind_controls()
   With Me.txtPhone
      .SetFocus
      .ControlSource = ""
      .Text = IIf(mCurrentMode = EDIT, mRst!phone, "")
   End With
   With Me.txtFax
      .SetFocus
      .ControlSource = ""
      .Text = IIf(mCurrentMode = EDIT, mRst!fax, "")
   End With
   With Me.txtName
      .SetFocus
      .ControlSource = ""
      .Text = IIf(mCurrentMode = EDIT, mRst!DepartmentName, "")
   End With
End Sub

' calls stored procedure (via DepartmentProcs object) to get all records
Private Sub FetchRecords()
   If mRst.State = adStateOpen Then mRst.Close   ' close recordset
   Call mDepartmentProcs.FetchDepartment(mRst)   ' refetch
   Set Me.Recordset = mRst                       ' relink to  Form recordset
   Bind_controls
End Sub

'========== End frmDepartment   =====================

'====================================================
' MODULE:      DepartmentProcs
' DESCRIPTION: Class to insert, modify, delete, and fetch records
' NOTES:       This class allows the user interface to be isolated
'                 from the calls to stored procedures related to the
'                 Department table at the backend SQL database
'               Thanks to Bob Belkin for suggesting this approach
' AUTHOR:      Roy Mash
' CREATED:     12/29/05
' REVISED:     12/30/05  added Delete method
' TODO:        Validation
'====================================================

Option Compare Database
Option Explicit

' One Command object per stored procedure
Private mcmdAdd As New ADODB.Command
Private mcmdUpdate As New ADODB.Command
Private mcmdGetAll As New ADODB.Command
Private mcmdDelete As New ADODB.Command
Private mSuccess As Boolean

' Returns status of the last access to stored procedures
Public Property Get Success() As Boolean
   Success = mSuccess
End Property

' Define unchanging properties of the Command objects
Private Sub Class_Initialize()
   With mcmdGetAll
      .ActiveConnection = CurrentProject.Connection
      .CommandType = adCmdStoredProc
      .CommandText = "pDepartment_GetAll"
   End With

   With mcmdAdd
      .ActiveConnection = CurrentProject.Connection
      .CommandType = adCmdStoredProc
      .CommandText = "pDepartment_AddNew"
      .Parameters.Append mcmdAdd.CreateParameter("@name", adVarChar, adParamInput, 50)
      .Parameters.Append mcmdAdd.CreateParameter("@phone", adVarChar, adParamInput, 14)
      .Parameters.Append mcmdAdd.CreateParameter("@fax", adVarChar, adParamInput, 14)
   End With

   With mcmdUpdate
      .ActiveConnection = CurrentProject.Connection
      .CommandType = adCmdStoredProc
      .CommandText = "pDepartment_Update"
      .Parameters.Append mcmdUpdate.CreateParameter("@PK", adInteger, adParamInput)
      .Parameters.Append mcmdUpdate.CreateParameter("@Departmentname", adVarChar, adParamInput, 50)
      .Parameters.Append mcmdUpdate.CreateParameter("@phone", adVarChar, adParamInput, 14)
      .Parameters.Append mcmdUpdate.CreateParameter("@fax", adVarChar, adParamInput, 14)
   End With

   With mcmdDelete
      .ActiveConnection = CurrentProject.Connection
      .CommandType = adCmdStoredProc
      .CommandText = "pDepartment_Delete"
      .Parameters.Append mcmdUpdate.CreateParameter("@PK", adInteger, adParamInput)
   End With

   mSuccess = True
End Sub

'========= One method per stored procedure ===============

' Get all records for department
' Argument holds resulting recordset
' Returns number of records found
Public Function FetchDepartment(ByRef rst As ADODB.Recordset) As Long
      ' pre-validation will go here
   ' Call stored procedure
   rst.Open mcmdGetAll, , adOpenForwardOnly, adLockReadOnly
   FetchDepartment = rst.RecordCount   ' return number of records
   mSuccess = IIf(rst.RecordCount = 0, False, True)
End Function

' Update the values of one particular record
Public Sub EditDepartment(ByVal PK As String, ByVal name As String, _
   ByVal phone As String, ByVal fax As String)

      ' pre-validation will go here
   ' Call stored procedure
   With mcmdUpdate
      .Parameters("@PK") = PK
      .Parameters("@Departmentname") = name
      .Parameters("@phone") = phone
      .Parameters("@fax") = fax
      .Execute , , adExecuteNoRecords
   End With
   mSuccess = True
End Sub

' Add one record
Public Sub InsertDepartment(ByVal name As String, ByVal phone As String, _
   ByVal fax As String)

      ' pre-validation will go here
   ' Call stored procedure
   With mcmdAdd
       .Parameters("@name") = name
       .Parameters("@phone") = phone
       .Parameters("@fax") = fax
       .Execute , , adExecuteNoRecords
   End With
   mSuccess = True
End Sub

' Delete one record
Public Sub DeleteDepartment(ByVal PK As String)
   ' Caller's responsibility to ask for confimation
   With mcmdDelete
      .Parameters("@PK") = PK
      .Execute , , adExecuteNoRecords
   End With
   mSuccess = True
End Sub
'========== End DepartmentProcs   =====================

Bob Bedell · December 30th, 2005, 09:08 PM

Hi Roy,

Cool code. Look forward to giving it a test drive when I get a chance.

Also worth mentioning is that this data class appraoch is so common among the Visual Studio crowd, and the code is so repetitive, that there are a bunch of free-ware, template-based code generation tools out there that will generate your data classes and stored procedure scripts pretty much for you. Your approach of basing classes on tables (instead of functionality) is a common one, and some of these code generation tools will allow you connect to your db, enter a table name, then generate the SELECT, INSERT, UPDATE, DELETE methods in a table based-class, along with the stored procedure scripts they execute.

CodeSmith is one such tool I've used in the past. Cool thing about it is there is a CodeSmith community where folks can contribute user-developed templates. CodeSmith used to be free, but I just checked out their sight, and they appear to have started charging a bit for it.

They are at: http://community.codesmithtools.com/

If you start using this approach at all regularly, knowing whats available in terms of code-generation apps, templates, generating your own templates, etc. can save you alot of coding.

Bob