wayne Morgan...Need your help again.

[ZACH]

Hello,

I tried entering the code to set the log to delete info.
but Im not sure its working. I tried to change the Date to
TIME to run it at minutes instead of days to test. I think
I placed the TIME (and removed the "date") in two spots
and changed the "d" to "n" but when I run it, it skips the
delete. It just compacts and quits.
I also added the lines to delete queries and forms. but I
dont think its stated correctly. I dont know if it
matters but most of my tables have a relationship, and
when I attempted this in a macro i got the error that I
couldnt delete the table while the relationship existed.
Will this do the same??? I would like to delete the
queries and forms if at all possible.
Any Ideas on how to fix this and test?
thank you for your help.. I know im very close.

zach

Dim db As DAO.Database, strUser As String
Dim dteDate As Date
Set db = CurrentDb
strUser = CurrentUser
If strUser = "Admin" Then 'or whatever your login is
db.Execute "UPDATE Log SET Log.[Login]= "TIME"Date
();", dbFailOnError
End If
dteDate = DLookup("[Login]", "Log")
If DateDiff("n", dteDate, "TIME"Date) > 10 Then 'pick your
number of days here
db.Execute "Delete * From Activity Name"
db.QueryDefs.Delete Deleteqry
DoCmd.Delete, acform,"myform"
'Do this for each table you want to delete data from
End If
Set db = Nothing
Application.SetOption "Auto Compact", True
Application.Quit

 

[Wayne Morgan]

Ok, let's get started.

1) The delete queries will delete data from the tables, not delete the tables themselves.
You are correct, you can't delete them until the relationships are removed, but you can
delete the data. As previously mentioned, if you have referential integrity set up in
those relationships, you will have to delete the data from the many table before you
delete the data from the table on the one side of the relationship or set up cascade
deletes.

2) > db.Execute "UPDATE Log SET Log.[Login]= "TIME"Date

();", dbFailOnError
dteDate = DLookup("[Login]", "Log")
If DateDiff("n", dteDate, "TIME"Date) > 10

For this to work, you would have to enter the time in the table. I would recommend using
the current date and time which is returned by Now, but Time should work. You can't just
stick "TIME" in front of Date, you have to replace it.

Example:
Change If DateDiff("n", dteDate, Date) >10 to
If DateDiff("n", dteDate, Time) > 10

Do you really want to delete all the forms and everything? Once the data is gone, they
should basically be useless and not deleting them would make it easier for you to start
over.

--
Wayne Morgan
Microsoft Access MVP

Hello,

I tried entering the code to set the log to delete info.
but Im not sure its working. I tried to change the Date to
TIME to run it at minutes instead of days to test. I think
I placed the TIME (and removed the "date") in two spots
and changed the "d" to "n" but when I run it, it skips the
delete. It just compacts and quits.
I also added the lines to delete queries and forms. but I
dont think its stated correctly. I dont know if it
matters but most of my tables have a relationship, and
when I attempted this in a macro i got the error that I
couldnt delete the table while the relationship existed.
Will this do the same??? I would like to delete the
queries and forms if at all possible.
Any Ideas on how to fix this and test?
thank you for your help.. I know im very close.

zach

Dim db As DAO.Database, strUser As String
Dim dteDate As Date
Set db = CurrentDb
strUser = CurrentUser
If strUser = "Admin" Then 'or whatever your login is
db.Execute "UPDATE Log SET Log.[Login]= "TIME"Date
();", dbFailOnError
End If
dteDate = DLookup("[Login]", "Log")
If DateDiff("n", dteDate, "TIME"Date) > 10 Then 'pick your
number of days here
db.Execute "Delete * From Activity Name"
db.QueryDefs.Delete Deleteqry
DoCmd.Delete, acform,"myform"
'Do this for each table you want to delete data from
End If
Set db = Nothing
Application.SetOption "Auto Compact", True
Application.Quit

 

[Zach]

Ok, I redid the code to show you what i originally did,
and I forgot to say I changed the DATE/Time in the Log
table to accept time, medium time.
that worked, and it updated the time when Id open the form
where the code was.
I have a Main table with several linked tables (for drop
down lists) and I have 2 tables that are one to manys,
both have referential integrity with cascade update and
delete set up. (there is a delete record button on the
main form that deletes all info assoc with that record now)
So I would have to tell it to delete the secondary tables
with no refer integ, first, then the 2 with REF INTEG,
Then the main...is that right?
Is the query delete stated right...they are the guts of
the database and the boss wants them gone too.
The forms need to be deleted due to company logos so we
dont have someone using our stuff. Like I said the boss
wants it. Do I have the syntax right on the DoCMD???
when I run it nothing deletes. Ive tried deleting the data
then rewrote it to delete the table, doesnt work.
the code below is what it looks like now.

Thanks again
Zach

Ok, let's get started.

1) The delete queries will delete data from the tables,

not delete the tables themselves.

You are correct, you can't delete them until the

relationships are removed, but you can

delete the data. As previously mentioned, if you have

referential integrity set up in

those relationships, you will have to delete the data from the many table before you
delete the data from the table on the one side of the relationship or set up cascade
deletes.

2) > db.Execute "UPDATE Log SET Log.[Login] = "TIME"Date

();", dbFailOnError
dteDate = DLookup("[Login]", "Log")
If DateDiff("n", dteDate, "TIME"Date) > 10

For this to work, you would have to enter the time in the table. I would recommend using
the current date and time which is returned by Now, but

Time should work. You can't just

stick "TIME" in front of Date, you have to replace it.

Example:
Change If DateDiff("n", dteDate, Date) >10 to
If DateDiff("n", dteDate, Time) > 10

Do you really want to delete all the forms and

everything? Once the data is gone, they

should basically be useless and not deleting them would

make it easier for you to start

over.

--
Wayne Morgan
Microsoft Access MVP

Dim db As DAO.Database, strUser As String
Dim dteDate As Date
Set db = CurrentDb
strUser = CurrentUser
If strUser = "Admin" Then 'or whatever your login is
db.Execute "UPDATE Log SET Log.[Login]= Time
();", dbFailOnError
End If
dteDate = DLookup("[Login]", "Log")
If DateDiff("n", dteDate, Time) > 3 Then 'pick your
number of days here
db.Execute "Delete * From Activity Name"
db.QueryDefs.Delete Deleteqry
DoCmd.Delete, acform,"myform"
'Do this for each table you want to delete data from
End If
Set db = Nothing
Application.SetOption "Auto Compact", True
Application.Quit

.

 

[zach]

Dim db As DAO.Database, strUser As String
Dim dteDate As Date
Set db = CurrentDb
strUser = CurrentUser
If strUser = "Admin" Then 'or whatever your login is
db.Execute "UPDATE Log SET Log.[Login]= Time();", dbFailOnError
End If
dteDate = DLookup("[Login]", "Log")
If DateDiff("n", dteDate, Time) > 3 Then 'pick your
number of days here
db.Execute "Delete * From Formname"
db.QueryDefs.Delete queryname
DoCmd.Deleteobject acform,"myform"
'Do this for each table you want to delete data from
End If
Set db = Nothing
Application.SetOption "Auto Compact", True
Application.Quit

Ok I think I might see whats going on...just a
thought,like I said im just learning to read VB.
If im reading it right, ONOPEN the code looks to see if
Admin is the current user, then it updates the Table LOG
to the current time/date.
then it ends if...not sure
Now it looks to the table and looks up the field LOG IN
and THEN it compares (I dont see where but it should say
the current date/time) to the date/time entered,In this
case 3.
IF the log is greater than 3 more than the current,
Then it deletes the table, query...
Is that the correct flow of the code???? if it is then the
time is UPDATE before it is compared to the >3, so it will
never be over the time....Is that right? If that is right
then should the code read: compare the date to the log
then if >3, delete, If <3 then UPDATE??? or does it run in
a different order? Just a thought.
Let me know what you think.

Thanks
Zach

 

[Wayne Morgan]

To delete the data from the tables you have 2 choices. First, you can delete from the many
side then from the one side of the link. Second, you can set up cascade deletes and delete
from the one side and the cascade will delete from the many side for you. This does not
delete the table, just the data in it.

This will delete all relationships, tables, forms, queries, and reports except for the
open form that called the code.

On Error Resume Next
Dim dbs As Object, i As Long, db As DAO.Database
Set dbs = Application.CurrentProject
Set db = CurrentDb
If dbs.AllForms.Count >= 1 Then
For i = dbs.AllForms.Count - 1 To 0 Step -1
DoCmd.DeleteObject acForm, dbs.AllForms(i).Name
Next
End If
For i = dbs.AllReports.Count - 1 To 0 Step -1
DoCmd.DeleteObject acReport, dbs.AllReports(i).Name
Next
Set dbs = Application.CurrentData
For i = dbs.AllQueries.Count - 1 To 0 Step -1
DoCmd.DeleteObject acQuery, dbs.AllQueries(i).Name
Next
For i = db.Relations.Count - 1 To 0 Step -1
db.Relations.Delete db.Relations(i).Name
Next
For i = dbs.AllTables.Count - 1 To 0 Step -1
DoCmd.DeleteObject acTable, dbs.AllTables(i).Name
Next
Set db = Nothing
Set dbs = Nothing


--
Wayne Morgan
Microsoft Access MVP

Ok, I redid the code to show you what i originally did,
and I forgot to say I changed the DATE/Time in the Log
table to accept time, medium time.
that worked, and it updated the time when Id open the form
where the code was.
I have a Main table with several linked tables (for drop
down lists) and I have 2 tables that are one to manys,
both have referential integrity with cascade update and
delete set up. (there is a delete record button on the
main form that deletes all info assoc with that record now)
So I would have to tell it to delete the secondary tables
with no refer integ, first, then the 2 with REF INTEG,
Then the main...is that right?
Is the query delete stated right...they are the guts of
the database and the boss wants them gone too.
The forms need to be deleted due to company logos so we
dont have someone using our stuff. Like I said the boss
wants it. Do I have the syntax right on the DoCMD???
when I run it nothing deletes. Ive tried deleting the data
then rewrote it to delete the table, doesnt work.
the code below is what it looks like now.

Thanks again
Zach

Ok, let's get started.

1) The delete queries will delete data from the tables,

not delete the tables themselves.

You are correct, you can't delete them until the

relationships are removed, but you can

delete the data. As previously mentioned, if you have

referential integrity set up in

those relationships, you will have to delete the data from the many table before you
delete the data from the table on the one side of the relationship or set up cascade
deletes.

2) > db.Execute "UPDATE Log SET Log.[Login] = "TIME"Date

();", dbFailOnError
dteDate = DLookup("[Login]", "Log")
If DateDiff("n", dteDate, "TIME"Date) > 10

For this to work, you would have to enter the time in the table. I would recommend using
the current date and time which is returned by Now, but

Time should work. You can't just

stick "TIME" in front of Date, you have to replace it.

Example:
Change If DateDiff("n", dteDate, Date) >10 to
If DateDiff("n", dteDate, Time) > 10

Do you really want to delete all the forms and

everything? Once the data is gone, they

should basically be useless and not deleting them would

make it easier for you to start

over.

--
Wayne Morgan
Microsoft Access MVP

Dim db As DAO.Database, strUser As String
Dim dteDate As Date
Set db = CurrentDb
strUser = CurrentUser
If strUser = "Admin" Then 'or whatever your login is
db.Execute "UPDATE Log SET Log.[Login]= Time
();", dbFailOnError
End If
dteDate = DLookup("[Login]", "Log")
If DateDiff("n", dteDate, Time) > 3 Then 'pick your
number of days here
db.Execute "Delete * From Activity Name"
db.QueryDefs.Delete Deleteqry
DoCmd.Delete, acform,"myform"
'Do this for each table you want to delete data from
End If
Set db = Nothing
Application.SetOption "Auto Compact", True
Application.Quit

.

 

[Wayne Morgan]

You are on the right track. It updates the time first IF you are the one logged on. So, if
you log on, you are correct, it will never be over the time limit, only if someone else
logs on and you haven't recently.

--
Wayne Morgan
Microsoft Access MVP

Dim db As DAO.Database, strUser As String
Dim dteDate As Date
Set db = CurrentDb
strUser = CurrentUser
If strUser = "Admin" Then 'or whatever your login is
db.Execute "UPDATE Log SET Log.[Login]= Time();", dbFailOnError
End If
dteDate = DLookup("[Login]", "Log")
If DateDiff("n", dteDate, Time) > 3 Then 'pick your
number of days here
db.Execute "Delete * From Formname"
db.QueryDefs.Delete queryname
DoCmd.Deleteobject acform,"myform"
'Do this for each table you want to delete data from
End If
Set db = Nothing
Application.SetOption "Auto Compact", True
Application.Quit

Ok I think I might see whats going on...just a
thought,like I said im just learning to read VB.
If im reading it right, ONOPEN the code looks to see if
Admin is the current user, then it updates the Table LOG
to the current time/date.
then it ends if...not sure
Now it looks to the table and looks up the field LOG IN
and THEN it compares (I dont see where but it should say
the current date/time) to the date/time entered,In this
case 3.
IF the log is greater than 3 more than the current,
Then it deletes the table, query...
Is that the correct flow of the code???? if it is then the
time is UPDATE before it is compared to the >3, so it will
never be over the time....Is that right? If that is right
then should the code read: compare the date to the log
then if >3, delete, If <3 then UPDATE??? or does it run in
a different order? Just a thought.
Let me know what you think.

Thanks
Zach

 

[The answer is...]

Thanks for your help Wayne. Took me pretty much the whole
day of playing, but I think I have it worked out.
I also found out that the admin can log in anytime..even
after the date has elapsed and the log is updated, but if
someone else logs in after the date, it will delete the
tables,querys, forms....
Thanks for your help.. Slowly but surely Im learning.

Zach


Private Sub Form_Open(cancel As Integer)
Dim db As DAO.Database, strUser As String
Dim dteDate As Date
Set db = CurrentDb
strUser = CurrentUser 'sees whose Logged On
If strUser <> "admin" Then 'if its NOT the admin
dteDate = DLookup("[Login]", "Log") 'look at the log
for the date
If DateDiff("d", dteDate, date) > 2 Then 'if that date is
older than 2 days, in this case
db.TableDefs.Delete "Table" 'delete table
db.QueryDefs.Delete "Query" 'delete query
DoCmd.DeleteObject acForm, "Form" 'delete form
DoCmd.DeleteObject acReport, "Report" 'delete report
DoCmd.DeleteObject acMacro, "Macro" 'delete macro
End If
ElseIf strUser = "admin" Then ' or if the user IS the
admin
db.Execute "UPDATE Log SET Log.[Login]= date();",
dbFailOnError 'update the field with todays date
Set db = Nothing
Application.SetOption "Auto Compact", True

End If

End Sub