Transcript Microsoft Access - Dalhousie University

Microsoft
Access
Ervin Ha
Content







What is Microsoft Access
Why use Access
Limitations
Basic background information
Advanced features
Example
Conclusion
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
Why use RDMS

Facilitate information sharing


Reduce data redundancy


data can be queried from multiple tables
joined and displayed in a subset of records
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
Why use MS Access

Easy to use


Stand-alone


Help File, Message boards
Relatively cheap


Do not require any other program to run or use
Lots of support


GUI, Looks good
Comes with MS Office Suite
Integration

Can be integrated with other MS Office
applications
Limitations

It’s a Microsoft product


Overly user- friendly


Takes up lots of computer resources
Expensive


Tried too hard to predict what you what to do
Not suited for large databases


Bugs galore
Must buy from Microsoft. Not open source
Complicated to use for connectivity to other
types of databases
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.
Basics of Access

The smallest piece of information in a
database is called a field


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


customer name
customers
A relational database consists of a set of
related tables

Customers and Orders and Products
Basics of Access

Primary Key



Composite Key


A field used to uniquely identify each record
Every table in a database must have a primary
key defined
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.
Basics of Access

Queries


A subset of table information
Similar to filter or find operation within table


Also like a sort operation


searches for records according to specified criteria
and displays these records
can change the order of records
Differs from filter and sort in that a query can be
run against several tables
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
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.
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)
Advanced Features

Data Access Objects (DAO)




A data access interface
communicates with Microsoft Jet and ODBCcompliant 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
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"
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
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"
Example

Building from scratch



Staticfree Technology relationship diagrams
Student Courses database
Other examples


FLI (Forms, Data entry)
Bookstore (SQL queries, Reports)
Conclusion

Explained MS access


Basics you need to know


Keys, Tables, Queries, Forms, Reports
Advanced features


Relational Database Management System
Jet Engine, ODBC, DAO, ADO, OLE
Examples
Questions