An Access Programmer - with Linked SQL Tables - Any ideas on the best way to approach this design would be welcome?
Here is a simple function that returns a True/False written in MS Access.
It is part (1 of 180) of a Rules Engine. In a MS Access Query for example, the query can call
Column1 [ID_Building] Comun2 StakingStatus: Building_Status_Staking_Does_Building_FieldWorkDate_Land([ID_Building])
The output would be something like 343434 True
For small pulls of a dozen records, it cost a few mili-seconds. The SQL Server Native Client actually does convert it to some sloppy T-SQL.
The problem is - I will soon have about 120 columns with more complex rules.
Times around 100 to 500 concurrent users.
Should I use a Stored Procedure, a TSQL pass-through query, or other method?
This is a Rules Engine under development. The rules are run when one of many key fields across many forms are changed.
Each user passes in a single [ID_Building] for a session that has around 30 forms (not all open of course) If they change one value, the rules engine evaluates all of the rules and updates a dashboard that needs to give the appearance of "real-time". (e.g. within 1 to 10 seconds).
Think about a tax form: change a dependent from 1 to 2, you are a homeowner, 2 incomes, with rental income, .... - the change from 1 to 2 activates a Dashboard that "recommends you file separately"
Public Function Building_Status_Staking_Does_Building_FieldWorkDate_Land(ID_Building) As Boolean
' Rule Building Status - Staking Staking Status Can NOT have an a Field Work Date of type LAND
Dim rstMisc As DAO.Recordset
Dim SQLMisc As String ' NOTE Added IP Date afterwards
10 Building_Status_Staking_Does_Building_FieldWorkDate_Land = False ' set function to false until proven true
SQLMisc = "SELECT APD_FieldWorkDate_2.ID_Buildings, APD_FieldWorkDate_2.ID_Bio_Svy_Type FROM APD_FieldWorkDate_2 " & _
"WHERE (((APD_FieldWorkDate_2.ID_Buildings)=" & ID_Building & ") AND ((APD_FieldWorkDate_2.ID_Bio_Svy_Type) In (15,18)));"
30 Set rstMisc = CurrentDb.OpenRecordset(SQLMisc, dbOpenDynaset, dbSeeChanges)
40 On Error Resume Next
60 If rstMisc.RecordCount > 0 Then
70 Building_Status_Staking_Does_Building_FieldWorkDate_Land = True
90 Building_Status_Staking_Does_Building_FieldWorkDate_Land = False
100 End If
110 If Err.Number <> 0 Then
130 Exit Function
140 End If
' A Case statement would be OK too.