Database

MIS 3053

Database Systems And Applications

Building the Front End In ACCESS 2007

Welcome to the Building the Front End in ACCESS 2007 lab!

In this lab, we will learn:

1. Build forms in MS Access to provide an interface for data entry into the tables in MYSQL that hold the data at the back end.

Let’s get started!

The process of creating a front end consists of 3 steps:

a) connect to the backend database (in MYSQL),

b) Create data screens to access each table

c) Add navigation buttons and exit buttons to different screens

In this lab we’ll cover all 3 aspects

STEP A: CONNECT TO BACKEND DATABASE We’ll look at connecting to a MYSQL server.

Connecting to a MYSQL Server

Create a holder database in Access2007 called FrontEnd.

This dummy database will have no tables in it, just links to our tables in the MYSQL database.

Select External Data then MORE( ODBC database. This is shown below.

Select Link to the database source, then OK, then select machine source.

Select New. Hit OK to the warning message about not having admin privileges, if it comes up. Select User Data Source, then next then MYSQL driver.

Hit Next then Finish.

Now we need to create the ODBC resource and test the connection.

The IP address of the MYSQL server is needed. If it is the same machine, then: localhost.

The user and password could vary. Ask the Instructor for the username and password.

Fill out the information in a manner similar to the screen shot below, in the connector screen and click Test to test the connection. In the screenshot below, the name of the MYSQL database is MIS3053. It has the mechanics, repairs and cars tables in it already. We should have created that in the back end lab if we are choosing to use MYSQL.

Great!! Now hit OK for the connection successful window and for the connector screen.

The Select Data source window should now have the ODBC resource listed. Select that and press OK, as shown below.

Once the 3 tables are shown, then select all, and press OK. This is shown below.

Great! Now all three tables should be linked into our Access FrontEnd database. This is shown in the screen shot below.

Once we have the connection to the backend, we are now ready for step b): to create forms for data entry.

STEP B: CREATING FORMS TO EDIT EACH TABLE’S DATA

Next, we need to set the lookup properties of the mech_num & car_num fields in the repairs table, so they will only allow values that already exist in the referenced tables, when we do data entry. In order to do this, we need to change the properties of the table repairs in the FrontEnd database. The screenshot below shows how to do it for the mech_num field in the repairs table. Look at each field in the screen shot below and make sure it is set similarly in our database. Especially note that Show only Row Source is set to Yes.

Similarly, set the properties of the car_num field in the repairs table, in the FrontEnd database. We do not have to make these changes to tables in the BackEnd database.

Great! Now let’s create data access forms.

Select the create tab, then forms, then the form tab. If the mechanics table was selected earlier, a mechanic form will be automatically created. Right click and save it.

Similarly create and save forms for the cars and repairs tables also.

This is what it should look like:

Select the repairs form by double clicking on it.

We should see dropdown fields for the mech_num and car_num fields.

Now try adding data to all 3 tables via the forms in the FrontEnd database.

Hint: Sometimes, we may have to close a form or table in order for it to sync with the BackEnd database.

Make sure the tables in the BackEnd database is being updated.

Great! We have finished creating data access screens.

Now let’s learn step c: how to add buttons that can open forms, close forms and exit the application. These are important to allow navigation between the different forms and to provide menus screens.

STEP C: ADD NAVIGATION BUTTONS AND EXIT BUTTONS

Make sure none of the data objects are selected, then CREATE(FORM DESIGN.

Drag buttons to the form from the controls palette on the top, as shown below.

Right click on the button, and select properties.

Change the name to Btn_MainMenuMechanics. We should use the item type designation, then form name then item name as the naming scheme. In this case, the item type is Btn (button) the form name will be MainMenu and the item name is Mechanics.

The properties window is shown below.

Now in the properties window, select the Event tab, then onClick, and then the 3 dots, so we get the choices as shown below.

Select the Macro Builder choice, then OK.

Build the macro as shown below. Note the choices we have to make at the bottom of the screen for the openForm event.

Now add the other close event to the macro as shown in the screen shot below, that will close the main menu and save the macro by rightclicking and selecting save.

Now close the form and open it by double clicking on it. Click the mechanics button and the mechanics form should open, and the MainMenu should close.

Now let’s add a navigate button on the mechanics form. Right click the form and select design view. Add a button and set its name and caption as shown below. Following our naming scheme, the name should be Btn_MechanicsNavigateMainMenu.

Now build the onClick macro for this Navigation-to-Main-Menu button as shown below.

Now let’s put in an exit button in our main menu form.

Add another button to the MainMenu form in design view. Call it Btn_MainMenuExit.

Right click on the button and make the fore color red. This shown below.

Now build the click event macro for this button.

The screen shot for this is below.

Rightclick, and save the macro, then save the form and try to run the MainMenu form.

Clicking on the exit button should kill the FrontEnd application.

Fun Lab Assignment:

Create navigation buttons for the Cars and repairs forms on the MainMenu, and also buttons to navigate back to the MainMenu from those 2 forms.

When done, please show our work to the Professor.

Congratulations, you have finished the lab where we learnt how to create a front end interface to the back end database server.