Running multiple queries in access with two variables changing

Question

Right now I have this query that I am running in Access:

UPDATE [Part B Mater Table] SET [Part B Mater Table].MCCat = 1
WHERE ((([Part B Mater Table].fyear)=1990)) AND CUSIP IN
(SELECT TOP 33 PERCENT [Part B Mater Table].CUSIP
FROM [Part B Mater Table]
WHERE ((([Part B Mater Table].fyear)=1990))
ORDER BY [Part B Mater Table].MC DESC);

I need to be able to loop through different years from 1990 to 2012 and change the DESC at the end to ASC, so there is one ASC and one DESC run per year.

Would it be best making a macro to do this or using VBA?

If it helps I have a php script that prints out all the Access SQL code I need, so I could easily modify that to show the VBA code, if it is possible to write out queries within VBA.

Any help is appreciated, thanks.

score 2 · Accepted Answer · answered Apr 27 '14 at 05:04

VBA:

Option Compare Database



Sub sqlCommand(iYear As Integer, sOrder)
    Dim sSQL As String
    sSQL = "UPDATE [Part B Mater Table] SET [Part B Mater Table].MCCat = 1 " & _
        "WHERE ((([Part B Mater Table].fyear)=" & iYear & ")) AND CUSIP IN " & _
        "(SELECT TOP 33 PERCENT [Part B Mater Table].CUSIP & " & _
        "FROM [Part B Mater Table] " & _
        "WHERE ((([Part B Mater Table].fyear) = " & iYear & ")) " & _
        "ORDER BY [Part B Mater Table].MC " & sOrder & ")"
    DoCmd.RunSQL sSQL
End Sub

Sub update()
Dim aOrder(2), sOrder As Variant, iCounter As Integer
Dim iYears As Integer

aOrder(0) = "ASC"
aOrder(1) = "DESC"

For Each sOrder In aOrder
    For iCounter = 1990 To 2012
        sqlCommand iCounter, sOrder
    Next
Next sOrder
End Sub

Running multiple queries in access with two variables changing

1 Answers1