Making SQL statements work

[davis2533]

I have some databases that are Access files. These files are getting full and I am trying to create a program to remove some of the files not needed. The Problem that I have is the code, which I will post below, only deletes one table and not the others in the code. Am I missing something?

Dim xCon As New OleDb.OleDbConnection
        Dim xCom As New OleDb.OleDbCommand
        Dim xAdap As New OleDb.OleDbDataAdapter(xCom)
        Dim xComBuild As New OleDb.OleDbCommandBuilder(xAdap)
        Dim ConnnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Store2Office\Emp.mdb;Mode=ReadWrite;Persist Security Info=False"
 
 
        Dim emp As String = "Emp.mdb"
        Dim hqomfox As String = "hqomfox.mdb"
        Dim s2o As String = "s2o.mdb"
        Dim product As String = "product.mdb"
        Dim s2ohourly As String = "s2ohourly.mdb"
        Dim inventory As String = "inventory.mdb"
        Dim composer As String = "composer.mdb"
        Dim e2 As String = "e2.mdb"
 
        'EMP.MDB and tables
 
        xCom.CommandText = "Delete * from charges where s_date<#" & TextBox1.Text & "#"
        xCom.ExecuteNonQuery()
        xCom.CommandText = "Delete * from clockchanges where indate<#" & TextBox1.Text & "#"
        xCom.ExecuteNonQuery()
        xCom.CommandText = "Delete * from clockdata where indate<#" & TextBox1.Text & "#"
        xCom.ExecuteNonQuery()
        xCom.CommandText = "Delete * from mhj_schedule where s_date<#" & TextBox1.Text & "#"
        xCom.ExecuteNonQuery()
        xCom.CommandText = "Delete * from schedule where indate<#" & TextBox1.Text & "#"
        xCom.ExecuteNonQuery()
        xCom.Connection = xCon
        xCon.ConnectionString = ConnnectionString
 
        If xCon.State = ConnectionState.Closed Then
            xCon.Open()
        End If
        xCom.ExecuteNonQuery()
        xCon.Close()

[jmcilhinney]

You cannot execute a command if the connection is not open. You're calling ExecuteNonQuery five times before you open the connection, then you open the connection and call ExecuteNonQuery once. It's only that last one that will work. You have to open the connection first, then execute all your commands, then close the connection.

Also, don't use a TextBox for date entry. Use a DateTimePicker.

xCom.Connection = xCon
        xCon.ConnectionString = ConnnectionString
        xCon.Open()
 
        xCom.Parameters.AddWithValue("@date", Me.DateTimePicker1.Value.Date)
        xCom.CommandText = "DELETE FROM charges WHERE s_date < @date"
        xCom.ExecuteNonQuery()
        xCom.CommandText = "DELETE FROM clockchanges WHERE indate < @date"
        xCom.ExecuteNonQuery()
        xCom.CommandText = "DELETE FROM clockdata WHERE indate < @date"
        xCom.ExecuteNonQuery()
        xCom.CommandText = "DELETE FROM mhj_schedule WHERE s_date < @date"
        xCom.ExecuteNonQuery()
        xCom.CommandText = "DELETE FROM schedule WHERE indate < @date"
        xCom.ExecuteNonQuery()
 
        xCon.Close()

[davis2533]

Thanks I will try that.. and I just checked out the datetimepicker. Thanks I like that idea.