Jim Lawrence
accessd at shaw.ca
Fri Jan 24 18:04:31 CST 2014
Hi all:
Now that I have listed some of the basics of using of using ADO to make an application be able to connect into the data servers from anywhere in the network and from any where in the province...objects like list and combo boxes have to be filled.
Note: The code is not a complete operational example. It just covers the high point and leaves out the common detail.
The list box is real easy. I just have a list box table template and populate in real-time when needs.
' You should first clear the recieving table or generate a new one.
...
Dim rs2 As DAO.Recordset
if db1.TableDefs(lsTemporaryTable).Name <> "" Then _
lbTableExists = True
If lbTableExists = True Then
strSQL = "DELETE " & lsTemporaryTable & _
".* FROM " & lsTemporaryTable
Else
strSQL = "SELECT " & lsTableTemplateName & _
".* INTO " & lsTemporaryTable & _
" FROM " & lsTableTemplateName
End If
db1.Execute strSQL
' Refresh the current MDB's table list.
If lbTableExists = False Then db1.TableDefs.Refresh
' Create a local table defined recordset from a table. Probably the only time a DAO
' table might be necessary. Locally DAO is faster than ADO but only marginally.
Set rs2 = db1.OpenRecordset(lsTemporaryTable, dbOpenDynaset)
...
Set rs1 = New ADODB.Recordset
' Must be a client side cursor/recordset
rs1.CursorLocation = adUseClient
' Note: recordset created on the client side default to
' Static and Read-only. I always add the following
' options to the standardize and clarify for future
' programmers.
rs1.Open objCmd, , adOpenForwardOnly, adLockReadOnly
' Transfer data from ADO recordset to the empty
' recorsdset and temporary table.
' fill the table...
With rs1
If .BOF = False Or .EOF = False Then
.MoveFirst
Do While .EOF = False
rs2.AddNew
For i = 0 To .Fields.Count - 1
rs2.Fields(i).Value = .Fields(i).Value
Next
rs2.Update
.MoveNext
Loop
Else
lsTemporaryTable = "" 'No data
End If
End With
' Refresh the current MDB's table list.
If lbTableExists = False Then db1.TableDefs.Refresh
At the end of that you can have a table with information for a list box source. It can be created upon request or via refresh so it is always kept reasonably current.
'-----------------------------------------------------------------
When it comes to ComboBoxes it can be a little more complicated but it works similarily. In the combobox object "row source type" property, like "FillCompanyServiceCategoryList" create a public function as displayed below. The function must be assembled in the follow format.
Public Function FillCompanyServiceCategoryList(ctlBox As Control, id As Variant, row As Variant, col As Variant, CODE As Variant) As Variant
'Common Combo and List box fill function.
'Assumes rsCompanyServiceCategory recordset is the supplied data and has
'equal or more fields than required in the control.
On Error GoTo Err_FillCompanyServiceCategoryList
Dim mvReturnVal As Variant
mvReturnVal = Null
With rsCompanyServiceCategoryList
Select Case CODE
Case acLBInitialize ' Initialize.
Set rsCompanyServiceCategoryList = New ADODB.Recordset
Note: the "rsCompanyServiceCategory" function call, gathers the data as described in the previous two chapters. Every time this function is initiated or refreshed it repopulates. If the app is done right it will either refresh from the source data or from recordset stored in memory or from a local table. Make sure the source data is using a static object not a forward-only object or the function will fail at case acLBGetValue.
Set rsCompanyServiceCategoryList = rsCompanyServiceCategory.Clone
If .BOF = False Or .EOF = False Then
.MoveFirst
mvReturnVal = .RecordCount
Else
mvReturnVal = 0
End If
Case acLBOpen ' Open.
mvRetu rnVal = Timer ' Generate unique ID for control.
gvComboTimer = mvReturnVal
Case acLBGetRowCount ' Get number of rows.
mvReturnVal = .RecordCount
Case acLBGetColumnCount ' Get number of columns.
mvReturnVal = ctlBox.ColumnCount
Case acLBGetColumnWidth ' Column width.
mvReturnVal = -1 ' -1 forces use of default width.
Case acLBGetFormat ' Get format
mvReturnVal = -1
Case acLBGetValue ' Get data.
.MoveFirst
.Move (row)
mvReturnVal = .Fields(col)
End Select
End With
FillCompanyServiceCategoryList = mvReturnVal
Exit_FillCompanyServiceCategoryList:
Exit Function
Err_FillCompanyServiceCategoryList:
'Handles error situation caused an apparent unrelated error(s)
'generated in other modules. (It loses its brains...)
If Err.Number <> 91 Then
ShowErrMsg "FillCompanyServiceCategoryList"
End If
Resume Exit_FillCompanyServiceCategoryList
End Function
In summary if the application is set up right it will be very resilient in environments where the data source is not always present or the connections are slow or unstable. I have lost more data and corrupted more DAO databases trying to function in a similar situation and so abandoned that technology except where absolute stability can be guaranteed...and that, from experience, is only on the host computer you are presently using.
Eventually, this system worked so well, that there could be a hundred people of a possible thousand on the network and there were no lags, that anyone noticed and in five years there was no reported loss of data. (...always an excuse used by "bound" proponents...)
Note: when using such a system you can not use auto-numbering. It is best to have a Stored Procedure on you data server which issues up the appropriate values. Also a date-time field should be kept with each record so any delay getting online can be managed in case the data becomes stale or dated and requires a refresh.
Data sourced from a number of databases can be easily assembled using UNION or UNION ALL queries.
After the above described Registration and documentation application was created, then a province wide Judge Scheduling system was build and finally a Project management program was assembled. The Project Management was built using five distinct databases. It pulled the contractors and employees, from one source, accounting data from another and assembled all with the documentation like documents, faxes, invoices, emails related to this current project...al linked to a single form. From there it was easy to see if the project was remaining in scope and within budget. To finish it off, the application would generate Excel graphs showing progress and detail suitable for gantt charting and similar management. Unfortunately, the result became too popular and it ended as too many interests were being challenged. The local IT who was heavily invested in Oracle and Oracle forms, other Oracle support companies and even Oracle itself who held province exclusivity.
Eight years and many millions of dollars later, the MS Access applications have all been replaced but it just goes to show how far ahead of the loop Access use to be...and could still be if Microsoft had not lost it way. Now a days there are a host of great applications that have filled the gaps and even far surpassed MS Access's lead but will there ever again be one system that consolidated so many features?
End of part 3
Jim