Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Question Unanswered: Delete all records from all tables

    Hi all,

    I have a piece of code which backs up all records from all tables into text format.... this works fine.

    I would subsequentally like to delete all records from all the tables within the access database, using the code so far i have,

    "Public Function Delete_datatables()

    On Error GoTo error_trap

    Dim table_name As Variant
    Dim table_names As TableDef
    Dim dbase_obj As Database

    Set dbase_obj = CurrentDb

    For Each table_names In dbase_obj.TableDefs

    table_name = table_names.Name

    <<<<<THIS IS WHERE THE CODE TO DELETE SHOULD GO!!!!!!>>>>>


    Next table_names

    perform_exit:

    Exit Function

    error_trap:

    If Err.Number = 2617 Then

    Resume perform_exit

    Else

    MsgBox Err & " " & Error$
    Resume perform_exit

    End If

    End Function"

    The code will loop through all the tables, but how can i get it to delete the data within each table... i can make it delete each table but not the table's content.

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    For Each table_names In dbase_obj.TableDefs

    table_name = table_names.Name

    'use this code

    docmd.runsql "DELETE * From " & table_name


    Next table_names
    KC

  3. #3
    Join Date
    Oct 2003
    Posts
    58

    Thankyou

    Originally posted by AZ KC
    For Each table_names In dbase_obj.TableDefs

    table_name = table_names.Name

    'use this code

    docmd.runsql "DELETE * From " & table_name


    Next table_names
    I have tried this but seem to get an error on the "From Clause"????

    jnr.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If you have spaces in your table names you will want to add:

    docmd.runsql "DELETE * From [" & table_name & "]"

    In general, you should add the square brackets when you are referencing table and field names.

  5. #5
    Join Date
    Oct 2003
    Posts
    58

    Wink Thankyou 2

    Sorry,

    It does work..... but it just started to delete the hidden system files from the DB,

    How can i stop this happening?

    jnr.

  6. #6
    Join Date
    Oct 2003
    Posts
    58

    Talking Sorted well kinda.....

    I added this,

    If table_name = "MSysAccessStorage" Or _
    table_name = "MSysACEs" Or _
    table_name = "MSysACEs" Or _
    table_name = "MSysObjects" Or _
    table_name = "MSysQueries" Or _
    table_name = "MSysAccessXML" Or _
    table_name = "MSysRelationships" Then

    Exit Function

    Else:

    DoCmd.SetWarnings False

    DoCmd.RunSQL "DELETE * From [" & table_name & "]"

    DoCmd.SetWarnings True

    End If

    Next table_names

    Thanks all,

    Jnr.

  7. #7
    Join Date
    Feb 2004
    Posts
    142

    Re: Thankyou 2

    Originally posted by 130213
    Sorry,

    It does work..... but it just started to delete the hidden system files from the DB,

    How can i stop this happening?

    jnr.
    if 0 <> instr(1,table_name,"Msys") then
    do delete
    endif

    this will prevent sys tables from being deleted
    KC

Posting Permissions

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