Create an Answer Mapping Database Using SQL

For the following example you will need:

Part one: Set up the database

  1. Open SQL Server Management Studio
  2. At the Object Explorer, right click on Databases and select New Database....
  3. Type in a Database Name and click OK. Your database will now be added to the list.
  4. At the Object Explorer, expand your new database folder.
  5. Right click on Tables and select New Table….
  6. In the Column Name, add your first column name and set the Data Type required. Press enter to add more rows for each column name and type you require. For example your first column could be “Employee Number” so the data type could be “int” and your second column could be “Employee Name” so you could select the data type “char”.
  7. Click the Save icon in the tool bar to save this table. Type in a name for your table, e.g. “Employee Details”
  8. At the Object Explorer, expand the Tables folder and right click your new table, and click on Edit Top 200 Rows.
  9. Type in the data your want your users to choose from in the interview (the changes are saved automatically).

Part 2: Set up the connection between your database and Workspace

  1. Open ODBCAD32.exe.
  2. Go to the System DSN tab and click Add.
  3. Select SQL Server and click Finish.
  4. Type in a Name and Description for your ODBC data source.
  5. In the Server drop-down list, select the SQL instance which contains your answer mapping database e.g. “MACHINENAME\SQLEXPRESS”
  6. Click Next.
  7. Choose the option With SQL Server authentication using a login ID and password entered by the user.
  8. Type in the user name and password you use to access your SQL server.
  9. Click Next on all the steps after, there is no need to change anything else.
  10. Click OK.

You now need to create the rest of the Answer Mapping.