a Count Variable used for Scheduled Backups

[bostonfrog]

I created backup data queries on my db, but I'd like to have a variable that stores the number of times the db was accessed, and tell that user, as when using Quicken/Money for example, that data should be backed up. As long as the program is open, I can create a static variable, for example, that acts as an accumulator and adds 1 to a count, but then those variables are reset to 0 when you exit the app, so it's not helpful. Want the ability for them to exit the app and, when re-accessing it, told that it's time to backup data (for example, after the program was accessed 4 times or within a given date span). If I said update it every week, it would have to remember the last restore date. Any basic suggestions, skeleton code, or ideas?
-- Michel

 

[PaulBricker]

I haven't tried this, but it seems that you would have to store the value of your Count in a table. Have two fields, UserID and CountofLogins

Then when your user logs in you look at the field CountofLogins and either give them a message or update the value by 1.

HTH

Paul

 

[bostonfrog]

Thanks, Paul! Someone at another Access forum suggested basically the same thing, but how would I store the value in CountOfLogins so it's remembered each time after Access is exited?
Should I use an update query on this field that adds 1?
Also, might I need some kind of For ... Next loop that would perform this backup at intervals of 3, so back it up at when CountofLogins is 6, 9, 12, 15, etc.? Any thoughts?
-- Michel

 

[PaulBricker]

Depending on how you want to do it, here is one way. First create a table. The table, tblLogins, has two fields, UserID (Text) and CountOfLogins (Long Integer)
You would enter all your users in the table by there User name and give them a count of 0.
Then write a function

Function getLogins()
Dim myLogins As Integer
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "Select * from tblLogins Where tblLogins.UserID = '" & CurrentUser & "'"
Debug.Print strSQL
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If rst!CountofLogins = 0 Then
rst.Edit
rst!CountofLogins = 1
rst.Update
Exit Function
End If
If rst!CountofLogins Mod 3 = 0 Then
MsgBox "You will need to backup material during this session"
rst.Edit
rst!CountofLogins = rst!CountofLogins + 1
rst.Update
Else
rst.Edit
rst!CountofLogins = rst!CountofLogins + 1
rst.Update
End If
Set rst = Nothing
End Function

Then create a macro. In the Action column select RunCode and in the Function Name argument put getLogins()
Then name the macro AutoExec
With that name, the macro will run each time the db is opened and the CurrentUser will be evaluated and the number of sessions will either generate a message box or will just update the count in the table.
Let me know what you think. I'm sure there are other ways to do this.

Paul

 

[bostonfrog]

Thanks for your code snippet, Paul. I am going to revise it slightly since I don't use the CurrentUser and this database is not a secured one yet. I saved my user names in a public variable called gUserName.

I had so many issues implementing Access security that I ended up corrupting the default workgroup admin. file (.mdw), so that every new instance of Access required a password and user; I had created a brand new .mdw (and I did not call the new one system.mdw), but still Access associated every new instance of itself with the new .mdw, so that other users couldn't use the program. I will attempt to read the 36-page MS Access Security FAQs in details -- it's very tricky. The suggestion of using MOD (modulus/remainder) was very helpful. I had used it once in a homework assignment in VB 4 years ago! One question though --
Why did you use 2 If statements, rather than nesting it:

Elseif rst!CountofLogins Mod 3 = Then
   MsgBox "you will need ... "

.
Any tips on using the Workgroup Administrator would be most welcome.
-- Michel

 

[PaulBricker]

Well, if the value for the user was 0 then
0 Mod 3 = 0 evaluated as True so it ran the message box. So first I tested for a zero value and if it was True I set the CountofLogins to 1 and exited the function. If False it ran the other if statment. Just threw something together to have you look over. Let me know if you need anything else. I have implemented a few secure databases. I know it's a bear until you get used to it. The reason you were prompted for all instances of Access is because you didn't join something. I'll have to look it up to refresh my memory but that won't happen for a few hours. I'm out of the office for a bit and will check back in when I return.

Paul

 

[bostonfrog]

Thanks again, Paul.
I had actually "joined" my new .MDW via a shortcut but only to 1 specific database. Later, however, whenever anyone tried to run Access for any reason, it looked for that .MDW, even when trying to create a brand new database. I was forced to "rejoin" the default system.mdw so I wouldn't screw up Access for other users on that computer wanting to use the program for their own purposes.

Also, why not just reset the CountofLogins back to 0 instead of incrementing it by 1? That way when the user again reaches 3 logins, he will be told to backup his database.
Thanks and hope to hear from you here.
-- Michel

 

[PaulBricker]

Resetting the CountofLogins to 0 would work just fine. The only reason I didn't is because I, personally, would like to see how many times staff has logged into a certain Db. It just kind of killed two birds with one stone.

As far as the Security goes, take a look at these instructions. They are the ones that got me started. Look at step 11 and see if that helps with the login problem. Also, read the whole thing thru first, then read each step thru completely before you proceed with the first part. It got me thru my first secure db's.

http://www.jmhare.com/dbsecurity.htm

Paul

 

[bostonfrog]

Yes, I understand about your desire to see how many times a user logged in; it's not so important here, just as long as we know they logged in 3 or 4 times and need to backup the db. I will bookmark the link you sent and read it very, very carefully!! Thanks.