Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    London, England
    Thanked 0 Times in 0 Posts

    DoCmd.RunSQL (2000)

    Mostly when I update tables from code I create a little SQL UPDATE, INSERT or DELETE string, turn SetWarnings to False then run the SQL using DoCmd.RunSQL. It's easy code to write and I suspect that it might run faster than opening up tables and explicitly changing field contents with ADO or DAO. However, I do not get any indication if something goes wrong. Is there any way, other than searching the updated tables for the presence/absence of my changes, to find out if a DoCmd.RunSQL operation has been successful? Also, I was once at a meeting where some people were recommending against its use, but it was a long time ago and I can't remember their arguments. Is DoCmd.RunSQL frowned upon and, if so, is there an alternative?


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: DoCmd.RunSQL (2000)

    In itself, RunSQL is often a good way to execute an action macro, since you stay within the Access interface. But I mostly use the Execute method of the DAO Database object. Since it uses DAO directly, it is more efficient than RunSQL. You can use the Errors collection of the DAO DBEngine object to retrieve all error messages associated with the action. The only downside is that in some situations, you can get an error message that another user has modified your data; this is because you bypass the Access interface.

    Example code (you need to have set a reference to the Microsoft DAO 3.6 Object Library for this):

    Sub TestExecute
    Dim dbs As DAO.Database
    Dim errLoop As DAO.Error
    Dim strSQL As String

    strSQL = "UPDATE Table Set Field = Value WHERE Condition"
    Set dbs = CurrentDb

    On Error GoTo ErrHandler
    dbs.Execute strSQL, dbFailOnError

    Set errLoop = Nothing
    Set dbs = Nothing
    Exit Sub

    If DBEngine.Errors.Count > 0 Then
    For Each errLoop In DBEngine.Errors
    MsgBox errLoop.Description
    Next ErrLoop
    End If
    Resume ExitHandler
    End Sub

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: DoCmd.RunSQL (2000)

    In addition to the method Hans suggests, you can create a temporary querydef in code and execute that. You don't need to set warnings off and the querydef object has a RecordsAffected property that will tell you how many items were affected by the query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts