|
Data Integration between Applications |
to Database Intro.... to Home Page
A Relationship is a link between two tables, where the same type of field exists in both tables.
|
|
Here we have 3 tables. The Supplier ID field in Suppliers table is linked to Supplier ID field in Products table. Category ID in Products table is linked to Category ID in Categories
table. Thus: Selecting one Supplier ID in the Suppliers table can give all the detail in the Products table. Selecting one Category ID in the Products table can give all the detail in the Category table.
|
The links are shown by the interconnecting lines with the linked o (oo) symbols indicating a one to many relationship.e g: One Supplier ID field gives many Product table fields. This is the most commonly used relationship.
Changing Relations
You can edit or delete relationships by using Tools, Relationships.
|
|
Click a link line (the link line will become bold after selection as shown in the diagram) To Delete it, press the Delete keyboard key. To edit (or delete) the link, Right Click it to get the drop-down
menu
|
|
To edit the link, change the table or query field together with the related corresponding field...or if you have the experience, change the Join Type from the One to Many default. |
|
What is a Macro ?: A macro is a set of one or more actions that perform a particular operation(s), such as opening a form or printing a report.
Macros can help you to automate common tasks. For example, you can run a macro that prints a report when a user clicks a command button.
How are they used ?:
A Macro can be used with a buttons in a form, or in response to an event that occurs during use of the database.
How do I make one ?:
In the Macros tab of the database Window, by clicking the New button
|
Click the down arrow at the right of the Action row. Select an action. E.g: MsgBox (gives a message on screen), or Open Form (Opens a form).....etc
|
|
|
|
Next decide what to do with the action: The Action Arguments window below
reflects the action selected above: Here the argument boxes are asking about:
|
|
|
|
In this example of an Open Form action:
|
|
|
|
Finally use View, Macro Names to bring in the Macro Name column and place a name into the row e.g: Doit |
|
|
When the action and arguments are completed, save the Macro.
How do I start the Macro ?:
In most cases the macro is run (evoked) when an events happens or when a button is clicked:
An Event
|
Example: Inside a Form when the field is entered (On Enter), when the user leaves the field (On Exit)....etc. These are event Properties of the field which can be set by using the Design view of the form: Select the Form in the Form tab, click Design button. Locate the field, right-click the right box (the first box is the Title box, the box to the right of this is the actual field box) Click Properties from the drop-down window:
Scroll down the properties list and choose the event (here: On Enter). Click the down arrow of this row and then from the drop-down list click the macro name e.g. Customers.Doit Now, when the user enters data and gets down to the this field (here, the City field of the Customers form) as soon as the user clicks into the cell, the message box appears:
|
There are many macro actions to use and many places from where they can be evoked.
Exercise:
- Make a macro as described in this help
- Draw a command button on a form.
Hint:
Open a form in Design view, select Command Button in the toolbox, draw the button, In the Command Button Wizard, select Miscellaneous, Run Macro, select the macro name from the list. Select Text for the button and type in a name for the button, finish the Command Button Wizard.
- Enter the macro name into the properties of the command button, as shown in this help.
A Module is a Visual Basic for Applications procedure or procedures which can be assigned with command buttons or an event(s) that happen during use of the database.
A good way to get an idea of aVBA module is to view the code created for you after you create a command button.
By using the Design button on a Form and then using the Command button to draw a button, the Command Button Wizard starts.
Next select Form Operations, Open Form, select tany form, use a Text name for the button, finish. You have now caused the system to write a VBA procedure, similar to the one shown under:
Note: my comments in green (don't affect
program)
Private Sub Customer_Open_Click()
'if error occurs e.g. form doesn't exist go to label below and Exit sub
On Error GoTo Err_Customer_Open_Click
Dim stDocName As String ' set up a string
variable with this name
Dim stLinkCriteria As String' set up a string
variable with this name
stDocName = "Customers" ' put a name
into the string variable
DoCmd.OpenForm stDocName, , , stLinkCriteria '
Open the form
Exit_Customer_Open_Click:
Exit Sub ' normal exit here
Err_Customer_Open_Click: ' exit procedure if error has occurred
MsgBox Err.Description
Resume Exit_Customer_Open_Click
End Sub
Check this out:
In the Database Window, select the Forms tab then select your form.
Click the Design button
In the Design view, double click the command button you had previously drawn, then click Properties to get the properties list
By 'On Click' you will see [Event Procedure]
Click into that row
Click the ... button and you will see your VBA procedure written for you and which will be evoked when the command button is clicked.
If you wished you could add the following code giving the user a
choice not to open the form
Dim DoCustGen As String ' NOTE: add this line
under the other Dim line
'add this block of code
DoCustGen = InputBox("Proceed with Customer Data Input, y/n",
"CONTINUE", "y", 1200, 1200)
If DoCustGen = "n" Or DoCustGen = "N" Then
Exit Sub
End If
There are hundreds of objects, properties and commands in VBA so this help cannot cover them - if you are really interested in using VBA, with a little patience and persistence, you will be able to get into VBA modular programming quite easily. See 'further examples'
Data Integration
between applications
The transference of data from/to Access, from different sources. E.g: Excel
First check out: File, Get External Data, Import
This can get data from other files, including data in Excel sheets.
|
|
Linking data enables you to read and in most cases update data in the external data source without importing. The external data source's format is not altered so that you can continue to use the file with the program that originally created it, but you can add, delete, or edit its data using Microsoft Access as well.
I'll leave it to you to check these out.
