July 2010 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

It is generally uncommon to convert a secured database (implemented using Microsoft Access Security) into an unsecured one. However, this step may become necessary when you want to deploy or share a database in an environment that does not use security.

The first step in removing security from a database is to change the ownership of the database objects. By default, the User who creates the database is its Owner. The owner of an object has full access rights to it and can also assign permissions to other Users or Groups. Additionally, members of the Admins group possess these privileges.

Points to remember:

The User who attempts to convert the Database must have at least Read Permission to all Objects of the Database.
The Hidden Objects, if any, cannot be transferred into the target Database.

The Conversion Process.

The conversion process is simple and needs only a few steps.

Create a new Database.
Select File - ->Get External Data - ->Import.
Browse to the location of the Database you are trying to convert, and open it.
Select the Tables tab and click on the Select All Command Button to select all the Tables to import.
Repeat this method for all Queries, Forms, Reports, Macros, and Modules.

Right-click to open Large Image in New Tab/Window.
If your Database has Custom Menus and Toolbars, then click on Options.
Select the Menus and Toolbars option.
Click OK to import all Objects (other than hidden objects) into the new Database.

At this stage, the access privileges of all objects in the new database are reset to their defaults. By default, every user of the database is a member of the Users group, and therefore has full access rights to all objects. This includes permissions to Open/Run, Read Design, Modify Design, and Administer permissions.

However, if you intend to share this unsecured database over a network, a few additional changes are required to enable concurrent use. Without these adjustments, the database will be limited to single-user access, preventing more than one user from working with it concurrently.

Changes for Multi-User Environment.

Select Tools - ->Options - ->Advanced.
Select Shared under the Default Open Mode Options Group.
Select Edited Record under the Default Record Locking Options Group. Open Database using Record Level Locking Option is already in the selected state.

Right-click to open Large Image in New Tab/Window.
Click OK to close the Dialog Box.
Select Tools - -> Security - -> User and Group Permissions.
Select the Admin User Name under the User/Group Name List.

Why select Admin User Account? Because in an unsecured environment, it is a member of the Admins Group logged in silently when no password is set. MS Access will not prompt for User ID and Password.
Select the Database Object in the Object Type Control.
Unselect the Open Exclusive Option.
Right-click to open Large Image in New Tab/Window.
Click OK to close the Dialog Box.

Now, you have a new Database with no Security settings. The old database will remain without change.

Technorati Tags: Microsoft Security

Introduction

With MS Access, the Date/Time field type can store a date, a time, or both together. When you enter a date value (for example, 14/07/2010), Access actually stores it as a whole number: 40373. This number represents the count of days since 30/12/1899, where day 1 corresponds to 31/12/1899.

You can verify this by typing the expression Format(1, "dd/mm/yyyy") in the Immediate (Debug) window. To open it, press Alt+F11 to display the VBA editor and then Ctrl+G. Pressing Enter will display the result 31/12/1899.

Similarly, time values are stored as decimal fractions of a day. For example, midnight corresponds to 0.0, while 12:00 noon corresponds to 0.5. When combined, a date and time are represented as a single numeric value. Thus, 14/07/2010 at 12:00 noon is stored as 40373.5.

When calculating time differences across midnight, Access treats midnight as 24.00 rather than 0.00 to ensure accurate results for times before midnight.

Type ? Format(40373.5,"dd/mm/yyyy hh:nn:ss") and press the Enter Key.

Result: 14/07/2010 12:00:00

It is interesting to explore how 0.5 becomes 12:00:00 noon or how the System maintains Date and Time internally.

We know we have 24 Hours in a Day, or 24 x 60 = 1440 minutes in a Day, or 24 x 60 x 60 = 86400 Seconds in a Day.

Time Calculations

That is 1 Second = 1 Day/86400 Seconds = 0.000011574074074074 Day (we can take it rounded as 0.0000115741). The end value of 074 is infinite. Again, 1 second is = 1000 Milliseconds.

From midnight onward, the time value increases in increments of 0.0000115741, which corresponds to one second. At 23:59:59 (one second before midnight), the stored value is approximately 0.9999906659 (representing 86,399 seconds). After one more second, the day value increases by 1, so the timestamp becomes 40374.0, representing 15/07/2010 at 00:00:00.

Each second is further subdivided into milliseconds, which can be accessed using the built-in Timer function.

For example, type the following direct command in the Debug Window:

? Timer

You will get output something like the example given below, depending on the time you try this out.

Result in Seconds: 68473.81

The Value .81 part is the time in milliseconds, and 68473 is the number of seconds of the current time of the day.

If you want to see this value in the Current Time of the Day format, type the following expression in the Debug Window and press Enter Key:

? format(68473.81/86400,"hh:nn:ss")

? format(68473.81*0.0000115741,"hh:nn:ss")

The Value of 68473.81 Seconds is converted into its equal value in Days by multiplying it by 0.0000115741.

Result: 19:01:14

Using the Timer Function.

You can use the Timer() Function to build a delay loop in a Program. The code below slows down the action by 5 seconds in program execution.

Public Function myFunction()
.
.
.
t = Timer
Do While Timer < t + 5 

  DoEvents

Loop
.
.
.
End Function

In the sample program shown earlier, the action is delayed by five seconds before executing the next statement after the loop.

We can retrieve the current system date and time using the built-in Now() function, while the Date() Function returns only the current system date.

When designing a table, you can set the Default Value property of a Date/Time field to either Date() or Now(). This automatically inserts the current date or the current date and time stamp, respectively, whenever a new record is added.

Now that we understand the basics of how time values are represented internally, let’s look at some examples of normal time value conversions involving hours, minutes, and seconds.

Always use date and time values together when calculating time differences. If you are designing a table and performing time-based calculations, store both the date and time in a single Date/Time field, rather than in separate fields. This is especially important when the time period spans more than one day—for example, if work begins at 20:00 and ends at 04:00 the following day.

Now, consider a case where the values are stored separately:

Date: 25/10/2020
Time: 5 hours, 7 minutes, 15 seconds

How can these be combined and converted into the correct internal storage format that represents both the date and time together?

Date and Time Converting to store in the Date/Time Field.

The Date Number 25/10/2020 is 44129 is the internal value.

To cross-check whether the number is correct or not, type the following expression in the VBA Debug window and print the result:

? format(#25/10/2020#,"0")

Result: 44129

Now, all the time values (5 Hours, 7 Minutes, and 15 Seconds) we need to convert into seconds first, then add them all together and divide the result by 86400 or (24*60*60) to get the internal time format suitable to add to the date number so that date and time value stay together in the Date/Time Field.

Now, let us do that as follows:

d_date = #25/10/2020#

hrs = 5
min = 7
sec = 15

h_seconds = hrs * 60 * 60
m_seconds = min * 60
Total = h_seconds + m_seconds + sec
? Total
Result: 18435 'seconds

'Convert to Time Value
timVal = Total/86400
OR
timval = Total/(24*60*60)

? timval
Result: 0.213368055555556

'Add TimeValue to d_date
d_date = d_date + timval

'Print the value of d_date in Date/time format
? format(d_date,"dd/mm/yyyy hh:nn:ss")

Result: 25/10/2020 05:07:15

You may convert the Hours, Minutes, and Seconds into Time Value format in a single expression:

timval = (((hrs*3600)+(min*60)+sec)/86400)
d_date = d_date + timval
OR

d_date = d_date + (((hrs*3600)+(min*60)+sec)/86400)

Date/Time Values change to Date, Hours, Minutes, and Seconds.

How do we separate again into Date, Hours, Minutes, and Seconds, if we want them in that way again, from the Date/Time Values?

'The Date/Time Value
'we have the date+time in:
d_date = d_date + timval

'get date value separate
dt = int(d_date)

timval = d_date - dt

'get hours
hrs = int(timval*24)

'subtract hrs value from time value
timval = timval - ((hrs*3600)/86400)

'get Minutes
min = int(timval * (24*60))

'subtract Minutes from time value
timval = timval-(min*60/86400)

'get seconds
s = int(timval*86400+0.1)

The +0.1 added for the correction of the rounding Error of the actual value of

The Simple Recommended Method.

If you want to do it differently, here it is:

d = 1/86400 :'1 second value = in day value internaly
H = 5
M = 7
S = 15

t = ((H*3600)+(M*60)+S)/86400
? t
0.213368055555556

TotalSeconds = t/d

? TotalSeconds
 18435 

hr = int(TotalSeconds/3600)
? hr
5

bal = TotalSeconds-(hr*3600)
? bal
 435 

mi = int(bal/60)
? mi
 7 

 se = bal-(mi*60)
? s
 15 
? 
? format(t,"hh:nn:ss")

05:07:15

You can follow any method you feel comfortable working with, and I recommend the last one.