Ken said:
Since now I can create a backup table and delete the old backup, I have to
find a way to "Drop table LIKE Locationsbkup%".
I've found snippits of code on the internet, but nothing I can make work.
Is there a way I can "Select Table name Like.. " and use the result in
executing a DROP?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You can't use DROP on wild-carded object names. The object has to be
explicitly declared. You'll have to run a VBA routine like this:
*********** BE SURE TO BACK UP BEFORE DOING THIS *********************
Sub DropTables(strTables As String)
' This only works on databases that have the
' MSysObjects table (a hidden system table) - .mdb & .mde files.
' strTables should hold the wild-carded tables:
' Like: "Locationsbkup". Don't include the wild card character.
'
' It seems that we have to use the asterisk wild-card character
' instead of the percent wild-card character, even though
' the percent character works in a QueryDef. Strange!
Const SQL = "SELECT Name " & _
"FROM MSysObjects " & _
"WHERE Name Like '|1*' AND Type=1 AND Flags=0"
dim db as dao.database, rs as dao.recordset
dim strSQL as string
On Error GoTo err_
' set up the search SQL
strSQL = Replace(SQL,"|1", strTables)
' Open a recordset that has all the tables that match
' the wild-carded strTables.
set db = currentdb
set rs = db.openrecordset(strSQL)
do while not rs.eof
db.execute "DROP TABLE " & rs!Name , dbFailOnError
rs.movenext
loop
' clean up & leave
set rs = nothing
set db = nothing
exit sub
err_:
' put some error code here
End Sub
HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSkUUfYechKqOuFEgEQLp1QCg9/Ja/rzJjdiVN9CKSDBX3Ia696sAmwVI
bTiq2UzyU/E2ViVsqx2HvtOd
=/LQf
-----END PGP SIGNATURE-----
