Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    88

    Unanswered: How to Select All Records

    I'm trying to add a line of code to select all records and I can't find a DoCmd for this. I see there is a show all records command.

    I want to open a table, select all of the records and then delete all of the recors. I figured there would be a DoCmd that correlates to these same macro commands

    Thanks in advance for any help.

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    There are a couple of ways to tackle this. If the intention is to delete all records from the table then delete them in one go. This code will accomplish this.


    Private Sub DeleteWithSQL_Click()
    Dim strSQL As String

    strSQL = "DELETE tblTableName.* FROM tblTableName;"

    DoCmd.RunSQL strSQL

    End Sub


    Or you can delete the whole table.

    Private Sub DeleteWithSQL_Click()
    Dim tblTable As TableDef
    For Each tblTable In CurrentDb.TableDefs
    If tblTable.Name Like "*Colour" Then
    DoCmd.DeleteObject acTable, tblTable.Name

    End If
    Next

    End Sub


    This will delete any table with the word Colour in it.
    The choice is yours.

    David

  3. #3
    Join Date
    Oct 2002
    Posts
    88

    Select all records and delete

    Below is the code I want to put this in right after the docmd transfertext line. I'm getting bits of code here and there and don't really know VB. I tired just copying it there but it didn't work.

    Can you help?

    The code is below (I am importing into a temporary table, I the run a couple find unmatched append queries and then want to select all of the records

    Private Const mcsFileFilter As String = _
    "Excel Files (*.xls)|*.xls|" & _
    "All Files|*.*||"

    Private Sub cmdCommonDialog_DE_Click()
    txtImportFile_DE = CommonDialogClickGs(Me, mcsFileFilter)
    End Sub


    Private Sub cmdImport_Click()
    On Error GoTo Proc_Err

    Dim sStatusBarContents As String

    ' ---- Convert the excel files to CSV files and Import ----
    ' ---- Copy records into HMDA master table eliminates duplicates due to the primary key ----
    DoCmd.TransferText acImportDelim, "Lab Data Import Specifications", "tbl_temp", txtImportFile_DE, True
    DoCmd.OpenQuery ("temp without matching records in results qry")
    DoCmd.OpenQuery ("temp without matching records in samples qry")




    Proc_Exit:
    Exit Sub
    Proc_Err:
    MsgBox Err.Description
    Resume Proc_Exit
    Resume
    End Sub

    Private Sub Form_Load()

    End Sub

Posting Permissions

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