[AccessD] Problem of a listbox's response on network... Part 1

Hi all:

I know I promised to answer back before last Christmas but things have been busy to say the least. Now that I have a few uninterrupted minutes I will answer.

First the preamble; MS Access is probably the best quick database rapid design application ever built. I have been looking for over thirty years and nothing has proved better. Access's one weakness is it database. It has always been too small, lacking security and is unstable to say the least. I know many will point out their prowess on keeping a full MS Access running, in spite off, other than because off, hosting many clients over unstable networks.

It was back in the late nineties I realized that the MDB was a toy in comparison to the strength of the Front End so to that end I moved the all subsequent applications to use ADO database connection even to the associated MDB.

I worked on government contracts for years and could not get anyone to listen or try MS SQL. The entire provincial government was a closed Oracle shop. Finally, supposedly, for a small job, I was given a nice new Dell server box, 7 drives and 8 GB of RAM along with a copy of MS SQL. The only problem was that the version of MS SQL had only a dozen connection licenses...hardly enough for a serious application.

I finally figured out a way to extend a MS Access application. First dropped was the concept of "bound data", as it just wasn't possible. One user would easily consume all licences. Next, requirement was that the new application, that I was assigned, had to be provincial wide. This meant that users had to be able to use the product, in real-time, in every urban and rural setting, on hardware connect from fiber-optic to a 56K modem and at sometimes, disconnected. Another challenge was to find a way have every computer using the application not having to have a tech either walk the staff through a complex installation procedure or go on site.

At that point I settled on ADO data and data connection. Every Windows computer since Windows95 up to Windows8.1, has ADO pre-installed. (The new version is 64 bit so this might cause complications?) This meant that ODBC did not have to be used and DAO was for the most part DOA. Below is a list of a few basic connection function:

...
' Microsoft connection string...standard security MS SQL
gstrConnection1 = "Provider=SQLOLEDB;" & _
                  "Initial Catalog=Registration;" & _
                  "Data Source=MyMSSQLServer;" & _
                  "Integrated Security=SSPI"

'   Test connection string and handle errors
Set mobjConn = New ADODB.Connection

mobjConn.ConnectionString = gstrConnection1
'mobjConn.CursorLocation = adUseClient	'adUseServer
mobjConn.Open
...

'----------------------------------------------------

...
' Microsoft connection string...standard security Oracle, tnsnames.ora
gstrConnection2 = "Provider=msdaora;" & _
                  "Data Source=MyOracleServer.gov.bc.ca;" & _
                  "User Id=" & myUsername & ";" & _
                  "Password=" & myPassword

'   Test connection string and handle errors
Set mobjConn2 = New ADODB.Connection

mobjConn2.ConnectionString = gstrConnection2
mobjConn2.Open
...

'----------------------------------------------------

...
  strMDBVersion = CurrentDb.Version
  strDBNameLocation = CurrentDb.Name
  strDBName = Dir(strDBNameLocation)
  strDatabaseLocation = _
    Replace(strDBNameLocation, strDBName, "MyMDBServer.mdb")

  ' We will assume that we are connecting to a remote
  ' backend MDB Datbase. In this case we will just connect
  ' another DAO database called 'MyMDBServer'.
  
gstrConnection3 = "Provider=Microsoft.Jet.OLEDB." & strMDBVersion & " ; " & _
                  "Persist Security Info=False; " & _
                  "Data Source=" & strDatabaseLocation

'   Test connection string and handle errors
Set mobjConn3 = New ADODB.Connection

mobjConn3.ConnectionString = gstrConnection3
mobjConn3.Open
...

'-------------------------------------------------------

The beauty of using ADO connection standards is that you can connect to virtually any standard data source, one at a time or all together and no previous specific computer configuration is necessary. One of the major problems with auto downloading a single MS Access copy, to hundreds of computers on a network, is the potential for conflicts. Connecting via ADO asynchronised resolves these problems.  

End of part 1

Jim