Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Database systems ms access

MS Access – Database Management System by Microsoft

MS Access or Microsoft Access is Database Management System (DBMS) by Microsoft. In Microsoft Access, Jet Database Engine is merged with the Graphical User Interface (GUI) and Software development tools. You can create customizable database applications using MS Access with many built-in functions like string, numeric, date, and information functions. MS Access 2016 is a part of the MS Office suite. Here we will learn the basics of MS Access and its designing and implementation in context as an accounting system. Let us get started.

Suggested V >

MS Access – What is Microsoft Access?

As we have learned before a Database Management System is a software that helps its users store and effectively manage databases. It is a systematic system/software to create, store, manage, manipulate, retrieves and update any kind of data.

One of the main applications of DBMS is that it can also be used to manage an accounting system. And MS Access is one of the popular DBMS tools that is used for the same. As Microsoft’s primary database program MS Access has many applications in the economic world.

In accounting it may be used for reviewing accounts, tracking down invoices, keeping a check on the creditors of the company, reviewing final accounts at year end etc. MS Access is a very effective tool for financial management and even tax management.

Components of MS Access

There are seven major components of Microsoft Access. When we create a component in Access it is an object, and several similar objects grouped together will constitute a class. Let us take a look at the components.

Tables

In MS Access Tables store the data or the information that you feed the program. The data table can be created with the required field names, properties etc as desired by the user. If the table is not created correctly, the database may be slow in giving results or give wrong results entirely. So it is essential the table be created properly and carefully.

Relationships

Relationships are the connections that form between one or more table. So the relationships can be one-on-one, one-to-many and then of course many-to-many.

Queries

Queries are when the user or the programmer requests the database for information. The objects help create a SQL compatible query, which can help store data and retrieve information from the database. Queries can also sort and filter data as per the query statement.

Forms

This is an object class which allows the programmer/designer to create a user interface for MS Access. A form is defined by tables and queries.

Reports

Once all the data has been entered into the database, the user will want to view the information. For this he can run reports which will allow him to view the information, or summarize the information. The reports can be customized by the user. The source of the information in these reports are the tables and the queries.

Macros

Macros are tools on MS Access that allow you to automate tasks on your forms or reports. They are predefined actions that add functionality and efficiency to your database. They can perform a series of actions like open a table, run queries, create reports etc. The command will be performed each time the macro button is clicked on.

Modules

These are the foundations of the software that allows the programmer to create a set of predefined instructions called sub-routines. These modules can be used throughout the database, they can be used from anywhere in the database.

Solved Question for You

Q: Every component that is created using access is a subject and several such similar objects constitute a class. True or False?

Ans: MS Acces is a popular DBMS system to create, store and manage databases. Every component that is created using Access is an object and several such objects constitute a class. Access is functionally available with seven object classes.

Microsoft Access

Microsoft Access

Released

Developer

Latest release version

Licensing

Operating system

Platform

Website

Access can use data stored in Access/Jet, Microsoft SQL Server, Oracle, or any ODBC-compliant data container. Skilled software developers and data architects use it to develop application software. Relatively unskilled programmers and non-programmer «power users» can use it to build simple applications. It supports some object-oriented techniques but falls short of being a fully object-oriented development tool.

Access was also the name of a communications program from Microsoft, meant to compete with ProComm and other programs. This Access proved a failure and was dropped. Years later Microsoft reused the name for its database software.

History

Access 1.1 Manual

Access version 1.0 was released in November 1992.

Microsoft specified the minimum operating system for Version 2.0 as Microsoft Windows v3.0 with 4 MB of RAM. 6 MB RAM was recommended along with a minimum of 8 MB of available hard disk space (14 MB hard disk space recommended). The product was shipped on seven 1.44 MB diskettes. The manual shows a 1993 copyright date.

The software worked well with very large records sets but testing showed some circumstances caused data corruption. For example, file sizes over 700 MB were problematic. (Note that most hard disks were smaller than 700 MB at the time this was in wide use.) The Getting Started manual warns about a number of circumstances where obsolete device drivers or incorrect configurations can cause data loss.

Access’ initial codename was Cirrus. This was developed before Visual Basic and the forms engine was called Ruby. Bill Gates saw the prototypes and decided that the Basic language component should be co-developed as a separate expandable application. This project was called Thunder. The two projects were developed separately as the underlying forms engines were incompatible with each other; however, these were merged together again after VBA.

Access is used by small businesses, within departments of large corporations, and hobby programmers to create ad hoc customized desktop systems for handling the creation and manipulation of data. Access can be used as a database for basic web based applications hosted on Microsoft’s Internet Information Services and utilizing Microsoft Active Server Pages ASP. Most typical web applications should use tools like ASP/Microsoft SQL Server.

Some professional application developers use Access for rapid application development, especially for the creation of prototypes and standalone applications that serve as tools for on-the-road salesmen. Access does not scale well if data access is via a network, so applications that are used by more than a handful of people tend to rely on Client-Server based solutions. However, an Access «front end» (the forms, reports, queries and VB code) can be used against a host of database backends, including JET (file-based database engine, used in Access by default), Microsoft SQL Server, Oracle, and any other ODBC-compliant product.

Many developers who use Access use the Leszynski naming convention, though this is not universal; it is a programming convention, not a DBMS-enforced rule.[2] It is also made redundant by the fact Access categorises each object automatically and always shows the object type, by prefixing Table: or Query: before the object name when referencing a list of different database objects.

Читать еще:  Понятие базы данных access

MS Access should not be used over a wireless link as this can cause failure and data corruption.

Features

One of the benefits of Access from a programmer’s perspective is its relative compatibility with SQL (structured query language) —queries may be viewed and edited as SQL statements, and SQL statements can be used directly in Macros and VBA Modules to manipulate Access tables. In this case, «relatively compatible» means that SQL for Access contains many quirks, and as a result, it has been dubbed «Bill’s SQL» by industry insiders. Users may mix and use both VBA and «Macros» for programming forms and logic and offers object-oriented possibilities.

MSDE (Microsoft SQL Server Desktop Engine) 2000, a mini-version of MS SQL Server 2000, is included with the developer edition of Office XP and may be used with Access as an alternative to the Jet Database Engine.

Unlike a complete RDBMS, the Jet Engine lacks database triggers and stored procedures. Starting in MS Access 2000 (Jet 4.0), there is a syntax that allows creating queries with parameters, in a way that looks like creating stored procedures, but these procedures are limited to one statement per procedure. Microsoft Access does allow forms to contain code that is triggered as changes are made to the underlying table (as long as the modifications are done only with that form), and it is common to use pass-through queries and other techniques in Access to run stored procedures in RDBMSs that support these.

In ADP files (supported in MS Access 2000 and later), the database-related features are entirely different, because this type of file connects to a MSDE or Microsoft SQL Server, instead of using the Jet Engine. Thus, it supports the creation of nearly all objects in the underlying server (tables with constraints and triggers, views, stored procedures and UDF-s). However, only forms, reports, macros and modules are stored in the ADP file (the other objects are stored in the back-end database).

Development

Access allows relatively quick development because all database tables, queries, forms, and reports are stored in the database. For query development, Access utilizes the Query Design Grid, a graphical user interface that allows users to create queries without knowledge of the SQL programming language. In the Query Design Grid, users can «show» the source tables of the query and select the fields they want returned by clicking and dragging them into the grid. Joins can be created by clicking and dragging fields in tables to fields in other tables. Access allows users to view and manipulate the SQL code if desired.

The programming language available in Access is, as in other products of the Microsoft Office suite, Microsoft Visual Basic for Applications. Two database access libraries of COM components are provided: the legacy Data Access Objects (DAO), which was superseded for a time (but still accessible) by (ADO) ActiveX Data Objects however (DAO) has been reintroduced in the latest version, MS Access 2007.

MS Access can be applied to small projects but scales poorly to larger projects involving multiple concurrent users because it is a desktop application, not a true client-server database. When a Microsoft Access database is shared by multiple concurrent users, processing speed suffers. The effect is dramatic when there are more than a few users or if the processing demands of any of the users are high. Access includes an Upsizing Wizard that allows users to upsize their database to Microsoft SQL Server if they want to move to a true client-server database. It is recommended to use Access Data Projects for most situations.

Since all database queries, forms, and reports are stored in the database, and in keeping with the ideals of the relational model, there is no possibility of making a physically structured hierarchy with them.

One recommended technique is to migrate to SQL Server and utilize Access Data Projects. This allows stored procedures, views, and constraints — which are greatly superior to anything found in Jet. Additionally this full client-server design significantly reduces corruption, maintenance and many performance problems.

Access allows no relative paths when linking, so the development environment should have the same path as the production environment (though it is possible to write a «dynamic-linker» routine in VBA that can search out a certain back-end file by searching through the directory tree, if it can’t find it in the current path). This technique also allows the developer to divide the application among different files, so some structure is possible.

Versions

DateVersion nameVersion
number
Supported OSCorresponding office suite
1992Access 1.11Windows
1993Access 2.02WindowsOffice 4.3 Pro
1995Access for Windows 957Windows 95Office 95 Pro
1997Access 978Windows, othersOffice 97 Pro
1999Access 20009Windows, othersOffice 2000 Pro and Premium
2001Access 200210Windows, othersOffice XP
2003Access 200311Windows, othersMicrosoft Office System 2003
2007Microsoft Office Access 200712Windows, others2007 Microsoft Office system
2010Microsoft Access 201014Windows, others2010 Microsoft Office Suite

There is no Access 3.0 to 6.0 because the Windows 95 version was launched with Word 7. All of the Office 95 products have OLE 2 capabilities, and Access 7 shows that it was contemporary with Word 7.

System Properties Comparison Microsoft Access vs. MySQL

Please select another system to include it in the comparison.

Our visitors often compare Microsoft Access and MySQL with Microsoft SQL Server, Oracle and SQLite.

More information provided by the system vendor

We invite representatives of system vendors to contact us for updating and extending the system information,
and for displaying vendor-provided information such as key customers, competitive advantages and market metrics.

We invite representatives of vendors of related products to contact us for presenting information about their offerings here.

Editorial information provided by DB-Engines

Name

Microsoft Access X exclude from comparison

MySQL X exclude from comparison

Description

Microsoft Access combines a backend RDBMS (JET / ACE Engine) with a GUI frontend for data manipulation and queries. The Access frontend is often used for accessing other datasources (DBMS, Excel, etc.)

Widely used open source RDBMS

Primary database model

Relational DBMS

Relational DBMS Key/Value like access via memcached API

Secondary database models

Document store

DB-Engines Ranking measures the popularity of database management systems

Trend Chart
Score

121.92

Rank

#10

Overall

#7

Relational DBMS
Score

1268.35

Rank

#2

Overall

#2

Relational DBMS
Website

products.office.com/­en-us/­access

www.mysql.com

Technical documentation

msdn.microsoft.com/­en-us/­library/­office/­ff604965.aspx

dev.mysql.com/­doc

Developer

Microsoft

Oracle since 2010, originally MySQL AB, then Sun

Initial release

1992

1995

Current release

1902 (16.0.11328.20222), March 2019

8.0.19, 2020

License Commercial or Open Source

commercial Bundled with Microsoft Office

Open Source GPL version 2. Commercial licenses with extended functionallity are available

Cloud-based only Only available as a cloud service

no

no

DBaaS offerings (sponsored links) Database as a Service

Providers of DBaaS offerings, please contact us to be listed.

Azure Database for MySQL: A fully managed, scalable MySQL relational database with high availability and security built in at no extra cost

Implementation language

C++

C and C++

Server operating systems

Windows Not a real database server, but making use of DLLs

FreeBSD
Linux
OS X
Solaris
Windows

Data scheme

yes

yes

Typing predefined data types such as float or date

yes

yes

XML support Some form of processing data in XML format, e.g. support for XML data structures, and/or support for XPath, XQuery or XSLT.

yes

Secondary indexes

yes

yes

SQL Support of SQL

yes but not compliant to any SQL standard

yes with proprietary extensions

APIs and other access methods

ADO.NET
DAO
ODBC
OLE DB

ADO.NET
JDBC
ODBC
Proprietary native API

Supported programming languages

C
C#
C++
Delphi
Java (JDBC-ODBC)
VBA
Visual Basic.NET

Ada
C
C#
C++
D
Delphi
Eiffel
Erlang
Haskell
Java
JavaScript (Node.js)
Objective-C
OCaml
Perl
PHP
Python
Ruby
Scheme
Tcl

Server-s > Stored procedures

yes since Access 2010 using the ACE-engine

yes proprietary syntax

Triggers

yes since Access 2010 using the ACE-engine

yes

Partitioning methods Methods for storing different data on different nodes

none

horizontal partitioning, sharding with MySQL Cluster or MySQL Fabric

Replication methods Methods for redundantly storing data on multiple nodes

none

Master-master replication
Master-slave replication

MapReduce Offers an API for user-defined Map/Reduce methods

no

no

Consistency concepts Methods to ensure consistency in a distributed system

Immediate Consistency

Foreign keys Referential integrity

yes

yes not for MyISAM storage engine

Transaction concepts Support to ensure data integrity after non-atomic manipulations of data

AC > but no files for transaction logging

AC > not for MyISAM storage engine

Concurrency Support for concurrent manipulation of data

yes

yes table locks or row locks depending on storage engine

Durability Support for making data persistent

yes but no files for transaction logging

yes

In-memory capabilities Is there an option to define some or all structures to be held in-memory only.

yes

User concepts Access control

no a simple user-level security was built in till version Access 2003

Users with fine-grained authorization concept no user groups or roles

Related products and services

3rd parties

General SQL Parser: Instantly adding parsing, decoding, analysis and rewrite SQL processing capability to your products.
» more

ScaleGrid: Fully managed MySQL-as-a-Service.
» more

Azure Database for MySQL provides fully managed, enterprise-ready community MySQL database as a service
» more

SQLPro for MySQL: A simple but powerful MySQL database client available on iPhone and iPad as well as macOS.
» more

Dremio is like magic for MySQL accelerating your analytical queries up to 1,000x.
» more

Navicat for MySQL is the ideal solution for MySQL/MariaDB administration and development.
» more

CData: Connect to Big Data & NoSQL through standard Drivers.
» more

More resources

Microsoft Access

MySQL

DB-Engines blog posts

MySQL is the DBMS of the Year 2019
3 January 2020, Matthias Gelbmann, Paul Andlinger

Database systems ms access

Browse > Grade Lists

  • Grade 6
  • Grade 7
  • Grade 8
  • Grade 9
  • Grade 10
  • Grade 11
  • Grade 12
  • Extra References
  • other
  • Blogs
    • Interesting News
    • Education
    • Interesting Facts
    • Health
    • Miscellaneous
    • Bed Time Stories
    • लोक कथा
    • बाल कथा
    • Model Questions
    • Poem
    • Essay

    Exposures

    • Poem
    • Scientific Discoveries

    —>

    • Home
    • Grade 10
    • Computer Science
    • Database Management System — MS Access
    • Database Management System MS-Access

    Database Management System MS-Access

    Please scroll down to get to the study materials.

    • Note
    • Things to remember
    • Videos
    • Exercise
    • Quiz

    Please Wait.

    Database Management System is a software that manages data stored in the database. Example: MS-Access, FoxPro, Oracle, etc.

    Data

    Data are the facts or figures obtained from various sources.

    Information

    Information is the processed data.

    Database

    Database is the collection of data or related information.

    Advantage of DBMS

    • It reduces data redundancy.
    • It creates link with different user and programs.
    • Easy for adding, editing and removing of data.
    • It provides better interacting environment to the user.

    Disadvantages of DBMS

    • Costly and time consuming procedure
    • Qualified personnel to operate.
    • Additional hardware and software may be required.

    Access

    Microsoft Access is a database management system which is used to collect, store and manage information as well as run reports. It provides facility to store the data in structured was and ways to organize the data and retrieve them as required by user. It is a relational database management system. Data are stored in more than one table to avoid data redundancy.

    Field

    A field is a piece of information about an element which may be a person, student or employee.

    Record

    A collection of related field is called record.

    Different aspects of MS-Access

    1. Table:

    A table is a collection of records or group of records. A table contains a number of rows and columns.

    Importance of Table

    • To store the data about specific table.
    • It holds the main information.

    Way to create table

    2. Forms:

    A form is a database object which is designed to enter records and edit the existing records easily.

    Importance of forms

    • It is used to make modification or addition of the data directly into the table.
    • To view the table in user defined manner.

    Ways to create forms

    • Form view
    • Design view
    • Datasheet view

    3. Query:

    A query is simply a more formal way to sort and filter data stored in the database.

    Methods to create queries are :

    • Design view
    • Simple query wizard
    • Cross tab query wizard

    Importance of query

    • It displays the result of different tables.
    • It sorts and filters the data.

    Types of query

    • Update query:Update query allows the user to make global changes to a group of records in one or more tables
    • Select query: A select query is the most common category and is used for existing information from one or more tables in a database. The result of a select query are displayed in a datasheet for viewing or editing, or used as the basis for a form or report.

    Sorting

    Sorting means organization of database so that the information we need most is easily available in ascending/descending order.

    Filtering

    Filtering database means showing only those databases which meet certain criteria.

    4. Report:

    Report is a very important object of database. A report is an effective way of presenting your data in a printed format or an screen soft copy.

    Importance of report

    • You can view report on your screen before printing it.
    • It allows complete output of the processing database.

    Creating report

    DBMS Terminologies

    Data type

    Data type is the type which is used to declare variable names, variable types and sizes for various operations.

    Data redundancy

    Data redundancy means the repetition of data in a database.

    Computerized database

    The database which is run on computer is called computerized database.

    Non computerized database

    The database which is run on paper is called non computerized database.

    Cell

    A cell is an intersection of rows and columns.

    • Database Management System is a software that manages data stored in the database. Example: MS-Access, Fox Pro, Oracle, etc.
    • Microsoft Access is a database management system which is used to collect, store and manage information as well as run reports.
    • There are different object in database. They are Table, Form,Query,Report.
    • Data type is the type which is used to declare variable names, variable types and sizes for various operations. The various data types are Text , Number Memo, Auto Number Currency, Date/ Time, Yes/No, Look up Wizard.

    .

    Very Short Questions

    What do you mean by data and information?

    Any raw facts or figure that may not have significant meaning by itself is known as data. Processed data or organized collection of related data which has significant meaning is known as information.

    What is database? List any four databases.

    The collection of data/ information related to any subject or purpose is known as database.
    Any four databases are as follows:

    • The telephone directory
    • Mark ledger
    • Attendance register
    • Encyclopedia

    What is DBMS? List any four DBMS.

    Data base management system (DBMS) is a system or mechanism that manages the database.
    Any four DBMS are as follows:

    Mention the differences between database and DBMS.

    The differences between database and DBMS are as follows:

    SNDatabaseDBMS
    1Database is a collection of interrelated dataDatabase management system is a software which can be used to manage the data by storing it on to the data base and by retrieving it from the data base.
    2The database holds the records, fields, cells with data.The DBMS is the «tools» used to manipulate the data in the database.
    3The examples of database are dictionary, encyclopedia etc.The example of DBMS are MS Access, DBASE etc.

    List any four advantages of DBMS.

    Any four advantages of DBMS are as follows:

    • Mass volume of data can be updated easily and efficiently.
    • Record can be stored easily on the basis of key field.
    • It provides the data sharing facility.
    • It provides data security facility.

    What is a table? Why do you need to create multiple tables in MS Access?

    A table is a primary block of a database which stores all the data in rows and columns.
    We need to create multiple table on MS Access because tale is a container that helps to stores multiple data in the forms of rows and columns.

    Define field and record.

    A column of a table which is used to store particular category of data is known as field and all the related field in a row that gives information about person or things is known as record.

    What is field name? Write the rules for naming fields.

    Field name is the field grid pane that specifies the name of a field within a table.
    The rules for naming fields are as follows:

    • Filed name can be up to 64 characters.
    • Filed name can have letters, spaces or punctuation marks.
    • Filed name cannot have square bracket, period and exclamation sign.
    • Filed name can’t begin with space.
    • Field name must be unique in table.

    List the field data type along with their uses.

    The list of data type are as follows:

    Text
    The text filed data type stores text or combinations of text and numbers.

    Memo
    The memo field data type stores lengthy text or combination of text and numbers.

    Number
    The number field data type stores numeric data that are to be used in mathematical calculations.

    Byte
    The byte stores numbers from 0 to 225 without decimals.

    Integer
    The integer number data type stores numbers from -32768 to 32767 without decimals.

    Date/Time
    The date/time data field stores only date and time.

    Currency
    The currency field data type stores currency data such as cost.

    Auto Number
    The auto number field data generates the unique sequential number automatically whenever a new record is added on a table.

    Yes/No
    The yes/no field data type stores a logical data.

    OLE Object
    The OLE object field data type allows a user to embed or link other objects in a table.

    Hyperlink
    The hyperlink filed data type stores hyperlink address, web sites, database objects or other files.

    Lookup wizard
    It allows to choose a value from another table or from a list of values by using list box or combo box.

    Description
    The description of field describes the purpose of field.

    What is meant by sorting record?

    The arrangement of records in a table either in ascending or descending order based on field is known as sorting record.

    What is filtering record?

    The process of viewing required data of a table that satisfy the specified criteria is known as filtering record.

    What is a query? List any four uses of queries.

    A query is simply a more formal way to sort and filter data stored in the database.
    Any four uses of queries are as follows:

    • To analyze data of a table.
    • To create new table with records from one or more tables.
    • To update or delete large volume of records at the same time.
    • To perform mathematical calculations on selected groups of records.

    Why is a form? List any two uses of form.

    A form is a database object which is designed to enter records and edit the existing records easily.
    Any two uses of form are as follows:

    • To enter data in a table or multiple linked tables.
    • To view record at a time.

    What is a report? Write the importance of a report.

    Report is a very important object of database. It is an effective way of presenting your data in a printed format or a screen soft copy.
    The importance of report are as follows:

    • You can view report on your screen before printing it.
    • It allows complete output of the processing database.

    What is data type? List the different data types of Ms-Acess.

    A data type is a characteristic of a field that determines the kind of data that can be stored in the field. Each field can store data relating of only a single data type.

    Ссылка на основную публикацию
    Adblock
    detector