Unbound forms controlled by a recordset class

Posted on 2013-11-11
Medium Priority
Last Modified: 2013-11-17
Ok MS Access guys I'm looking to develop unbound forms that make use of a custom built class to handle all the CRUD actions. I'm sure something like this already exists and if so I'd like to be pointed to such an example, otherwise I'd like to collaborate with someone else who might be interested in putting something like this together.

I've developed some basic vba code that loops through unbound controls and depending on what action is required, will (C)reate, (R)ead, (U)pdate or (D)elete. If anyone is interested I can upload an example of what I've done so far. If there is nothing else out there we could use this as a basis to develop it further. The reason I want to use CRUD is to minimise bandwidth requirement when working with attached/remote SQL Server tables. Also I want to make editing a more controlled operation where the user has to follow set steps when interacting with a record. The reason for developing a class to perform the CRUD operations is that this can then be implemented for any form and related recordsource and be ported to other Access applictations.

Does anyone know of some CRUD examples?


Question by:dataflowjoe
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
LVL 58
ID: 39638655

 I think you'll be hard pressed to find anything like that for two reasons:

1. The whole point of using Access is what it offers, including bound forms.   Very few Access apps are writen with totally un-bound forms.

2. Not many use classes with VBA in the typical sense because VBA is object based, not object orientated.   There's a big difference.

With that said, if your taking the time to go fully un-bound, I would not use Access.   VB.Net would be a better choice for development.

<<The reason I want to use CRUD is to minimise bandwidth requirement when working with attached/remote SQL Server tables.>>

  You don't need to go unbound to manage that.  Simply setting WHERE criteria by default or opening forms with the Dataentry property set to True can handle that.  minimizing bandwidth is more of a development / design choice.

There's also some excellent tips here:


Download "The best of both worlds..."

LVL 85
ID: 39638680
VB.Net would be a better choice for development.
Agree with that 100%. I have developed unbound Access apps on several occasions, and it always involves quite a few workarounds to get past the builtin Access behavior.

Also agree that you'll be hard pressed to get much in the way of complete examples of unbound Access apps. By the time most Access developers have need of working unbound, they've moved along to other languages - in recent years, that would be one of the .NET languages (or PHP, if the web world), and before that it was pretty must VB or ASP.

Also, it's difficult (if not impossible) to develop a single class that can be used for all unbound forms. The more common method is to use a class-based approach for each "entity", and include the code to handle CRUD operations in those classes. Yes, there is some duplication of code, but for the most part INSERT and UPDATE statements are sufficiently different to warrant this.

For example, in an Invoicing operation I'd have a class for Customer, one for Invoice (which would be a collection class, with each element of that class consisting of an InvoiceHeader object and multiple InvoiceLineItem objects). The Customer class would consist of the main Customer data, as well as a collection of Address and Contact objects (i.e. classes) ... and so on.

Author Comment

ID: 39638699

Thank you for your reponse, I do understand what you are saying. The thing is I also use a number of activex com controls in the applications I develop, supplied by http://www.dbi-tech.com/ (the functionality they offer is just brilliant). Also I find deploying Access (with sagekey) is simple and the applications are fairly easy to maintain and evolve.  Learning VB.net would be a whole new learning curve for me, but I am open to that if neccessary.

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

LVL 58
ID: 39638706
One other reason that I should have added, which is what LSM is saying is that Access can't do 3-tier designs on it's own, which is where you typically work with data provider classes.

 Access doesn't allow for separating the presentation and business layers to any great degree, and not even the data layer very well.

 You can do what you want, but it's a lot of effort for something that's already built into the product and is not required for your real goal, which is to minimize bandwidth.

LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 total points
ID: 39638754
dbi-tech controls are very cool, but they come in .NET flavors too :)

If you're intent on this, then the concepts are fairly straight-forward. As you mention, you create classes to handle the CRUD operations, and you'd do so for each "entity".

So I'd have a Customer class something like:

Event Loaded()
Event Dirty()
Event IsNew()
Event CustomerNotFound()
Event EditCancel()

Private Type udCustomer
	CustomerName As String
	CustomerID as Integer
	CreditLimit As Double
	OverLimit as Boolean
	CurrentBalance As Double
	IsActive As Boolean
	'etc etc
End Type

Private mCust As udCustomer
Private mCust_Save as udCustomer

Private fDirty As Boolean
Private fNew As Boolean

Property Let Customer(DataIn As STring)
  mCust.CustomerName = DataIn
End Property
Property Get Customer As String
  Customer = mCust.CustomerName
End Property
Property Let ID(DataIn As Integer)
  mCust.CustomerID = DataIn
End Property
Property Get ID As Integer
  ID = mCust.CustomerID
End Property
<and so on>

Private Sub BeginEdit()
  If Not fDirty Then
    mCust_Save = New udCustomer
	mCust_Save = mCust
    fDirty = True
	RaiseEvent Dirty
  End If
End Sub

Function CancelEdit() As Boolean
  If fDirty Then
    mCust = New udCustomer
	mCust = mCust_Save
	fDirty = False
	RaiseEvent EditCancel
  End If
End Function
Function Load(CustomerID As Integer) As Boolean
  Dim rst As ADODB.Recordset
  Dim con As ADODB.Connection

  con = GetConnection 'a method to return the app's connection
  rst = New ADODB.Recordst
  rst.Open "SELECT * FROM Customer WHERE ID=" & CustomerID, con

  If Not (rst.EOF and rst.BOF) Then
    CustomerName = rst("Name")
    CustomerID = rst("ID")
    IsActive = rst("Active")
    '/ GetCustomerBalance is a method to calc the open balance of a Customer
    CurrentBalance = GetCustomerBalance(CustomerID) 
    OverLimit = CurrentBalance > rst("CreditLimit")
    <and so on>
    RaiseEvent Loaded
    Load = True
    RaiseEvent CustomerNotFound 
    Load = False    
  End If
End Function

Function Save As Boolean
	If Not fDirty Then
		Save = True
		Exit Function
	End If
	Dim s As String
	If fNew Then
		s = "INSERT INTO Customer(Name, Active, etc) VALUES('" & Replace(mCust.CustomerName, "'", "''") "',True, etc)"
		s = "UPDATE Customer SET Name='" & Replace(mCust.Customer, "'", "''") & "', Active=" & mCust.Active & ",etc etc WHERE ID=" & Cust.ID
	End If

	Dim con As ADODB.Recordset
	con = GetConnection
	con.Execute s
End Function

Open in new window

And that's just the very, very bare basics. I'd have to flesh this out a LOT, and provide error handling, error checking, etc etc. I'd have to repeat this for every "entity" item I wanted to manage.

Some very basic entities could be managed through a common class. For example, you would need to use a method to fill your Combos and Lists (using the AddItem method of those controls). You could very likely create a common class to retrieve those items (like CustomerType, AddressType,e tc), and use that to loop through and fill the controls.

Finally (and FWIW) I do a lot of work in Access, but more and more new work is in the .NET arena. I've found .NET apps to be MUCH easier to deploy and maintain than Access, and I find myself immediately progressing to the .NET platform when a client mentions a new utility.

Author Comment

ID: 39639226
Hi LSM Consulting,

That's great it will give me the start I was looking for. Ideally I'd like to have a class that is a generic entity against which I can map specific data objects to, such as the table name, field names, datatypes etc.  If I'm having to create a class for each entity then this does seem to be a bit over the top, considering the built in functionality that Access provides. Nevertheless I will have a go at developing something with your example and see how it shapes up.

I've been working with Access for 15 years now and have found it to be a great tool but I'm not that happy with the way Access is going right now, it's being pushed into SQL_Azure, Office 365 and Sharepoint (all producst that have to be subscribed to). I've been looking at Alpha Anywhere which seems quite well suited to an Access Developer but .NET has a huge community and tons of resources so I guess that should be the natural choice for me.
Can you suggest a good route into .NET for someone coming from an MS Access background.  What books or online video tutorials can you suggest to me.

Thanks again for your class example! I won't close the question just yet.

LVL 85
ID: 39639700
Ideally I'd like to have a class that is a generic entity against which I can map specific data objects to, such as the table name, field names, datatypes etc.
I tried the same thing, way back when I started on the path of unbound usage. I actually created one where I would essentially pass in the Table name, and then pass in arrays of Fields and Values. All worked nicely, until I started running into exceptions, and I quickly found my nice, neat little class to have dozens upon dozens of stubs for those exceptions, and I couldn't keep them straight. I eventually found that a little more work on the frontend to create sustainable, object-specific classes paid off in HUGE dividends down the road. Moral of my story was fairly easy to figure - the all-in-one class sounds great, but it's a pipe dream if you have an application of any complexity
If I'm having to create a class for each entity then this does seem to be a bit over the top, considering the built in functionality that Access provides.
But if you move to unbound forms, you won't be able to take advantage of most of that built-in functionality, so I can't see that as a reason to avoid doing this. One of the greatest strengths of Access is it ability to easily and quickly get your application up and running. If you take away the builtin functionality by moving to unbound forms, you take away that strength.

And don't forget - if you're truly unbound, you can no longer have bound combos or listboxes, or subforms/reports. Those items require you to have a table/query, and you won't have that with unbound forms. Yes you can use local temporary tables, but then you're defeating the entire purpose of the unbound application.

I don't really have any tutorials on .NET programming. I was very comfortable working with unbound applications, so my move to .NET was a bit easier than most traditional Access developers. There are many, many tutorial sites out there, however, but if I had to pick I'd start with the Microsoft Learning stuff: http://www.microsoft.com/learning/en-us/default.aspx. And of course, EE has a lot of articles and such presented by the excellent .NET crowd.
LVL 38

Expert Comment

ID: 39642151
As has already been pointed out, your reason for switching to unbound forms is flawed.  With bound forms you have complete control over how many records you fetch from the server.  That's what we call criteria.  Old style Access apps were typically built with forms bound directly to tables and the app used filters to get to the record a user wanted.  Client/server style Access apps use forms bound to queries and those queries have criteria which limits the number of rows returned.  Keep in mind that Access makes every effort to "pass through" EVERY query, not just the ones specifically defined as pass through so unless you have done something to prevent Access from passing through your query, the server will always do the heavy lifting and return only the selected record.

If you need coding practice, making unbound forms will give it to you but for your stated reason, it is a waste of time and your client/employer's money.

Author Closing Comment

ID: 39652097
Thanks for this!
LVL 38

Expert Comment

ID: 39652232
I'm going to try one more time.  Just because you can do this doesn't mean that you should.  You would never create a web page that uses the techniques you use for bound forms (I'm not even sure you could) and it doesn't make sense to add some abstracted I/O layer onto Access.  Access in conjunction with the ODBC driver already does a pretty good job without any user code.  All you are doing is adding complexity for no gain.  Your stated purpose is to improve I/O but I pointed out earlier that the gains you might achieve will not be measurable.  There is some justification for using pass-through queries and sprocs for complex batch processes but forms typically work with only one or a few records at a time and so don't cause any problem as long as you understand how to use criteria and form level events.
LVL 85
ID: 39652592
So what's the reason for the B grade?
LVL 85
ID: 39652599
I don't necessarily agree that you shouldn't use unbound forms. There are legitimate reasons to do so (to learn how to work with data, for example), but I do agree that there is often very little reason to work unbound in Access.
LVL 38

Expert Comment

ID: 39654958
I don't think I said you shouldn't use unbound forms.  I said the OP's reasoning was flawed.  If you decide to use unbound forms, it should be for a valid reason and this isn't it.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question