delete table > 30 days

[EBee]

I am trying to find out how I can drop these tables in VBA that are 30 days old. But with a specific table name +dates+time.

ex. tblAllAddresses_05052005_152010
tblAllAddresses_04052005_182010

I have this code that renames tables and appends datatime to it.

DoCmd.Rename "tblAllAddresses_" & Format(Now, "mmddyyyy_hhmmss"), acTable, "tblAllAddresses"

'I also have an import table.
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"\\sbserver02\Circulation Transfer\Circ Admin Share\ThirdPartyData\PaidPlus.mdb", acTable, "tblAllAddresses", "tblAllAddresses"

 

[TheAceMan1]

How are ya EBee . . . . .

Can do, however . . . need to be sure if [blue]05052005[/blue] is [purple]Month,Day,Year[/purple] or [purple]Day,Month,Year[/purple] (can't guess here)?

See Ya! . . . . . .

 

[EBee]

DoCmd.Rename "tblAllAddresses_" & Format(Now, "mmddyyyy_hhmmss"), acTable, "tblAllAddresses"

format is mm dd yyyy.

thanks

 

[EBee]

this what I have so far, but does not work
sample data to delete

tblAllAddresses_04272005_152505
tblAllAddresses_03302005_091501

====================================================
Private Sub cmdImportData_Click()
Dim Response As Integer
Dim rst As ADODB.Recordset
Dim MyDate As Date
Dim j As Integer
Dim obj As AccessObject
Dim dbs As Object
Set dbs = Application.CurrentData
Dim tblarray(50) As String

. . . . some code here


For Each obj In dbs.AllTables

tblarray(j) = obj.Name
Next obj
For j = 1 To 50
If tblarray(j) Like "tblAllAddresses_*" Then

MyDate = Mid(tblarray(j), 16, 8)
If MyDate < (Now - 30) Then

DoCmd.RunSQL "Drop table tblarray(j)"

End If
Else
j = j + 1
End If
Next

 

[TheAceMan1]

OK EBee . . . . .

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

In a [blue]module[/blue] in the [blue]modules window[/blue], copy/paste the following routine:

[blue]Public Sub DelAllAddrTbl()
   Dim db As DAO.Database, tdf As DAO.TableDef
   Dim Ary, FileDate As Date, tgtDate
   
   Set db = CurrentDb()
   tgtDate = Int(Now()) - 30
   
   For Each tdf In db.TableDefs
      If Left(tdf.Name, 15) = "tblAllAddresses" Then
         Ary = Split(tdf.Name, "_")
         FileDate = CDate(Left(Ary(1), 2) & "/" & _
                          Mid(Ary(1), 3, 2) & "/" & _
                          Right(Ary(1), 4))
         
         If FileDate < tgtDate Then
            DoCmd.DeleteObject acTable, tdf.Name
         End If
      
      End If
   Next
         
End Sub[/blue]

Thats it! . . .

See Ya! . . . . . .

 

[EBee]

thanks. . that worked with a little change

Private Sub cmdImportData_Click()
Dim Response As Integer
Dim rst As ADODB.Recordset
Dim obj As AccessObject
Dim dbs As Object
Set dbs = Application.CurrentData
Dim Ary, FileDate As Date, tgtDate

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection


Response = MsgBox("Are you sure you want to import new data?", vbOKCancel, "WARNING!!")


If Response = vbOK Then


Set db = CurrentDb()
tgtDate = Int(Now()) - 30

For Each obj In dbs.AllTables
If Left(obj.Name, 16) = "tblAllAddresses_" Then
Ary = Split(obj.Name, "_")

FileDate = CDate(Left(Ary(1), 2) & "/" & _
Mid(Ary(1), 3, 2) & "/" & _
Right(Ary(1), 4))

If FileDate < tgtDate Then
DoCmd.DeleteObject acTable, obj.Name
End If

End If
Next