Results 1 to 11 of 11
  1. #1
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21

    Looping through table using VBA and deleting certain records

    Hi all,

    Whilst I have been using VBA in excel for many years I have only just started connecting excel with Access tables. The problem I am trying to solve is from an excel workbook I need to loop through a table in Access that has a bunch of locations in it and delete any records that start with the letters "Temp", their whole names will be Temp1, Temp2 etc. This is the code I have that successfully deletes one record however I cannot manage to get it to loop through the whole table. The commented out code is what I have been trying to use for the loop. Any suggestions would be appreciated.

    Kind Regards,


    Marcus

    Code:
    Sub DelTemps()
    
    Dim i As Long
    Dim TempName As String
    
    '## Deletes the temporary waypoints place in the database for a lat/long entry on the AdHoc navlog entry points
    
    TempName = "Temp"
    
    Call openConnection
    
    cn.Open strConnection
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM tblLocation WHERE left([Location Name],4)='" & TempName & "'", cn, adOpenKeyset, adLockOptimistic
    
    rs.Delete
    
    Call closeConnection
    
    '''get the maximum number of records in tblCrew
    ''Call openConnection
    ''
    ''strSql = "SELECT Count(*) FROM tblLocation';"
    ''cn.Open strConnection
    ''Set rs = cn.Execute(strSql)
    ''nRecords = rs.Fields(0)
    ''
    ''Call closeConnection
    ''
    '''get all Surnames
    ''Call openConnection
    ''
    ''strSql = "SELECT Type FROM tblLocation ORDER BY Type;"
    ''cn.Open strConnection
    ''Set rs = cn.Execute(strSql)
    ''
    ''rs.MoveFirst
    ''Do While Not rs.EOF
    ''
    '''    If rs![Location Name] Like "Temp*" Then
    '''        rs.Open "SELECT * FROM tblLocation WHERE left([Location Name],4)='" & TempName & "'", cn, adOpenKeyset, adLockOptimistic
    '''        rs.Delete
    '''    End If
    ''
    ''    rs.MoveNext
    ''    i = i + 1
    ''Loop
    ''
    ''Call closeConnection
                      
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont normally 'loop thru' records....you run a delete query.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with ranman256 --normal approach would be a DELETE query.

    Often do a SELECT query first to confirm the records to be deleted
    Code:
    SELECT locationName 
    FROM tblLocations
    where left(locationName,4) = "Temp";
    Then , if you have verified the records to be deleted, use a Delete query
    Code:
    DELETE *
    FROM tblLocations
    where left(locationName,4) = "Temp";

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I'll add my voice to using a DELETE query.
    Looping through a recordset and deleting one record at a time will work but will be very slow especially for large recordsets.
    Using a delete query will delete all selected records 'at once' and therefore be much faster.

    As already suggested, its a good idea to check the data before deletion using a SELECT query
    Last edited by isladogs; 01-14-2020 at 01:04 PM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you are doing this in vba and don't want to use the (good) advice of using a delete query, try starting at the last record and moving backwards (i.e.movelast/moveprevious). The reason is that when you delete a record, the pointer will move to the next record - and then you movenext again with your code - effectively skipping the record after the one you've just deleted.

  6. #6
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21
    Thanks for the responses. Yes I am a novice at using Access tables and associated code and was trying to do it similar to how I would in excel. Note that this procedure is called from within an excel workbook, do I just place this code between the OpenConnection and CloseConnection subs or is there more to it?

  7. #7
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21
    I have had a play with it and come up with this:

    Code:
    Sub DelTemps()
    
    Dim i As Long
    Dim TempName As String
    
    '## Deletes the temporary waypoints place in the database for a lat/long entry on the AdHoc navlog entry points
    
    TempName = "Temp"
    
    Call openConnection
    
    cn.Open strConnection
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM tblLocation WHERE left([Location Name],4) ='" & TempName & "'", cn, adOpenKeyset, adLockOptimistic
    
    If rs.EOF = True Then GoTo NoRecords
    
    'rs.Open "DELETE * FROM tblLocation WHERE left([Location Name],4) ='" & TempName & "'", cn, adOpenKeyset, adLockOptimistic
    
    rs.Delete
    
    NoRecords:
    Call closeConnection
             
    End Sub
    It is still only deleting one record at a time however I need it to delete all records that start with Temp. Any help would be appreciated.

    Marcus

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to delete records where [Location Name] value begins with "Temp"?

    cn.Execute "DELETE * FROM tblLocation WHERE [Location Name] LIKE 'Temp*'"

    No need to open a recordset.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I just want to comment on your code. Not about HOW to delete records, but about the commands.

    You used the single line syntax of the IF() function;nothing wrong with that. But the only time you should use the GOTO command is in error handling code. (IMHO)

    So your original code has this snippet of code:
    Code:
    If rs.EOF = True Then GoTo NoRecords
    
    'rs.Open "DELETE * FROM tblLocation WHERE left([Location Name],4) ='" & TempName & "'", cn, adOpenKeyset, adLockOptimistic
    
    'rs.Delete
    
    NoRecords:
    Call closeConnection
             
    End Sub

    Here are 3 other ways of writing the same code:
    1)
    Code:
    If rs.EOF = False Then 
       'rs.Open "DELETE * FROM tblLocation WHERE left([Location Name],4) ='" & TempName & "'", cn, adOpenKeyset, adLockOptimistic
       'rs.Delete
    End If
    
    Call closeConnection
             
    End Sub
    2)
    Code:
    If Not rs.EOF Then
       'rs.Open "DELETE * FROM tblLocation WHERE left([Location Name],4) ='" & TempName & "'", cn, adOpenKeyset, adLockOptimistic
       'rs.Delete
    End If
    
    Call closeConnection
             
    End Sub
    3)
    Code:
    If rs.EOF Then
       'do nothing
    Else
       'rs.Open "DELETE * FROM tblLocation WHERE left([Location Name],4) ='" & TempName & "'", cn, adOpenKeyset, adLockOptimistic
       rs.Delete
    End If
    
    Call closeConnection
             
    End Sub

    My 2 cents.....

  10. #10
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21
    Thanks for all the replies! This appears to be working:

    Code:
    Sub DelTemps()
    
    Dim i As Long
    Dim TempName As String
    
    '## Deletes the temporary waypoints place in the database for a lat/long entry on the AdHoc navlog entry points
    
    TempName = "Temp"
    
    Call openConnection
    
    cn.Open strConnection
    cn.Execute "DELETE * FROM tblLocation WHERE left([Location Name],4) ='" & TempName & "'"
    
    cn.Close
    Set cn = Nothing
    
    End Sub
    Kind regards,
    Marcus

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Replies: 5
    Last Post: 03-11-2019, 02:35 PM
  2. Replies: 7
    Last Post: 04-07-2017, 02:08 AM
  3. Replies: 22
    Last Post: 11-26-2014, 01:51 PM
  4. Replies: 6
    Last Post: 10-07-2014, 03:02 PM
  5. Replies: 11
    Last Post: 12-14-2010, 01:25 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