Books or Advice - Converting VBA functions to T-SQL (1 Viewer)

[Rx_]

Can anyone recommend the best way (books, articles, sites, training, application tools ... ) to convert VBA Functions to T-SQL?
One could hope there is some application to buy that would help someone create

There are about 280 VBA functions that pass in various parameters and return values that need to be converted from VBA to T-SQL and called from VBA. Access Front-End with Linked SQL Server Tables / Views

Here is a simple one for example:

Public Function Lease_LeaseTypeTribeOrAllotted(ID_Well) As Boolean   ' AKA Rule 187  Rule_187      NOTE R_30 is a SQL VIEW
Dim sqlstr As String
Dim rstMisc                         As DAO.Recordset  
On Error Goto errTrap
10    Lease_LeaseTypeTribeOrAllotted = False ' default tofalse
20    sqlstr = "SELECT DISTINCT R_30.ID_Wells, R_30.State, R_30.[Well Name], R_30.[Well Lease Type], R_30.MineralOwner, R_30.SurfaceOwner, R_30.Status, R_30.[CA No], R_30.CA_Req " & _
" FROM R_30 WHERE (((R_30.ID_Wells)=" & ID_Well & ") AND ((R_30.[Well Lease Type]) In ('Federal','allotted','tribal'))); "
30    Set rstMisc = CurrentDb.OpenRecordset(sqlstr, dbOpenDynaset, dbSeeChanges)
50            rstMisc.MoveLast    
60              If rstMisc.RecordCount > 0 Then
70                  Lease_LeaseTypeTribeOrAllotted = True
80              Else
90                  Lease_LeaseTypeTribeOrAllotted = False
100             End If
             Set rstMisc = Nothing
Exit Function
errTrap:
110             If Err.Number <> 0 Then
115                 ErrorLog "Rule_187", Err.Number   ' central Error logging
120                 Err.Clear
130                 Exit Function
140             End If
            Set rstMisc = Nothing
End Function

A SQL Server site also recommended wrapping the Function into a Select statement. This allows SQL to basically run the code on more than one thread. It was refered to as a scalar function. It is an inline table valued function. It means you can join to it just like a table.

If there is a good softare tool to purchace, that would work too. I have hundreds of these to convert and test in a short amount of time. Thanks

 

[Fran Lombard]

While I don't know of a good book or piece of software I do have an idea as to an approach to take.

Convert a few of them and get comfortable with the syntax.
Come up with as many as needed standard templates for the different types of functions you have and then write a code generator/converter in vba to loop through the collection of modules stored in access to perform the conversion writing the result to files.

This approach will takes some time to work out the kinks but once functioning should produce consistent results

 

[Uncle Gizmo]

I use (used - subscription currently lapsed) Safari Books Online

https://ssl.safaribooksonline.com/trial

The beauty of it is, you have "SEARCHABLE" Access to 10's of thousands of books. PUBLISHED BOOKS that's like searching Google on "Steroids"

Having said that, I occasionally cannot find what I want, and I search AWF...

In other words I use both.