Clear Access Table Records using VBA from Excel

[raghuram.star]

I need your help to Clear/Delete the content in Access Table Records using VBA from Excel

I have a Access Table "StudentsRecords" as my Database with Excel 2010 being by front end application to feed in data.

To create a new record, I check the table is the student record is existing for the Department and for the given week (students records will be created seperate for each Department and Week). Which means A student will have only 1 Unique record for any given Week & Department combined. I know this is funny, but this has to be this way to track the students records over a period for analysis.

Access Table

StudentName	Department	UpdateWeek	CurrentProgress	Achievements
Criss	Management	26	A	B+
John	Finance	26	A	B++
Peter	Finance	26	A	B-
Criss	Management	27	A	C

<tbody>
</tbody>

I want to move next level, defining what is Recent Update (Latest Record) for the given Student and Department Combined by looking at the UpdateWeek Column

For example there are 3 records available for Student "Criss" in Department "Management" in all 3 records UpdateWeek 28 is the latest available record.

Access Table

StudentName	Department	UpdateWeek	CurrentProgress	Achievements	Latest
Criss	Management	26	A	B+
John	Finance	26	A	B++	Yes
Peter	Finance	26	A	B-	Yes
Criss	Management	27	A	C
Criss	Management	28	A	B+	Yes

<tbody>
</tbody>

I want to how to Fill the "Latest" Column with "Yes" in matching with the criteria and rest of the records should be Blank or "No".

Below the Excel VBA code I use to define if the student record is existing in Access Table with given Department and Week, and Create a record if not exist or update if available.

Function UpdateDataBase(iDepartment, WeekNum, Student)
    Dim cn As ADODB.Connection, rs As ADODB.Recordset
    Dim sProduct As String, sVariety As String, cPrice As Variant
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
        "Data Source=C:\Tool-BetaTest\Database21.accdb;"
    
        ' open a recordset
        Set rs = New ADODB.Recordset
        rs.Open "StudentsActivities", cn, adOpenKeyset, adLockOptimistic, adCmdTable
            
            rs.Filter = "Department='" & iDepartment & "' AND UpdateWeek='" & WeekNum & "' AND StudentName='" & Student & "'"
            If rs.EOF Then
                'Student record not available, create new
                rs.Filter = ""
                rs.AddNew
                'Proceed further
            Else
                'Student record Available, Update it
                'Proceed further
            End If
            rs.Update
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Function

 

[ranman256]

to clear a table, run a delete query... docmd.openquery "qdDelAllInTbl"
delete * from table

 

[raghuram.star]

Thanks for the reply, but I do not want to delete entire rows.

I want to delete data from only one selected Column where the Content in Another Column contain specified value

I tried below code but it did not worked.

Delete * from Table.[Column3] where Table.[Column1] is like "Col1DataLookup" and Table.[Column2] is like "Col2DataLookup"

Any Suggestions?