MS Access

Microsoft Access which is a database management system or abbreviated to (DBMS) from Microsoft that merges the relational Access Database Engine (ACE) with a graphical user interface and software development tools. Microsoft Access may be used to build application software by software developers, data architects, and power users. Visual Basic supports access for Applications (VBA). This object-based programming language can reference several objects, including the traditional DAO (Data Access Objects), ActiveX Data Objects, and other similar ActiveX components. Visual objects usually used in forms and reports expose their methods and attributes in the VBA programming environment, and VBA code modules may declare and invoke Windows operating system activities.MS Access’s main components are tables, Queries, Relationships, Macros, Forms, Reports, and Modules.

FUNCTIONS: Comprising tables, queries, forms, and reports, MS Access provides many built-in features. The numeric, string, and other date functions in MS Access are covered in this section.

STRING FUNCTIONS: Below is a list of the standard string functions:

Function

DESCRIPTION

Asc

The ASCII value for the specified character is returned.

Chr

The character for the provided ASCII number code is returned.

Concat with &

Combines two or more strings

Curtis

Returns the complete path for a given drive.

Format

Formats a value according to the format supplied.

Instr

The position of the first occurring string in another string is returned.

InstrRev

Returns the position of the first occurrence of a string in another, starting from the beginning of the string.

LCase

Lowers the case of a string.

Left

Several characters from a string (starting from left) are extracted.

Len

The length of a string is returned.

LTrim

Removes the first few spaces from a string.

Mid

Parses a string and extracts certain characters (starting at any position)

Replace

A substring within a string is replaced with another substring a defined number of times.

Right

Some characters are extracted from a string (starting from the right)

RTrim

Removes trailing spaces from a string.

Space

Space

Returns a string with the provided amount of space characters.

Split

This function divides a string into an array of substrings.

Str

A string representation of an integer is returned.

StrComp

Analyses strings.

StrReverse

It flips a string and gives the result.

StrConv

Generates a converted string.

Trim

Both leading as well as trailing spaces from a string are eliminated.

UCase

Changes a string to upper-case letters.

Numeric Functions: The following is an overview of standard numeric functions:

Function

DESCRIPTION

Abs

Returns a number’s absolute value.

Atn

The arctangent of an integer is returned

Avg

Returns the expression’s average value.

Cos

Returns the angle’s cosine.

Count

The number of records returned by a specific selected query is returned.

Exp

Returns e raised to a specified power of a number.

Format

Formats a numeric value according to the format specified.

Max

The highest value in a set of values is returned.

Min

The lowest value in a set of values is returned.

Randomize

Provides a seed to the random number generator (used by Rnd()).

Rnd

Generates a random number.

Int

The integer portion of a number is returned.

Round

A number to a given number of decimal places is rounded off.

Sgn

Determines the sign of a number.

Sqr

The square root of a number is found.

Sum

Computes the total of a set of values.

Val

Reads a string and returns the numbers it finds.

Date and Time Functions: The following is an overview of standard date and time functions:

Function

DESCRIPTION

Date

returns the current date and time on the system.

DateAdd

inserts a time/date interval to date and returns the result.

DateDiff

Calculates the time difference between two dates.

DatePart

Returns a portion of date (as an integer)

DateSerial

Returns a date based on the parts specified (year, month, and day values)

DateValue

Returns a position based on a string.

Day

Returns the month’s day for a given date.

Format

Formats a date value as per the format specified.

Hour/Minute/Second/Month/Year

Returns the hour/date/second/month/year portion of a time and or date.

Month

Name

Gives the name of the month based on a particular number.

TimeSerial

Reverts a time from the specified parts (hour, minute, and second value)

Conclusion

Microsoft Access makes it simple to convert data into answers and includes tools to assist even inexperienced users in getting up and running quickly. Every coin has two sides. But we have to evaluate and examine whether or not to use it based on the weightage of both the advantages and the disadvantages. Here, we see that the number of benefits of MS Access is far more than the drawbacks. Hence, it is appropriate to be used as a database management software.