Member Avatar for Sevyt

Is there any way to create a button which basically clears a entire table in your database?

I was hoping to have 2 buttons, 1 to empty a table 1 to fill the same table again.
The idea is to store a year long values, and at the end of the year delete all of those with a button.
The other button i want to use to fill the same table again.
Starting from 1st day of the year untill the last day of the year.
So add a record for each day.

Found some examples, but those did not apear to work in VB2010 anymore.

  • 3 Contributors
  • 6 Replies
  • 201 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

A short, practical addendum for that ties the existing replies together and gives working options you can apply now. Because you are using Visual Studio Data Sources with an OleDb-backed Access file, you can either remove rows at the DataSet/TableAdapter level or run commands directly against the .accdb/.mdb file. was right to point you toward executing SQL from VB; 's TRUNCATE idea is not supported by Jet/ACE, so use one of the other approaches below.

If you prefer to work with the generated DataSet/TableAdapter (the Data Sources route), mark rows as deleted and call Update so the framework issues the appropriate deletes:

' typed DataSet / TableAdapter approach
Dim ds As New MyDataSet()
Me.MyTableTableAdapter.Fill(ds.MyTable)

For i As Integer = ds.MyTable.Rows.Count - 1 To 0 Step -1
  ds.MyTable.Rows(i).Delete()
Next

Me.MyTableTableAdapter.Update(ds.MyTable)

If you want a direct, robust repopulate routine (one record per day), use a parameterized insert inside a transaction. This example inserts a row for each day of a target year; change table/field names and the connection string to match your file:

Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\MyDb.accdb;"
Using conn As New System.Data.OleDb.OleDbConnection(connString)
  conn.Open()
  Using tx As System.Data.OleDb.OleDbTransaction = conn.BeginTransaction()
    Using cmd As New System.Data.OleDb.OleDbCommand("INSERT INTO [MyTable] ([DateField]) VALUES (?)", conn, tx)
      cmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("?", System.Data.OleDb.OleDbType.Date))
      Dim yearNum As Integer = 2026
      Dim d As Date = New Date(yearNum, 1, 1)
      Dim endD As Date = New Date(yearNum, 12, 31)
      While d <= endD
        cmd.Parameters(0).Value = d
        cmd.ExecuteNonQuery()
        d = d.AddDays(1)
      End While
    End Using
    tx.Commit()
  End Using
End Using

Final tips: always back up the .accdb/.mdb first, wrap delete+insert in a transaction for safety, and test on a copy. Access AutoNumber values won't reliably restart just by deleting rows—recreate the table or compact/repair on a copy if you need IDs to start at 1. If the table has related child records, delete children first or handle relationships to avoid constraint errors.

Recommended Answers

All 6 Replies

Member Avatar for Begginnerdev

Do you have a method to connect to the access database?

If not, you will have to:

1) Connect to the database (Use this for your connection string)
2) Create a new command (Use this for a simple guide on connecting to the database.)
3) Execute the command. (Use the string below to delete.)

Try something like this:

Dim sqls As String = "DELETE FROM table"
Edited by Begginnerdev
Member Avatar for Sevyt

Yeah the database is shown on a form already.
The connection is done through Data sources.

Member Avatar for Begginnerdev

What are you using for a data source? ADO?

Member Avatar for Reverend Jim

A much faster way to delete all records is "TRUNCATE TABLE tablename"

commented: I didn't know this existed. Thanks Rev +5
Member Avatar for Sevyt

oledb

i thought Truncate table only worked for SQL DB's?

Member Avatar for Reverend Jim

My mistake. You are correct.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.