Download Transaction Processing Using Microsoft Access - Lecture Notes | CS 701 and more Study Guides, Projects, Research Principles of Database Management in PDF only on Docsity!
CS 701-Transaction Processing Using Microsoft Access
Purpose
The purpose of this micro seminar is to discuss the process of developing transaction processing using Microsoft Access.
Problem
The XYZ corporation needs a simple database system in which to track department, employee, and dependent history. To support the corporation’s business objectives, the user needs to be able to perform the following transactions:
- Add a department
- Delete a department
- Modify a department
- Add an employee
- Delete an employee
- Modify an employee
- Add a dependent
- Delete a dependent
- Modify a dependent
Database Schema
Department
DNUMBER DNAME DLOCATION DMGRSSN
Employee
SSN FNAME MI LNAME BDATE DNO SALARY SEX
Dependent
ESSN SSN FNAME MI LNAME BDATE BDATE
Create The Table
From the Access main screen, click on the Blank Database command button to request the creation of a new database.
Click on the Table command button to request the creation of a new table.
When the New Table Dialog appears you can choose either the Table Wizards command button or the Design View command button. I recommend the Table Wizards button if you are new to this process.
Create The Transactions
From the Access main screen, click on the Modules Tab and then the New command button to request the creation of a new ABC (Access Basic Code) module. This module will contain the transaction queries.
This will then bring you into the declarations section of the new module. This is where you declare variables visible within all functions in the Access Basic Module. Here is where we will declare variables for Workspace, Database, and Recordset instances.
And enters the name of the transaction to be created. For this example AddADepartment.
The user then needs to click the OK command button to open the function declaration area.
And then enters the code required to support the transaction.
/*****************************************************************************
Public Function cmdAdd_click(DNUMBER, DNAME, DLOCATION, DMGRSSN) As Integer
Set WS = DBEngine.Workspaces(0) Set DB = WS.Databases(0) Set RS = DB.OpenRecordset("Department", dbOpenDynaset)
On Error Resume Next RS.Close
Sqlinsert = " insert into Department " Sqlvalues1 = " values (" & DNUMBER & ",'" & DNAME Sqlvalues2 = "','" & DLOCATION & "','" & DMGRSSN & "')" Sqlcontent = Sqlinsert & Sqlvalues1 & Sqlvalues MsgBox (Sqlcontent) DB.Execute Sqlcontent WS.CommitTrans MsgBox ("Department Added!") End If End If Else WS.Rollback MsgBox ("Duplicated DNUMBER!")
End If
End Function
*****************************************************************************/
One way that this function can be executed is through the use of a macro that executes the function with a sequence of inPutBox$ builtin methods supplying the input variables. A macro is created similar to creating other object instances in Access by clicking on the Macro tab and then clicking the New command button.
The dialog which follows is the result of adding a department entry without already having a record for the department’s manager.
The access user is invited to review the code incorporated to recognize the unites of work described.
