Visual Basic Coding to Delete Tables

[PJname]

Can someone provide me with a sample of Visual Basic coding where I can set up to delete backup tables going back from today's date ( now()) to less than or equal to a date say two weeks prior to today's date?

the naming convention for the backup tables that I create:
table name

Master_tbl_#1 06/22/04 16:18


backup tables will always begin with the Master_tbl_#1 with the date/time that it was created which will be different.

it should capture the date portion ONLY included in the table name above and if anything is <= to this date, delete...


Any suggestions will be greatly appreciated.

 

[kjv1611]

For the deletion part:

DoCmd.DeleteObject(acTable, "tblTableName")

Of course, you'll need something with at least one loop and conditional statement, and a couple variables where you can compare with the system date/time (environ variables).

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PJname]

Tried to use the following code, but to no avail.....


Function DELETEOLD()

'-----------------------------------------------------------------
' This subroutine takes today's date and subtracts 5 weeks from
' it and then uses the "olddate" to delete tables from the database.
'-----------------------------------------------------------------

Date1 = DateAdd("ww", -5, Now)
olddate = Format(Date1, "mm/dd/yy")

On Error Resume Next

'-----------------------------------------------------------------
' Deletes tables from the database
' Table naming convention: Master_tbl_#1 06/22/04 16:18
'-----------------------------------------------------------------


DoCmd.DeleteObject A_TABLE, "Master_tbl_#1 " & olddate

End Function



I am missing something. I know it has something to do with the time at the end of the table name, but I am not sure how to code this.

 

[kjv1611]

Have you tried changing the Delete line to this:

DoCmd.DeleteObject(acTable, "Master_tbl_#1" & olddate)

?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PJname]

Yes, maybe I am not running correctly.

I have the module created, I created a macro where I am using the RUNCODE - DELETEOLD ()

I try running the macro but nothing happens.

I have 2 backup tables named:

Master_tbl_#1 06/22/04 07:49
Master_tbl_#1 06/22/04 16:18

which if codes is working, I would think would delete these 2 tables, but it isn't.

Any suggestions?

 

[Zion7]

Your formatting may be incorrect. Try this..

Dim Date1, olddate As String
Date1 = DateAdd("ww", -5, Now)
olddate = Format(Date1, "mm/dd/yy h:m")
Debug.Print olddate

Good Luck!

 

[RoyVidar]

Problems with these attempts, are that you'll very seldom find a table having the exact date/time as Now()-N days. The tables are named not only with date, but also with time, and it would need an excact match of the name to delete. I think you'll need to loop the table names, check the dates and then delete. Here's something that might work:

[tt]public sub deleteoldtables(byval lngNumDays as long)
dim tbf as dao.tabledef
dim db as dao.database
set db=currentdb
for each tbf in db.tabledefs
if (isdate(mid$(tbf.name,13,8))) then
if (datediff("d",datevalue(mid$(tbf.name,13,8)),now)> _
lngNumDays) then
db.tabledefs.delete (tbf.name)
end if
end if
set tdf=nothing
set db=nothing
end if[/tt]

Now - I don't use DAO normally, which this relies upon, so there might be things to look at (adding some errorhandling?). It would need a reference to Microsoft 3.# Object Library (in VBE - Tools | References), and I made this a sub (wich can be called from for instance a button click event, passing the number of days to delete: call deleteoldtables(10), which should delete all tables starting from 10 days, going back). TEST THIS IN A BACKUP;-) - typed not tested...

Roy-Vidar

 

[kbutler6]

Is there a simple way to check for the existence of a table and if it exists, delete it?

 

[PHV]

Have a look in the Object Browser at the DAO.TableDef object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hilbertl]

You can also even query the MSysObjects system table, contains all the names of tables and other objects that currently exist in a database.

Hilbertl

 

[jaydeebe]

pass an sql string and use "drop table"

Jaydeebe BSc (hons)
MCSA 2k
MCSE 2k

 

[Zion7]

KButler6,
This code will allow you to run through all the tables in your DB.

I set it up, that you pass a certain table name as an argument, if found, run your delete code...

Sub TableFields(sTable As String)

Dim tbl As TableDef

For Each tbl In CurrentDb.TableDefs
Debug.Print tbl.Name
If tbl.Name = sTable Then
Debug.Print "Delete " & tbl.Name
'CurrentDb.TableDefs.Delete(sTable)
End If
Next

This is very similiar to Roy Vidar's code. (Basically Identical, in principal). Tweeking either one, should get you what you want.

I added the Debug statements, so you can see what's going on. & I commented out the delete action, until you're satisfied with the debug results.

As Roy explained, it may be hard to pinpoint a table, who's name, has a Now() expression in it.
It appears, you've opted to restrict the search, down to a single table.

Either way, Good Luck!

 

[PHV]

When browsing the TableDefs collection, you may consider the Like operator:
If .Name Like "Master_tbl_[#]1 " & olddate & " ##:##" Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244