CODEWITHSUNDEEP
ms-accessms-access-2013
IMTheNachoMan
IMTheNachoMan

Reputation: 5821

is it possible to use WHERE criteria stored in a table to make updates in another table in MS Access

I figure it'll be easier to explain with an example. I have two tables like so.

+----+--------------------------------+---------+
|                    Table 1                    |
+----+--------------------------------+---------+
| ID |            criteria            |  value  |
+----+--------------------------------+---------+
|  1 | [Table 2].[name] like "dingo*" | alpha   |
|  2 | [Table 2].[location] = "here"  | bravo   |
|  3 | [Table 2].[active] = TRUE      | charlie |
+----+--------------------------------+---------+

+----+-------------+----------+--------+-------+
|                   Table 2                    |
+----+-------------+----------+--------+-------+
| ID |    name     | location | active | value |
+----+-------------+----------+--------+-------+
|  1 | dingo bingo | there    | FALSE  |       |
|  2 | bob         | here     | FALSE  |       |
|  3 | bingo       | there    | TRUE   |       |
|  4 | bingo bob   | here     | FALSE  |       |
+----+-------------+----------+--------+-------+

Table 1 has rows of matching "criteria" for Table 2 and a value for that row in Table 2. I know I can use VBA to iterate through each row in Table 1, create an SQL update string which can be used to update Table 2.

For example, the SQL for the first row in Table 1 is:

UPDATE [Table 2]
SET [Table 2].[value] = "alpha"
WHERE [Table 2].[name] like "dingo*"

And the result is:

+----+-------------+----------+--------+-------+
|                   Table 2                    |
+----+-------------+----------+--------+-------+
| ID |    name     | location | active | value |
+----+-------------+----------+--------+-------+
|  1 | dingo bingo | there    | FALSE  | alpha |
|  2 | bob         | here     | FALSE  |       |
|  3 | bingo       | there    | TRUE   |       |
|  4 | bingo bob   | here     | FALSE  |       |
+----+-------------+----------+--------+-------+

But I'm wondering if there is a way to use a single query to do this instead of having to loop through each row in Table 1.

If it matters, it is guaranteed that the criteria in Table 1 have no overlaps. In other words, if ID 1 of Table 1 matches ID 1 in Table 2 then no other rows in Table 1 will match ID 1 in Table 2.

Upvotes: 0

Views: 42

Answers (2)

Don Jewett
Don Jewett

Reputation: 1967

No, it is impossible without VBA.

Allowing that would collapse the space-time continuum or at least result in a lisp.

But it is possible to do without looping through every record manually.

Write a public Function that accepts the sql fields as parameters and uses them to build and execute a sql statement. I'm not sure it's advisable, but it's unlikely to collapse the space-time continuum.

Public Function RunTableSQL(ByVal sValue As String, ByVal sCriteria As String) As Long

    Dim sSQL As String

    On Error GoTo EH
    sSQL = "UPDATE [Table 2] SET [Table 2].[value] = "" & sValue & "" _
        & "WHERE " & sCriteria
    CurrentDb.Execute sSQL        
    RunTableSQL = 0

    Exit Function
EH:
    RunTableSQL = Err.Number
    Err.Clear
End Function

And run for every row by creating a query like this:

SELECT *,RunTableSQL([value],[criteria]) AS Result FROM [Table 1]

Upvotes: 1

shawnt00
shawnt00

Reputation: 17915

Yes, you can look at other tables inside an update. But it looks like you're storing SQL expressions in your data and unfortunately you won't be able to use those as part of a larger query.

There might be for you to encode your filters in a way that it usable from a query but it won't look like free-form SQL. The alternative is to look at dynamic SQL and construct the query string on the fly. Of course, that would require VBA.

Upvotes: 0

Related Questions