Recordsets and Stored Procedures

Bob Bedell · December 30th, 2005, 10:21 PM

And if anyone using .mdb files and Jet 4.0 is wondering if you can do this kind of thing with an .mdb and Jet, you only need to change a single line of the code I posted:

Set g_cnn = CurrentProject.Connection

Just the connection string in basConnectionManagement.

Because Jet 4.0 supports SQL Server compatible syntax if you enable it at Tools -> Options -> Tables/Queries -> SQL Server compatible syntax (ANSI 92) in the Database Window, you can create the two Access "stored procedures" with the exact same stored procedure scripts I used for SQL Sever, with code like:

Public Sub PublishersStoredProcedures()

    Dim strSQL As String

' First Stored procedure
    strSQL = "CREATE PROCDURE 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);"

  ' First Stored procedure
  ' strSQL = "CREATE PROCEDURE procSelectPublishers AS SELECT * FROM Publishers;"

   Call CreateStoredProcedure(strSQL)

End Sub

Public Sub CreateStoredProcedure(strSQL As String)
    Dim cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command

    cmd.CommandText = strSQL
    cmd.CommandType = adCmdText

    Set cnn = GetNewConnection
    cmd.ActiveConnection = cnn

    cmd.Execute

    cnn.Close
    Set cnn = Nothing
    Set cmd = Nothing
    Exit Sub

ErrHandler:

    If cnn.State = adStateOpen Then
        cnn.Close
    End If

    Set cnn = Nothing
    Set cmd = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If

End Sub

Public Function GetNewConnection() As ADODB.Connection
   Dim Cnxn As ADODB.Connection
   Set Cnxn = CurrentProject.Connection

   If Cnxn.State = adStateOpen Then
      Set GetNewConnection = Cnxn
   End If

End Function

Jet 4.0 renders its "stored procedures" as Action Queries or Select Queries and replaces the SQL Server data types with Jet data types. All the other cde runs with 0 modification.

Just export the publishers table from SQL Server into Jet (your .mdb) using SQL Server's Data Transformation Services, and create the primary key field in the new Jet table.

Bob

Bob Bedell · December 30th, 2005, 10:38 PM

Its like welcome to the brave new world of a consistent, cross-platform application design and coding style. It works for Access (Jet), Access Data Projects, VB6, VB.Net, C#, ASP.Net, Web Services apps, anything that talks to a database! You'll never need to ask yourself "Where should I start this databse application?" again!

I get kind of excited about it...

Anyway, Happy New Year, Wrox forum folks. I'm going steelhead fishing on the Lake Erie tribs up in Steelhead Alley at 4:30 am. Most fun you can have with your cloths on. Data access classes are a distant second.

Bob Bedell · January 1st, 2006, 12:33 PM

Oh, and another way to cut down on the code redundancy would be to take the Data Access Application Block approach. Microfoft has already written these classes for SQL Server in .NET languages at :

http://msdn.microsoft.com/library/de.../html/daab.asp

but since its mostly ADO or ADO.NET code, you can see pretty clarly how the translation to VBA would be made.

Folks have developed some free DAAB code for Access at places like:

http://secretorange.co.uk/Content/Co...a.AccessHelper

DAAB classes contain methods written to recieve more parameters, such as a connection string, a command object, command text, an array of command parameters, etc. sent from the UI code or a buisness layer object. The functions are called with calles like (sorry 'bout the C#):

OleDbDataReader rdr = AccessHelper.ExecuteReader(Global.ConnectionString , CommandType.StoredProcedure, "procGetCatalogs");

The functionn header would look like:

public static OleDbDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)

An OleDbDataReader is the .NET equivalent of an ADO forward-only, read-only cursor. The DAAB function open a connection, executes the commnd, then closes the connection). This approach lets you use pre-fab function calls that return recordsets with different cursortypes, etc.

Just another approach worth beng aware of to a very flexible design pattern.

Bob

Bob Bedell · January 12th, 2006, 11:42 PM

Hi Roy,

Got your app running and I really like it. Quick question though. How are you handling the PK field? I can't tell if your using an identity column. Since you're not passing a PK value to mDepartmentProcs.InsertDepartment it would appear that you are, but then you do pass a PK value to mDepartmentProcs.EditDepartment. However, txtDepartmentID is never bound to the recordset. So when you pass PK:=Me.txtDepartmentID to mDepartmentProcs.EditDepartment, what value is being passed?

Thanks for the code,

Bob

Bob Bedell · January 13th, 2006, 12:19 AM

Could just pass nPK = mRst!DepartmentID as the PK parameter and not worry about displaying the PK field on the form, I guess. Is txtDepartmentID hidden, or getting its ControlSource set at design-time?

Bob

Roy0 · January 13th, 2006, 12:13 PM

Bob,
First of all, this project is still at the toy level. I'm using it to give myself a firm grounding in client server db techniques before porting over an existing Access app which uses a .mdb back end.

Quote:

quote: Is txtDepartmentID hidden, or getting its ControlSource set at design-time? ... txtDepartmentID is never bound to the recordset.

I made this text box as a development tool just so I can see what was going on. It will eventually go away. It gets its Control Source -- the identity column Primary Key for the Department table -- via the Access GUI at design time. So you are indeed right to point out that I'd be better off pass mRst!DepartmentID to the Edit procedure. (I would have eventually realized this when I got rid of the control.)

BTW, I picked up the Chipman and Baron book, Microsoft Access Developer's Guide to SQL Server, that you mentioned, and have been finding it very useful. Thanks for all your feedback.
Roy

lauriedthompson · March 13th, 2007, 09:19 AM

Hello Bob,

Just been reading your code and wonder if you could demonstrate splitting the data-tier into business logic and data access logic?

Kind regards

Laurie Thompson (mr.)

lauriedthompson · March 14th, 2007, 04:32 AM

Hello Bob,

Just been reading your code and wonder if you could demonstrate splitting the data-tier into business logic and data access logic?

Kind regards

Laurie Thompson (mr.)

Quote:

quote:Originally posted by Bob Bedell

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