Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 1st, 2006, 09:55 PM
Registered User
 
Join Date: Jan 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Unsuccessful execution of SQL statement within VBA

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


Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
convert a SQL Statement from MS Access to a SQL Corey Access 6 March 28th, 2007 12:33 PM
Help with SQL Insert statement in VBA Code ausmoran Access VBA 1 December 28th, 2006 05:16 PM
Unsuccessfuly execution of SQL statement in VBA cindyzhou Access VBA 0 January 1st, 2006 09:58 PM
Excel VBA to SQL & back to VBA edesousa Excel VBA 1 June 1st, 2004 02:39 AM
Execution Plans and IO Statistics in SQL Server bmains ADO.NET 0 March 19th, 2004 09:40 AM



All times are GMT -4. The time now is 02:27 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.