I have a problem about running SQL statement in VBA code. I will appreciate a looot if you can help me solve the problem ASAP.

I tried to run the following statement in my VBA code to update a table in my database:

DoCmd.RunSQL "UPDATE TBL_GRANTBASEDATA, TBL_QUARTER SET TBL_GRANTBASEDATA.Retire_QTR = TBL_QUARTER!QUARTER WHERE ((([TBL_GRANTBASEDATA]![Retire_Date])<=[TBL_QUARTER]![End_Date] And ([TBL_GRANTBASEDATA]![Retire_Date])>=[TBL_QUARTER]![Start_Date]));"

 It turns out that the table will not be updated if I run the above SQL statement with my other VBA codes. (ACCESS doesn't give any error messages even though the database was not updated.) But if I run the SQL statement by itself rather than within the other codes, the database will be updated successfully. (When I set a breakpoint at the above SQL statement and run it manually within the other codes, the database are updated successfully too.)

Apparently, the SQL statement and the other codes don't have any syntax errors. I wonder why ACCESS doesn't execute the SQL statement when the SQL statement is put within the other VBA codes. Is it because the table it tried to manipulate was locked by the other codes?

The following are all the VBA codes including the SQL statement I'm talking about. When I run the following codes together, the database is not modified by the SQL statement (the last sentence). But if I run the last sentence and the other codes separately, the database was modified successfully.


    Dim rstDataLoad As New ADODB.Recordset
    With rstDataLoad
        Set .ActiveConnection = cnnSHELL
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Source = "SELECT TBL_GRANTBASEDATA.* FROM TBL_GRANTBASEDATA;"
        .Open
    End With

   'Calculate Retire_Date
    rstDataLoad.MoveFirst
Do While Not rstDataLoad.EOF

    Calculation omitted.

        rstDataLoad.MoveNext
    Loop
    rstDataLoad.UpdateBatch
    rstDataLoad.Close
    Set rstDataLoad = Nothing

    DoCmd.RunSQL "UPDATE TBL_GRANTBASEDATA, TBL_QUARTER SET TBL_GRANTBASEDATA.Retire_QTR = TBL_QUARTER!QUARTER WHERE ((([TBL_GRANTBASEDATA]![Retire_Date])<=[TBL_QUARTER]![End_Date] And ([TBL_GRANTBASEDATA]![Retire_Date])>=[TBL_QUARTER]![Start_Date]));"