Clear all table contents without deleting records

[ameedoo3000]

Hello everyone
I want to clear the contents of all the fields in a table without deleting the records
There is a code (SQL Update Field) but it updates each field separately and the table contains many fields
Is there a way to clear all the contents of the fields without deleting the records in an easy and simple way?
Note that this table has a relational relationship with another table and it is wrong to delete records from it

With many thanks

 

[dhookom]

So, you probably need to leave the key values. I'm having trouble wondering why you would want to do this, but you can set multiple fields to Null in a single update query.

UPDATE tblYourTable SET field2 = Null, field3 = Null,...

 

[jedraw]

What exactly are you really trying to achieve? Can you give us a before and after scenario?

I hope Duane has guessed well.

 

[ameedoo3000]

So, you probably need to leave the key values. I'm having trouble wondering why you would want to do this, but you can set multiple fields to Null in a single update query.

UPDATE tblYourTable SET field2 = Null, field3 = Null,...

Unfortunately, the method is very long, especially since there are many fields, perhaps more than 100 fields.

 

[ameedoo3000]

What exactly are you really trying to achieve? Can you give us a before and after scenario?

I hope Duane has guessed well.

As I said before, I want to empty all data from the fields without deleting the records and keeping the number of records in the table as it is because it is linked to another data table.

 

[dhookom]

100 fields raises even more question about your application. However, you can use code to create and run your SQL statement;

Public Function DeleteFields(strTable As String, strKey As String)
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim fd As DAO.Field
    Dim strUpdateSQL As String
    
    Set db = CurrentDb
    Set td = db.TableDefs("[" & strTable & "]")
    strUpdateSQL = "UPDATE [" & strTable & "] SET "
    For Each fd In td.Fields
        If fd.Name <> strKey Then    'primary key field
            strUpdateSQL = strUpdateSQL & " [" & fd.Name & "] = Null, "
        End If
    Next
    strUpdateSQL = Left(strUpdateSQL, Len(strUpdateSQL) - 2)
    Debug.Print strUpdateSQL
    'db.Execute strUpdateSQL, dbFailOnError
End Function

 

[dhookom]

The above query will fail if any field is required or has some referential integrity with another table.

 

[ameedoo3000]

100 fields raises even more question about your application. However, you can use code to create and run your SQL statement;

Public Function DeleteFields(strTable As String, strKey As String)
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim fd As DAO.Field
    Dim strUpdateSQL As String
   
    Set db = CurrentDb
    Set td = db.TableDefs("[" & strTable & "]")
    strUpdateSQL = "UPDATE [" & strTable & "] SET "
    For Each fd In td.Fields
        If fd.Name <> strKey Then    'primary key field
            strUpdateSQL = strUpdateSQL & " [" & fd.Name & "] = Null, "
        End If
    Next
    strUpdateSQL = Left(strUpdateSQL, Len(strUpdateSQL) - 2)
    Debug.Print strUpdateSQL
    'db.Execute strUpdateSQL, dbFailOnError
End Function

Thank you very much
I would like to execute this code in the button click event
How is this
Table name "Students"
First field "ID"

 

[dhookom]

Copy the function into a new, blank module and save the module with the name mod queries. Add a button to a form and set its On Click Property to:

=DeleteFields("Students", "ID")

I would test this in a backup copy of your database prior to your live application.

 

[ameedoo3000]

Copy the function into a new, blank module and save the module with the name mod queries. Add a button to a form and set its On Click Property to:

=DeleteFields("Students", "ID")

I would test this in a backup copy of your database prior to your live application.

iam sorry
there was an error

 

[dhookom]

I suggested you simply set the Click property, not the VBA. If you want to use VBA try:

Call DeleteFields("Students", "ID")

 

[ameedoo3000]

I suggested you simply set the Click property, not the VBA. If you want to use VBA try:

Call DeleteFields("Students", "ID")

Unfortunately, nothing happened or changed.

 

[Andrzejek]

Could you state here why do you want to 'clear' your (more than 100 fields) table?

BTW - did you un-comment the last line of your code:
Debug.Print strUpdateSQL
'db.Execute strUpdateSQL, dbFailOnError

 

[ameedoo3000]

Could you state here why do you want to 'clear' your (more than 100 fields) table?

BTW - did you un-comment the last line of your code:
Debug.Print strUpdateSQL
'db.Execute strUpdateSQL, dbFailOnError

no
But I decided to cancel it now... and the code is working very well
Thank you very much... my problem is solved
Thank you

Answer the question Why do I want to clear the contents of a table that may contain 100 fields: I work in a school "Recording students' exam results in the academic year" and when the academic year ends and we welcome the new academic year, I clear all the old student results to be ready for the new results

 

[dhookom]

I rarely delete records but if I did, I wouldn’t leave records with only the primary key. That makes no sense to me.

 

[ameedoo3000]

I rarely delete records but if I did, I wouldn’t leave records with only the primary key. That makes no sense to me.

Thank you very much sir
My problem has been solved perfectly
I wanted to know the same method for quiries but I will do it in a new topic so as not to burden you
Thank you very much sir again