Generate MS Access test data
This example describes how to create mass test data for the t_Persons table in an Access database.
The completed Definition is found in the file Examples\EN\AccessAdapter\ms-access-create-test-data.fhd an all examples are contained in the ZIP archive downloaded with FlowHeater. This example uses a Microsoft Access 2000 database called fh.mdb and this database is also in the ZIP archive. This database is compatible with MS Access versions 97, 2000, 2002, 2003 and 2008. In addition, the MS Access Adapter supports the database formats MDB, MDE, ACCDB and ACCDE.
Note: This example generates German address information, but can easily be adapted to other environments.
TestData and Access Adapter
Let us get started, open FlowHeater and create a new Definition using the menu option File->New. In the following popup window, on the READ side select the TestData Adapter and on the WRITE side the Access Adapter and close the popup by confirming OK.
Database properties
Open the WRITE Configurator for the Access Adapter and enter as database name the fh.mdb database. In addition, uncheck the checkbox "Update data", this option is not required here. Note: This will also ensure the Definition runs faster.
data types and field properties
Data types and field properties
Switch to the tab Fields / Data types, select the table "t_Persons" and then click the Load Schema button; the window should now appear as illustrated on the right. Close the popup by confirming OK.
Configure properties
Now open the READ Configurator for the TestData Adapter, switch to the tab "Fields / Data types" and click the COPY button "Copy fields from the WRITE Adapter". Then delete the ID, Title and First Name from the fields list.
Reason: The ID field is of type AutoNumber and the Microsoft Access database takes care of this itself. For the Title and First Name fields we use a little trick, we will generate the Title and First Name as list values in a single field.
Now insert a new field called "Title / First Name", see on the right. Now select all the fields, one after the other, and enter the following values
- Title / First Name: Select the Random Value Type as "List" and enter here any first names you like, prefixed by the appropriate title divided with a space.
e.g.
Mr Robert
Ms Manuela
... - Last Name: Again select the Random Value Type as "List" and enter here a variety of any surnames you like.
z.B.
Strong
Miller
Smith
... - Street: The same procedure as above, with any street names you care for.
- Postcode: Select for this the Random Value Type of "Number" and specify as numeric range 1000 – 99999.
- City: Select as Random Number Value "String" and enter the length as 10 - 50 characters. From the list of characters to choose from, remove the numbers at the beginning of the string and the space character, so that only letters are used.
- Date of Birth: Select as Random Number Value "DateTime" and specify the range 01/01/1950 - 12/31/2000.
- Remarks: Choose again the "String" type and enter a length range of 0 - 1000 characters.
Close the popup by confirming OK.
Now drag and drop two copies of the Split Heater on the Designer windows and connect their inputs to the field "Title / First Name" and now click on the first Split Heater and enter for Find a space character. In the second Split Heater again enter a space character for Find, but also select the Return Column as the value 1. Now connect the output of the first heater with the Title field and connect the second heater output to the First Name field on the Access Adapter.
The fields for Last Name and Street we can connect directly; we do not need to do any further transformations with these. However, as a refinement you could experiment by inserting a random street number into the Street field yourself.
For the Postcode field we use the Format Heater. Drag and drop this heater onto the Designer page and connect the input and output to the Postcode fields on both sides. Double click on the Format Heater, so that the properties popup opens. Enter here INT as Data Type, set the justification to Right, enter as padding character a zero (0) and finally enter the length as five (5). By so doing we instruct FlowHeater to format a five-character, right justified postcode and to pad any shorter codes with leading zeros.
There is one further thing we must do, click on the "Format" tab and specify that no thousand divider character is wanted in the numeric format options.
MS Access test data definition
Now drag and drop two copies of the SubString Heater, one ToUpper Heater, one ToLower Heater and one Append Heater onto the Designer page. Now try to figure out for yourself how to connect these heaters so that the town name always starts with an upper case letter and the rest of the town string consists of lower case letters.
Finally, we drag and drop two copies of the Clone Heater onto the Designer page. We only need these heaters so that the otherwise direct connections for Date of Birth and Remarks do not traverse the rest of our Definition details. In this way the Definition appears somewhat more attractive. Now all that is left is to ensure it all works. Start the Execute and Test popup window and run everything in test mode for the first time. When you are happy with the result, you just need to uncheck the "Test run" checkbox and run the Definition again; thereafter the test data will be available in the Access database table t_Persons.
Microsoft®, Windows®, Access® are registered trademarks of Microsoft Corporation
