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

Hi all:

Once all the connections you need are established then you have to create a method for quickly filling combo and list boxes but first some simple basics of retrieving data with ADO:

In order to receive data from your data source a bit of preparation is necessary.
 
' Can be a client side cursor/recordset...
     rs3.CursorLocation = adUseClient	
' or a Server side recordset...  
     rs3.CursorLocation = adUseServer 
' ...or used if recordset is going to be bound or will use a Dynamic recordset

Note: recordsets created on the client side default to Static and Read-only. I always add the following options to the standardize and clarify for future ease of reading but it is not necessary.

     rs3.Open objCmd, , adOpenStatic, adLockReadOnly
' or
     rs3.Open objCmd, , adOpenDynamic, adLockOptimistic

There are three types of ADO recordsets that can be created. Dynamic, Static and Forward-only. (There is a keyset but I don't use it....no particular reason.) 

A recordset opened as Dynamic, adOpenDynamic can be 'bound' to the source data and has the capability to have records added, deleted as well as been forward and backward linked. Once this type of recordset has been created it does not need to remain bound or connected to the source table...see example further down on how a disconnect a recordset. 

The Static adOpenStatic recordset has forward and backward links but records can not be added or deleted to. This type of recordset is a good source for list and combo lists. The Forward-only recordset only has a forward link. It is great when you are populating a report or table source as it is very fast to process.
 
'----------------------------------------------------

'    strSQL ="Can be any appropriate sql string/query..."

Note I think it is unwise to have a remote database that uses SQL strings as it lends itself to having your database through, insertion hacks, being compromised. So many large companies have continued to experience such issue...their DB tech have not learned Database management 101. This was the trouble with some systems that used MySQL before it had the SP (Store Procedures) option.

    Set objCmd = New ADODB.Command
    With objCmd
        .ActiveConnection = gstrConnection3

' Use one of the two following syntaxes
        .CommandText = strSQL
        .CommandType = adCmdText
' or
        .CommandText = "FillCityList" 'Stored Procedure name
        .CommandType = adCmdStoredProc

The parameters can be passed to a SP or Procedure (MS SQL or Oracle). Example of a single parameter being passed, to receiving variable, on the server side, DistrictCode, of a string/character type. The variable, being pasted to receiving variable, of a length of 15 characters and the data is stored locally in variable named strDistrictCode. Example of description below:

        .Parameters.Append .CreateParameter("DistrictCode", _
                                             adChar, _
                                             adParamInput, _
                                             15, _
                                             strDistrictCode)

' else

	strSQL ="Can be any appropriate sql string/query..."

    End With

Prepare receiving recordset
     
    If Not rsCityList Is Nothing Then Set rsCityList = Nothing
    Set rsCityList = New ADODB.Recordset
    
    With rsCityList
        .CursorLocation = adUseClient
' or
        .CursorLocation = adUseServer ' Used with Dynamic recordset

The three options (only needs one) below are a simple get data and fill recordset request given the parameters above...whether the data was being retrieved from a MS SQL, Oracle or an even MDB database...or many other relational sources. (I understand that data can even be retrieved from NoSQL DBs but have not tested this...yet.)

        .Open objCmd, , adOpenStatic, adLockReadOnly
' or
        .Open objCmd, , adOpenDynamic, adLockOptimistic
' or
        .Open objCmd, , adOpenForwardOnly, adLockReadOnly

        If .BOF = False Or .EOF = False Then .MoveLast 'if not Forward-Only recordset 
    End With

Now that the recordset has been created to insure the recordset, even if it is Dynamic and its cursor location was server side, i.e: .CursorLocation = adUseServer, it can be disconnected by issuing the following line of code... 

'   Create a Disconnected the recordset.
    Set rsCityList.ActiveConnection = Nothing

...and to test it you could run a piece of code as follows to display/dump a whole recordset.
   With rsCityList
      .MoveFirst
      Do While .EOF = False
         For i = 0 To .Fields.Count - 1
            Debug.Print .Fields(i).Value
         Next
         .MoveNext
      Loop
   End With

;-------------------------------------------------------
 
Notes: 
One of the fun things you can do once a Dynamic recordset has been disconnected, is run it like you would any other table except this one is stored in memory so processing is super fast. Add, delete, update, search and sort.   

One of the greatest time wasting processes is the use of "Orderby" or "Order" within a query whether local or server side. This process can be speeded up by only performing an order process on a loaded recordset. (With the prospects of only having 12 connection licenses and the potential of up to a thousand users, it was necessary to shave off every second of server processing time.) Example that was a real time saver...sort the retrieved recordset.

    rsCityList.Sort = "DistrictCode DESC, CityName ASC" 

End of part 2

Jim