It's fine when you have just a handful of users, but many companies now detest having to support many copies of the application, and some just refuse.

Some have preference for web applications, purely to ease their deployment and support.

Of  course, MS Access already supports multiple Users opening the same physical instance of an application. In a two-tier file server environment, this can be inefficient, as the forms and data buzz to and fro across the ether.

However a two-tier or three tier thin-client topography (such as Citrix) can be quite efficient. The Users are only sent the "live pictures " of their application running on the Application or Presentation Server, and they send their keystrokes and mouse-moves in return. Neither forms or data are sent across the network to the user. In a two-tier system, the data resides on the same server as the application, so an Access backend can be jolly efficient (especially in terms of backend license) for small to medium sized databases. In three-tier systems, there is a database server (or many) which are usually connected to the Presentation layer by means of high speed communications such as fibre-optic link.

I haven't discussed DAP's as I have never used them in anger, and can't make up my mind how useful they could be.

Shared Applications

Whether thin-client, or just a single application used by many users, certain pitfalls must be avoided. These are mainly due to "saved" objects containing properties or data unique to an individual User. These are usually used to deal with non-symmetrical complexities in the application. Occasionally they are used for buffering to boost system performance.

Imagine two users sharing an application. They both call the same report, which is based on a temporary table, but select different criteria. The temporary table is populated by User1's data, but before User1's report is called, the data is overwritten with User2's data. User1 does not see the correct data.

The main ones I shall discuss are :

Temporary Tables

I hate temporary tables. I see the use of temporary tables as a failure by the developer to think his system through. Believe me, there is hardly any reason for the blighters. As each subsequent version of Access improves its use of recordsets, this task has become easier.

In 12 years of Access development, I have built temporary databases three times :

One common reason I see in others' applications, is that the developer has not used queries, or subforms and subreports, effectively. Perhaps the source query just needs more thought to condition the data and provide the complexity required. Perhaps subforms and subreports would provide the means to support multiple queries. Or perhaps he should just read the bloody manual!

ADO Recordsets can be used as sources for forms, reports and controls, such as listboxes, but it depends what version of Access, and whether MDB or ADP. Sub-forms and sub-reports are still lacking this connectivity.

This is an example of code to build a recordset out of nowhere and populate a listbox with a list of files. The listbox cannot be sourced directly from the recordset :

Dim rsTemp As New ADODB.Recordset

With rsTemp
  .Fields.Append "str_MonthCode", adVarChar, 6
  .Fields.Append "bln_Imported", adBoolean
  .Fields.Append "str_MonthName", adVarChar, 15
  .Open
   str_DirName = Dir(str_ImportRoot, vbDirectory)
  Do While str_DirName <> ""
    If Len(str_DirName) = 6 And IsNumeric(str_DirName) Then '--- Is this a valid directory? (nnnnnn)
      If CLng(str_DirName) > 200208 Then '--- Only data after August 2002
        .AddNew
        !str_MonthCode = str_DirName
        dtm_Temp = DateSerial(CInt(Left(str_DirName, 4)), CByte(Right(str_DirName, 2)), 1)
        !str_MonthName = Format(dtm_Temp, "mmmm yyyy")
        !bln_Imported = <a flag elsewhere>
      End If
    End If
    str_DirName = Dir
  Loop
  .UpdateBatch
  .Sort = "str_MonthCode DESC"
  .Filter = "[bln_Imported]=False"
  If .RecordCount > 0 Then Me!<listbox>.RowSource = .GetString(adClipString,_

     ColumnDelimeter:=";", RowDelimeter:=";")

End With
 

The above code shows that you can build a "virtual" recordset , add, edit or delete if you want, and use it as a source, without using saved data.

Remember you can populate a Form, Report , Listbox or Combobox (Access XP onwards) with something like :

Me.Recordset = rs

rs.Close

Set rs = Nothing

Linked Tables

When applications are shared, linked tables need to remain unchanged. Many applications can change the .Connect string of the link to point at different data sources. However, each named link can only have one source at the time. If two users want different sources, we again have trouble.

Linked tables can also expose the security of the backend database by making the User ID and Password readable.

Try and replace linked tables, with ADO recordsets, which each User can have independently of the other.

Pass Through Queries

God bless the Pass-Through-Query (SPT). It saw me through many a project, in the days when ADO was unheard of.

Although they are read-only sources, they make great quick client-server snapshots to populate your comboboxes, listboxes and reports.

Theoretically, you can use just one for the whole project, and change its source just before you run it. :

Public Function ChangeQuerySQL(pstr_QueryName As String, pstr_SQL As String) As Boolean

ChangeQuerySQL = False

Dim DB_App As DAO.Database
Dim qry As DAO.QueryDef
Set DB_App = CurrentDb()
Set qry = DB_App.QueryDefs(pstr_QueryName)
qry.SQL = pstr_SQL
qry.Close
Set qry = Nothing

ChangeQuerySQL = True

End Function
 

But of course, this would be compromised if more than one user was using this copy of the application.

Design-Time Changes

Any objects that are changed dynamically and saved will cause problems in shared instances of an applications.

For instance, to speed up multi-lingual applications, the labels captions etc. are usually are rewritten and saved, only when a different language is required. This way, there will be no delay if forms were to be rewritten every time they are opened. However, this would cause an anomaly if shared between to users of different locales.

Command Bars are often changed and saved.

Citrix

Citrix is becoming increasingly popular, bringing three-tier thin-client architecture to our applications, and support and deployment benefits to our customers.

However, from a User's point of view, there are few perceptible benefits, assuming that the application would have been written well in the first place.

I'm a mild fan of Citrix. It certainly is a good way to support rich-client applications across a company. Performance benefits are difficult to assess.  If a form displays one record at a time, then rich client would be better, as both the data and the form display would need to be sent from the presentation server, whilst rich client would need only the data as the form would already be on the PC. However, if that form were based on an Access query, and the the required record was in a table of 1,000 records then the presentation server would sort through the 1,000 records, only sending the picture of the selected record to the User, whilst the rich-client application would need to be sent all 1,000 records to sort through. However, if the query were a Pass Through Query, we'd be very much in the original position again.

As Citrix is three tier, you may have issues with your permissions. For instance a User may have permissions to access the application, but does the application have permissions to access the ODBC driver?

Microsoft Terminal Services