Access-Adding a Calculated Field from a Linked Excel Table

Question

I have two tables, Table: A which is built in Access and Table: Error which is a linked table in Excel. The Error Table has the calculation that is SQL coding that I want to be in the Query within Access. An example of the Error is:

 IIf([Table A].[RETURN_DAYS]<>30,"-RETURN POLICY DAYS SHOULD BE 30","")

The Columns in the Errors Table are:

|Audit Name|Error Codes|
|Audit 1   |the code above
|Audit 2   |

Currently I manually Copy and paste the Error Code into the Query within the Field from the Excel table in order for the calculation to take place. This takes an incredibly long time since I have over 150+ Audits and 5 Different Error fields.

I want to be able to select the error from the error column in the linked table that is applicable based on the Audit name and place that error in the field so that when the Query is ran, access completes the calculation. I can not find any information on how to do this or if its possible. Any help is appreciated.

Not sure I understand... Your Excel sheet has a list of 'audits to perform', where the column named 'Error Codes' is actually a SQL string that you want to run in Access against table(s)? Then, in Access, you want to randomly select one or more 'Audit's' to perform (execute the SQL) in Access? Where are the results of your query (example above) going? To a report, or an 'Audit Results' table? — Wayne G. Dunn
This is correct. My process right now is that I have a Query Built in Access with multiple tables. One table being Table A. There are Multiple Fields in the Query Based on Table A. The Query is is essentially the Audit, called Audit 1. I then have an Excel Workbook that is a database and performs calculations to tell me what the SQL String should be for each Audit. So for Audit 1, the Error is created automatically in excel based on calculations and specific parameters. I then paste that SQL String into the Query(Audit 1) from the Excel Table. — MrLockett
Paste it into the Field Name so that it performs the calculation of the SQL String. I hope that clarifies the process. The results of the Query are going to a Audit 1 Table. I want to be able to link the Excel table(Errors) in access and select the SQL String Based on the Audit Name(Audit 1) and place that sting in the Access Query as a Field so that the calculation gets performed and spits out the result. In this case, if Return days<>30 then it populates -RETURN POLICY DAYS SHOULD BE 30 for each result. — MrLockett
I see, The Excel workbook has multiple tabs. One tab(The Database) has a number of parameters. Those parameters drive What the SQL String should be for each Audit on the "Errors Tab" in excel. So if I update a parameter the SQL String Updates automatically. The issue with this is, I then have to go into Access and update the Query with the new SQL String so that the calculation is made in the Access Query. I have to do this for every single Audit Query. Excel is basically generating the SQL String on the tab labeled "Errors Table" based on the parameters of (The Database) — MrLockett
OK, you change a 'parameter' in a sheet in Excel, and magically, that updates the SQL string on another sheet. Now you want to automatically apply that new SQL string to a query that is stored in Access? Do you also want to execute that query in Access at that time? If desired, you can use VBA to change the SQL in Access (and execute if necessary). Is this what you are asking? — Wayne G. Dunn

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2017-03-03T17:30:58

Since I have no idea about what cells you are using, or sheet names, you will need to adjust the following code.

This code will monitor changes to a certain Column (Your SQL) and will change the query in Access. Assumes the query name is in col A, SQL in col B.

Place this code in the sheet where you will make changes.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    'Monitor the range of cells you may be making changes to. I have assumed SQL is in col 2 (B)
    If Target.column <> 2 Then Exit Sub         ' If change not made in column 'A' then Ignore!

    ' Assuming you have the query name in Col A and the SQL in Col B
    If Target.column = 1 Then
        MsgBox "You are changing the query name?"
    End If
    If Change_SQL(Cells(Target.Row - 1, Target.column), Cells(Target.Row, Target.column)) <> "passed" Then
        MsgBox "Update failed."
    End If

End Sub

Function Change_SQL(strQueryName As String, strNewSQL As String) As String

'Declare Variables'
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Dim strSQL_Old As String
Dim strSQL_New As String

'Connect to the Database'
On Error GoTo Error_Trap

    Set dbs = DBEngine.OpenDatabase("C:\data\Access\xxxxxx.mdb", False, False, _
                                                "MS Access;")
    Set qdf = dbs.QueryDefs("Query5")
    strSQL_Old = qdf.Sql
    Debug.Print "Query Name: " & qdf.Name & vbTab & qdf.Sql
    qdf.Sql = strNewSQL
    qdf.Close

    ' You can execute the query if needed....
    'Set rst = dbs.OpenRecordset(strNewSQL)    ' If it's a select query

    'Do While Not rst.EOF
    '    Do whatever
    '    rst.MoveNext
    'Loop

    'rst.Close
    'Set rst = Nothing
    Set dbs = Nothing
    Change_SQL = "passed"
Exit Function

Error_Trap:
    Change_SQL = "failed"
    Debug.Print Err.Number & vbTab & Err.Description
    MsgBox Err.Number & vbTab & Err.Description
    Exit Function

    Resume
End Function

Access-Adding a Calculated Field from a Linked Excel Table

1 Answers