Unsuccessful execution of SQL statement within VBA codes

[cindyzhou]

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]));"

 

[llkhoutx]

You've posted this elsewhere in this forum. don't expect two answers from me.

 

[cindyzhou]

Thanks for your reply. But it's still not working. As I said, the SQL statement runs well by itself. It just doens't work when I put it at the end of other VBA codes. I can make a query by the sql statement given before. The query runs OK by itself, but it doesn't work when I use DoCmd Openquery XXX to open it by VBA code.

I post it here because I think it is more of a VBA issue than a SQL issue. Thanks again!

 

[Bat17]

I am not sure what is causing your problem but it may be worth adding a DoEvents() before running the SQL

HTH

Peter

 

[Laurentech]

I'm certainly no expert, and this is just a stab in the dark, but don't references to dates have to begin and end with an '#'?

Larry

 

[cindyzhou]

Thanks a lot for the reply. But it's still doen't work. The only things works so far is the stop and run the query manually. Has anyone ever seen this problem before?

 

[Bat17]

Does it work if you add a msg box to break the code?
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

MsgBox "wait here", vbOKOnly

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]));"

HTH

Peter