Digital Workshop

Q: How do I create a connection to a database and display records within my Opus Pro publication?

A: Please find below a step-by-step procedure for creating a small test database, then setting up a Database Query action and displaying the returned records:

1) Create an Access database with the fields:

NAME
AGE
OCCUPATION

All of these fields can be set as "Text" format

2) Save the table as Table1 and do not set up a primary key
3) Add a few rows of test data to your table then save and close Microsoft Access
4) Launch Opus Pro (or Opus Pro XE) and create a new publication
5) Right-click on the page and select Edit Actions
6) Select the Triggers tab
7) Select the Synchronisation group
8) Double-click on the On Show trigger to apply this to the page
9) Select the Actions tab
10) Select the Database group of actions then double-click the Database Query action to apply this. This should open a set of Database Query options
11) Click on the Select button next to the DSN drop-down menu
12) In the Select Data Source window, click New
13) Select Microsoft Access Driver (*.mdb) from the list of drivers and click Next >
14) You can name this connection what ever you choose. Make a note of the name given and click Next >
15) You will now be shown a summary of the changes made, just click OK to confirm
16) You will now need to specify which database to use. Click on Select and navigate to you database. Once selected, click OK
17) Ensure that the DSN you have just created is highlighted in the list and select OK
18) You will now return to the Database Query tab, but the DSN will be set to FILEDSN=<your_DSN_name>.dsn
19) Click on SQL Wizard, remove the tick from the Select Data for Updating/Deleting and click Next >
20) From the table drop-down list, select the table in which your required data resides, ensure that all of the field names are ticked, then click Next >
21) This page allows you to apply SQL operators to filter the data (i.e; to search the database for certain information). For the purposes of this example, simply click Next
22) This page allows you to sort the results by field. Leave the Field setting as (None) and click Finish
23) You will now return to the Database Query tab, but the Query name will read Query1
24) At the bottom of this window is a grid showing all of the variables assigned to your Field names. At present there are no variables assigned
25) Click on the box next to the Name field (which currently reads None) and an Insert Variable window will open. Click New and type in NAME, then click OK twice
26) Repeat Step 25 for your other Field names, calling their associated variables AGE and OCCUPATION respectively
27) Click Apply then OK to return to the main editor window
28) Add three text boxes to the page.
29) Left-click in the first text box, wait a moment, then left-click again to display the caret (flashing I-beam)
30) The Insert Variable icon (a capital A with a red asterisk in the top-left corner) on the toolbar should now be available. Click this and select the NAME variable
31) Repeat this for the other two text boxes, inserting the AGE and OCCUPATION variables
32) Preview the publication
33) The first record of the database should now be displayed in the three text boxes

If you wish to add previous and next record actions to navigate through the recordes, simply add two buttons to the page and apply a Previous Record action to the left-most button and a Next Record action to the other. Please ensure that you select the appropriate query from the drop-down menu before clicking the Apply button.