Building Multiuser Apps with Access

[winshent]

I've always setup Front Ends and Back Ends so that a copy of the Front End is distributed to each Desktop.

I've now joined a new company (been here 6 months now) and the way things are done here is that one copy of the FE sits on a share and users open it from that location. This file contains no local tables and all data is connected to either Access or Oracle databases using ADO. The FE has built been utilizing User Defined Types to read all data in to memory.

Is this a perfectly acceptable way of doing things?

 

[spikepl]

No it isn't. But it works just fine, until the day it stops doing so.

I have met companies like that - the reasoning is quite often that updating distributed frontends is too complex. Which it is unless you have a tool to do so like eg. Bob Larson's updater http://www.btabdevelopment.com/ts/freetools

 

[vbaInet]

The FE has built been utilizing User Defined Types to read all data in to memory.

Is this a perfectly acceptable way of doing things?

Are the UDTs a collection of ADO/DAO recordsets? If they are, how are they handling change in data?

 

[winshent]

The UDTs are loaded in to arrays not collections. They essentially read all of the data from the database and load in to memory when the app is opened, then displayed to the forms etc..

When a change is made on a form, another 'snapshot' of the data is loaded into a new UDT and both initial UDT and snapshot UDT are compared to see if other users have changed the data before committing the changes to the database.

Make sense?

 

[vbaInet]

What I meant by a "collection of ADO/DAO recordsets" is something like this:

Type InMemRecordset
    Customers_tbl As Recordset
    Products_tbl As Recordset
    .
    .
    . etc ...
End Type

... but obviously this is not the case.

But I understand what they're doing. For each record in a recordset they read it off and create a record using a UDT and load each record into an array of the same type. Correct?

 

[winshent]

But I understand what they're doing. For each record in a recordset they read it off and create a record using a UDT and load each record into an array of the same type. Correct?

Yep... that is pretty much what most of the databases here are doing.

 

[vbaInet]

Sounds a bit ridiculous. Does it not explain in the technical documentation (i.e. if there's any) why they've implemented it that way?

A couple of downsides I see with this approach are:
1. The initial opening of the db will take a while
2. Record locks are non-existent because everything is done in memory
3. If User A deletes the record User B is currently viewing, User B will not see or know of this until a commit is applied. At which point the reconciliation process becomes absolutely crucial.
4. It's doing double work to read/write. I.e. looping through a recordset, looping through each field in the record, saving it to a UDT, then saving the UDT to an array of UDTs.

Comments:
If they were going to do this at least they would have used GetRows to save the entire recordset into an array, then save the field names in a dictionary and use this as a reference point to resolve field names.

It just seems that they've completed turned Access on its head and moved it from a relational database to a flat file (that of a Spreadsheet). This is the sort of thing one would do with a spreadsheet not a database.

By the way winshent, how is record searching implemented? Are they doing it on the array?

 

[winshent]

Sounds a bit ridiculous. Does it not explain in the technical documentation (i.e. if there's any) why they've implemented it that way?

Thats a negative. No documentation. Built like this for performance reasons I'm told.

A couple of downsides I see with this approach are:
1. The initial opening of the db will take a while
2. Record locks are non-existent because everything is done in memory
3. If User A deletes the record User B is currently viewing, User B will not see or know of this until a commit is applied. At which point the reconciliation process becomes absolutely crucial.
4. It's doing double work to read/write. I.e. looping through a recordset, looping through each field in the record, saving it to a UDT, then saving the UDT to an array of UDTs.

Some good points there.

Regarding deletes, I never build DBs with the ability to delete records. I prefer to use an archive method if needed. There are no deletes on the DBs here as far as I am aware.

Comments:
If they were going to do this at least they would have used GetRows to save the entire recordset into an array, then save the field names in a dictionary and use this as a reference point to resolve field names.

It just seems that they've completed turned Access on its head and moved it from a relational database to a flat file (that of a Spreadsheet). This is the sort of thing one would do with a spreadsheet not a database.

The relational model is reflected in the UDTs.. for example:

Public Type TMailCheck
    CheckID As Long
    CheckOrder As Integer
    ApprovalRole As Long
    AllowEdit As Boolean
    Description As String
    Confirm As String
    IsComplete As Boolean
    IsError As Boolean
End Type

Public Type TMailbox
    MailboxID As Long
    MailName As String
    Mailbox As String
    AllowEdit As Boolean
    CheckCount As Integer
    MailCheck() As TMailCheck
End Type

Public Type TMailboxes
    ItemCount As Integer
    Mailbox() As TMailbox
    LoadDate As Date
End Type

Public gudtMailboxes As TMailboxes

By the way winshent, how is record searching implemented? Are they doing it on the array?

Yes, record searching is done on the array.

I have never seen UDT's until I joined here. Are they commonly used? I rarely see them mentioned on here. I have used classes for some projects I have built but in general I have not needed to.

There are a couple of applications here that read text files which come in various formats, so I can see the benefit of loading configurations in to UDT's as the source data is fixed. This is drifting off topic slightly but am still very interested in your views on how everything is built here.

 

[vbaInet]

Thats a negative. No documentation. Built like this for performance reasons I'm told.

That doesn't surprise me. You've definitely got your work cut out.
Yes it's great to do everything in memory and this is what I do in Excel but working this way in what is supposed to be a relational db is a pitta.

The relational model is reflected in the UDTs.. for example:

A UDT is not really a relational model, it's not even a model. What you see is a record (TMailCheck) of a record(TMailbox) of a record(TMailboxes). UDTs can be used as a data structure for holding a single record or it can be used to hold items that don't even relate to each other. So in your case think of a UDT as a bunch of variables that hold data for one record alone.

Yes, record searching is done on the array.

I'm surprised that faster collections for storing the UDTs like Collections or Dictionaries were not even implemented.
How is the search actually done? It loops through each row and each column looking for a match? And have they employed any searching and sorting algorithm like binary search or bubble sort.

I have never seen UDT's until I joined here. Are they commonly used? I rarely see them mentioned on here. I have used classes for some projects I have built but in general I have not needed to.

Yes, if you've used classes then you would have come across Types. In a class in VBA you have a constructor/destructor, you can instantiate multiple instances of a class but with Types you don't instantiate and if memory serves me right Types can't be passed ByVal.

There are a couple of applications here that read text files which come in various formats, so I can see the benefit of loading configurations in to UDT's as the source data is fixed. This is drifting off topic slightly but am still very interested in your views on how everything is built here.

That's right and that's why it's absolutely acceptable to do that for a flat file structure, but then again it depends on the intended use of the data. Are they doing this through a WAN?

Basically the main advantage of using a type is so that you can identify which field belongs to which value but if you're going to do this for every record it's just a waste. The retrieve the value from an array all you need is the ordinal position. Since the variables in the UDT already define the field names why not just store the ordinal position there and store a dimensioned array.

In any case winshent I think they've thrown Access out the window and gone back to Excel. They might as well store the tables in a spreadsheet and read off the values from there and store in memory.

Another issue they'll face is if a new field is created it means any change to the table must also be reflected in code.

 

[winshent]

I'm told UDTs consume much less memory compared to Classes as classes are treated as Variants.

I'm surprised that faster collections for storing the UDTs like Collections or Dictionaries were not even implemented.
How is the search actually done? It loops through each row and each column looking for a match? And have they employed any searching and sorting algorithm like binary search or bubble sort.

Correct. The code loops through each record looking for a match.

Yes, it does run over a WAN. Is this important?

There are constraints on the backend to enforce referential integrity etc as you'd expect on a properly designed db, but as you say, once its loaded in to memory in the front end then its effectively a spread.

From my point of view there is an intimidating volume of code to maintain (one app in particular has around 300 pages) which to me seems unnecessary.

 

[vbaInet]

I don't know where they got that from and I don't know how they can prove that. Classes and UDTs can't be compared.

A poor way to search if I'm being honest. They are other ways to work in memory through disconnected recordsets for example. Or building a recordset from scratch. That way data is handled on the client side and not the server and you apply criteria/sorting just the way you would do to a normal recordset. ADO can do this.

It's probably a slow WAN that's why they've opted to work in memory. WAN's are just not ideal for JET databases, it could lead to corruption or loss of data and of course very slow performance. To overcome the limitations one could up-scale to SQL Server, Oracle or even Sharepoint linked lists... well any other proper Db Server.

I'm not against working in memory, but at least have a reference to every single property, attribute and method for that table and for every single field. The approach is just poor I have to say.

Ha! It has to be done because all the forms are unbound and it would also depend on the level of customisation that's needed. Plus there's a huge chunk of error handling code.