Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    38

    Unanswered: Restricting Access to DB on Certain Dates

    I have a requirement that some users should not be accessing the database on certain date in the month. This is too ensure there is a closing period observed for consistent reports. Issue is that there are number of users who access the database and it is only one dept we would like to bar access on a nominated date. This particular dept access their data entry form through a switchboard menu option which I have password protected (no security immplemented otherwise and don't want it). This dept uses a form to open their data entry form. Is there some way that by using a table of dates, I could restrict the acces. Following will be the table entries.

    Closed From This Date To Open on This Date
    26/11/2008 Wed 8/12/2008
    22/12/2008 Mon 5/01/2009
    27/01/2009 Tue 3/02/2009
    26/02/2009 Thu 9/03/2009
    26/03/2009 Thu 6/04/2009

    Alternatively, if I can figure out 20th working dayand the first Monday next month, I don't need to use the table.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    have a look here about working days

    http://www.mvps.org/access/datetime/date0012.htm

    my thinking would be add a feild to the User table then when the user login and that feild is true and the date is true then kick them out.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    May 2006
    Posts
    38
    Appreciate, If I could have a simple code based on working days requirements, along the followings lines, I will be happy.

    If Day(Date) > 25 And Date < #4/12/2008# Then
    MsgBox "Data Entry is not available during PNP cut off period starting 25th of each month to first Monday of the following month."
    Cancel = True
    End If

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it is easy enough to obtain the 25th of this month and the first monday of next month. one way:
    Code:
        dat25 = DateSerial(Year(Now), Month(Now), 25)           '25th
        datTmp = DateSerial(Year(Now), Month(Now) + 1, 1)       '1st of next mo
        '/// if is Monday, add 0 days
        '/// if is Tuesday add 6 days
        '/// if is Wednesday, add 5 days
        '/// if is Thursday, add 4 days
        '///    Weekday(1st, vbTuesday)
        '///        = 7:Mon, 6:Sun, 5:Sat, 4:Fri, 3:Thu, 2:Wed, 1:Tue
        '///    7 - Weekday(1st, vbTuesday)
        '///        = 0:Mon, 1:Sun, 2:Sat etc
        datMon = DateAdd("d", 7 - Weekday(datTmp, vbTuesday), datTmp)
    it seems unfortunate to kill the users for half the month just to run reports.
    have you considered simply copying the live data to another database and running the reports off the copy?

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    May 2006
    Posts
    38
    Thanks for your help. I just did not get the alert and did not realise that you had posted a reply. I have created a function of the code but I could not test it in the immidiate window. Should this code be like this?

    Public Function Freeze(datMon) As Date

    dat25 = DateSerial(Year(Now), Month(Now), 25) '25th
    datTmp = DateSerial(Year(Now), Month(Now) + 1, 1) '1st of next mo
    datMon = DateAdd("d", 7 - Weekday(datTmp, vbTuesday), datTmp)
    End Function

    The system I am using is being used for lot of other users. The close off period is not for running reports, rather some adjustments for month end.

  6. #6
    Join Date
    May 2006
    Posts
    38
    Sorry did manage to test it. It comes as 12:00:00 when i test it for 4 Dec 08.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    mmmm
    don't undertand your question - sorry

    EOM processing - bah!
    you have data
    you have some rules ( the stuff you do to your data )
    ??? what sort of machine are you running on that takes two weeks to do some calcs ???
    10 seconds is eternity, and i would aim for < 1 sec

    can you explain again what you are trying to do and what is unhappy?

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    May 2006
    Posts
    38
    I used your code to create a function as shown above. To test it,, in the immediate window, I entered today's date to see whether it figures out the Monday next month. But the answer is in hours.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    khwaja,

    izyrider is bringing up some good points regarding your need to do what you're requesting. If it's in order to make adjustments, it sounds like there's a better solution than your current method and need to lock the database for a day or more.
    Last edited by pkstormy; 12-04-08 at 22:08.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    May 2006
    Posts
    38
    Many thanks for your kind efforts.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •