Results 1 to 3 of 3
  1. #1
    edmscan is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    168

    Determing number of affected records from Insert / Update SQL statement.

    I would like to know the best way to determine if a SQL statement was successful. Mostly ... for inserts and updates. I would like to be able to check the number of records affected by the SQL statement. I am
    not using a backend of any sort .. just normal Access database.

    I like to use DoCmd.RunSQL statements .. but understand that I cannot do this. I go back to the Access 2 days .. and guess I just never learned the best way to do this.

    Normally I let the insert run ... and then do a Dlookup (or actually Elookup) and check if that record is there. Works .. but is there a better way ?



    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    edmscan is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    168
    Not bad of a solution .. but I think I will keep looking. I want a several line .. simple solution. What I am doing is working .. but probably not the most efficient. But my database is very small and it is fast, so no performance issues.

    I did find this.

    Got it here .. http://allenbrowne.com/func-ado.html#ExecuteADO

    Function
    ExecuteADO() As Long
    'Purpose: How to execute an action query with ADO.
    'Return: Number of records affected by action query.
    Dim strSql As String
    Dim lngKt As Long

    strSql = "INSERT INTO tblClient (Surname, FirstName ) " & _
    "SELECT 'Smith' AS Surname, 'Jim' AS FirstName;"
    CurrentProject.Connection.Execute strSql, lngKt

    ExecuteADO = lngKt
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 10-22-2013, 07:37 AM
  2. INSERT statement
    By GraeagleBill in forum Programming
    Replies: 2
    Last Post: 03-29-2013, 12:53 PM
  3. Insert Into statement
    By TimMoffy in forum Programming
    Replies: 7
    Last Post: 07-13-2012, 07:10 AM
  4. Replies: 2
    Last Post: 12-20-2011, 07:33 AM
  5. Insert statement
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 08-19-2011, 02:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums