I have written a Rule Engine in MS Access (client) that uses linked tables with SQL Server Native Client to SQL Server 2008 R2.
There are around 150 Binary Rules. Each Binary Rule has combinations of SQL and other logic. They are designed to return a True/False.
These Binary Rules are the first tier for the Rule Engine conversion.
The process is now to take the tested VBA and convert each Binary Rule to TSQL UDF.
The next layer, a Status Rule - will take one parameter and then call on dozens of the Binary Rules. Depending on the status number, it will expect different Binary Rules to return some pattern of True or False.The code below is the most simple rule. The purpose is to get a template for conversion from VBA to TSQL.
Function Rule71(ID_Wells As Integer) As Boolean
' Rule 71 Well is a Facility - Pass in Primary Key for Well, run SQL for this ID. If record returns then True/ else False
Dim rstMisc As DAO.Recordset
Dim rstExclude As DAO.Recordset ' excluded states
Dim SQLMisc As String ' NOTE Added IP Date afterwards
Dim SQLExclude As String ' use to exclude states
Dim RuleResult As Integer
10 On Error GoTo errTrap
20 Rule71 = False ' false until proven true
25 ' the ID_Wells parameter is passed in to SQL statement
35 SQLMisc = "SELECT Wells.ID_Wells, Wells.Well_Name, Wells.ClassificationID FROM Wells WHERE (((Wells.ID_Wells)=" & ID_Wells & ") AND ((Wells.ClassificationID)=3));"
40 Set rstMisc = CurrentDb.OpenRecordset(SQLMisc, dbOpenDynaset, dbSeeChanges)
50 If Not (rstMisc.EOF And rstMisc.BOF) Then rstMisc.MoveLast ' 1 (or more) record indicates a True 0 records a False
60 If rstMisc.RecordCount > 0 Then
70 Rule71 = True
90 Rule71 = False
100 End If
110 Exit Function
120 If Err.Number <> 0 Then
130 Debug.Print "Function Rule71 has problem with well " & ID_Wells
150 End If