Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    45

    Unanswered: Deleting data from columns

    I have a table with 100 columns and 1000 records, and I need to delete the data from all of the columns except one.

    Does anyone know of a way to loop through a recordset and delete from all columns except one for all records?

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you need to delete the data or remive the columns

    if this is a one off then I'd do an sql query that updated the colums to null

    if you dont need the columns then just delete the columns in the table editor
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Make a Create table query to create a new table with the record you need.
    Delete the original table, then rename the created table with the name of the deleted one.

  4. #4
    Join Date
    Dec 2005
    Posts
    45
    Actually, I ran a maketable query to create a new table, then deleted all records. I then did an append query to add just the one field I needed:


    DoCmd.RunSQL "SELECT Table1.* INTO newtable FROM Table1"

    DoCmd.RunSQL "Delete * from newtable"

    DoCmd.RunSQL "INSERT INTO newtable ( [LOAN NO] )SELECT Table1.[LOAN NO] FROM Table1"


    btw, does anyone know how to make this work with fields with different data types? I get errors on 'rs.fields(ctr) = null':

    rs.open "Table1", currentproject.connection
    for ctr = 0 to rs.fields.count - 1
    If rs.fields(ctr).name = "LOAN NO" then
    else
    rs.movefirst
    do until rs.eof = true
    rs.fields(ctr) = null
    rs.update
    rs.movenext
    loop
    end if
    next ctr

Posting Permissions

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