Clear contents of field in access table

[YasserKhalil]

Hello everyone
I am trying to do specific task on access but I am very newbie at access stuff
Suppose I have a table named "MyTable" and there is a field named "MyField"
There are values in the field and I need to clear the contents of that field. I didn't find a feature in access that can do that directly
How can I do that using the VBA in access?

[HansV]

Do you want to clear the field in ALL records?

[YasserKhalil]

Yes in all records. Just as simply in excel, select the column then clear the contents (not delete the column)

[HansV]

This can be done using an update query. In VBA:

Code: Select all

    CurrentDb.Execute "UPDATE MyTable SET MyField = Null"

[YasserKhalil]

That's amazing. Can I do the same for the whole table or I should loop through fields and execute that part?

[HansV]

Do you want to keep the records but clear all fields? (What would be the purpose of that?)
Or do you want to delete all records from the table?

[YasserKhalil]

I am just try to learn some VBA in access. What I am seeking for is the clear the contents of the table without deleting..?

[HansV]

You could mention all fields in the code that I posted earlier:

Code: Select all

    CurrentDb.Execute "UPDATE MyTable SET MyField = Null, ThisField = Null, ThatField = Null, OtherField = Null"

[YasserKhalil]

That's great. But what if the fields are 100 columns? This will be too long to type all the fields headers

[HansV]

In the first place, this is a futile exercise - it is not a good idea to clear all fields in all records of a table. It has no practical use.
But here is a way to do it. It requires that you have a reference to the Microsoft Office n.0 Access database engine Object Library in Tools > References... (the value of n depends on your version of Access). I have added comments.

Code: Select all

Sub ClearTable(TableName As String)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    ' Get a reference to the current database
    Set dbs = CurrentDb
    ' Get a reference to the table
    Set tdf = dbs.TableDefs(TableName)
    ' The first part of the SQL string
    strSQL = "UPDATE [" & TableName & "] SET "
    ' Loop through the fields of the table
    For Each fld In tdf.Fields
        ' Add to the SQL string
        strSQL = strSQL & "[" & fld.Name & "]=Null,"
    Next fld
    ' Remove the last comma
    strSQL = Left(strSQL, Len(strSQL) - 1)
    ' For testing:
    MsgBox strSQL
    ' To clear the fields:
    dbs.Execute strSQL, dbFailOnError
End Sub

[YasserKhalil]

Thank you very much
I know Mr. Hans it is not practical. I consider it as reference of learning so as to know how to refer to objects and other stuff in Access VBA
Best and Kind Regards