Microsoft Access

1
Microsoft Access

• Ervin Ha

2
Content

• What is Microsoft Access
• Why use Access
• Limitations
• Basic background information
• Advanced features
• Example
• Conclusion

3
MS Access

• Microsoft Program
• Part of the Microsoft Office suite
• Used to create relational database management
  system (RDMS)
• consists of multiple tables, each storing
  specific information
• relates information from one table to another
• One-to-many relationships are the power behind a
  relational database
• Query the database to find information
• Enter information into forms
• Display results in reports

4
Why use RDMS

• Facilitate information sharing
• data can be queried from multiple tables
• joined and displayed in a subset of records
• Reduce data redundancy
• the same data is not stored over and over again
• Keep data accurate
• a change in data is made in one table but appears
  updated in all queries and reports

5
Why use MS Access

• Easy to use
• GUI, Looks good
• Stand-alone
• Do not require any other program to run or use
• Lots of support
• Help File, Message boards
• Relatively cheap
• Comes with MS Office Suite
• Integration
• Can be integrated with other MS Office
  applications

6
Limitations

• Its a Microsoft product
• Bugs galore
• Overly user- friendly
• Tried too hard to predict what you what to do
• Not suited for large databases
• Takes up lots of computer resources
• Expensive
• Must buy from Microsoft. Not open source
• Complicated to use for connectivity to other
  types of databases

7
Basics of Access

• The key window in Access is known as
• the database window.
• Seven objects can be launched from this window
• Tables
• Queries
• Forms
• Reports
• Pages
• Macros
• Modules
• These objects, in turn, become their own windows.

8
Basics of Access

• The smallest piece of information in a database
  is called a field
• customer name
• A group of related fields is called a record
• customer name, address, phone number
• A table is a collection of records about a
  particular subject
• customers
• A relational database consists of a set of
  related tables
• Customers and Orders and Products

9
Basics of Access

• Primary Key
• A field used to uniquely identify each record
• Every table in a database must have a primary key
  defined
• Composite Key
• More than one field used in combination to
  uniquely identify each record
• Foreign Key
• A field (in one table) that is related to a
  primary key in another table within the same
  database.

10
Basics of Access

• Queries
• A subset of table information
• Similar to filter or find operation within table
• searches for records according to specified
  criteria and displays these records
• Also like a sort operation
• can change the order of records
• Differs from filter and sort in that a query can
  be run against several tables

11
Basics of Access

• Forms
• Used to easily view, enter, and change data
  directly in a table
• retrieves the data from one or more tables, and
  displays it on the screen
• also display pictures and other objects
• can contain a button that prints, opens other
  objects, or otherwise automates tasks
• Reports
• Used to analyze your data or present it a certain
  way

12
Advanced Features

• Microsoft Jet database engine
• The part of the Access database system
• Retrieves and stores data in user and system
  databases.
• Thought of as a data manager upon which database
  systems, such as Access, are built.

13
Advanced Features

• Open Database Connectivity (ODBC)
• A standard method of sharing data between
  databases and programs.
• ODBC drivers use the standard Structured Query
  Language (SQL) to gain access to external data.
• Use to connect
• Microsoft SQL Server databases
• Microsoft FoxPro databases
• other programs that provide 32-bit drivers in
  compliance with ODBC Level 1 to access their data
  files (Oracle RDMS, Paradox, dBASE, Lotus)

14
Advanced Features

• Data Access Objects (DAO)
• A data access interface
• communicates with Microsoft Jet and
  ODBC-compliant data sources
• Used to connect to, retrieve, manipulate, and
  update data and the database structure.
• Enable you to manipulate the structure of your
  database and the data it contains from Visual
  Basic

15
Advanced Features

• The following code creates a new
• TableDef object and sets its Name
• property
• Dim dbs As DAO.Database
• Dim tdf As DAO.TableDef
• Set dbs CurrentDb
• Set tdf dbs.CreateTableDef
• tdf.Name "Contacts"

16
Advanced Features

• Connectivity
• OLE DB
• A component database architecture
• provides efficient network and Internet access to
  many types of data sources
• relational data, mail files, flat files, and
  spreadsheets.
• ActiveX Data Objects (ADO)
• manipulate the structure of your database and the
  data it contains from Visual Basic.
• Many ADO objects correspond to objects that you
  see in your database

17
Advanced Features

• The following code creates a new
• TableDef object and sets its Name
• property
• Dim tbl As New ADOX.Table
• Dim cnn As ADODB.Connection
• Set cnn CurrentProject.Connection
• tbl.Name "Contacts"

18
Example

• Building from scratch
• Staticfree Technology relationship diagrams
• Student Courses database
• Other examples
• FLI (Forms, Data entry)
• Bookstore (SQL queries, Reports)

19
Conclusion

• Explained MS access
• Relational Database Management System
• Basics you need to know
• Keys, Tables, Queries, Forms, Reports
• Advanced features
• Jet Engine, ODBC, DAO, ADO, OLE
• Examples

20
Questions