Transferring data between a Snap survey and a database

Step 1: Setting up the Database Linkage Wizard

1. Make sure that the database or spreadsheet you are importing from is closed.
2. Open the survey you wish to import the data into.
3. Click  to open the Data Entry window.
4. Select File | Database Link to display the Database Linkage dialog.

   

5. Click [New…] to create a new database link.
6. The Database Linkage Wizard dialog will be displayed.
7. Select the Import from Database radio button.

   

8. Click [Next>].
9. The next step in the wizard asks you to select a database file or build a database link. Usually you will be able to click on the [Select Database File] button. If the database type you need is not listed as one of the defaults in the Select Database File dialog box then you can use the [Build Data Link] button to create the connection.
   This worksheet describes how to build a link to an SQL database.
10. Click the [Build Data Link…] button.

    

Step 2: Creating the data link for the SQL database

1. The Data Link Properties dialog is displayed.You can use the [Help] button on the dialog to display Microsoft’s help on this dialog.
2. Select the appropriate data type. For a link to an SQL Server database, select Microsoft OLE DB Provider for SQL Server.

   

3. Click [Next>]. The Data Link Properties dialog moves to the Connection tab.
4. Select the SQL server on your network from the dropdown list.
5. Select the radio button for the appropriate security type for the server.
6. Select the SQL Server database file from the drop-down list, or type the name of the file.

   

7. Click [Test Connection] to confirm that you can connect to your database.
8. Click [OK]. You will be returned to the Database Linkage Wizard.

Step 3: Matching records between the survey and the database

1. Select the table or sheet in your database containing the data you want.

   

   Click [Next>].

2. Select how you will import data to the survey. You will need a unique id for each person in the database that can be matched with a survey respondent (for example, a customer id or email address). This is called the link variable.
   Join cases where the link variables match only imports data from the database if the id is already in the survey.
   Join matching cases and append unmatched cases imports all data from the database. If the id is already in the survey, the appropriate data is added to that case. If it’s not, a new case is created.
   Append unmatched cases creates new cases in the survey for the ids that are not already in the survey. It does not change the existing cases.
   Append all cases creates new cases in the survey for every id in the database. It does not check if the ids are already in the survey.
3. Select the field in the database that contains the unique id from the Database field dropdown list.

   

4. Select the variable in the survey that contains the unique id from the Use Snap variable dropdown list. (You can also use the Snap case number as the unique number for each case.)
5. Click [Next>].

Step 4: Choosing what database fields to import into your survey

When you have chosen the database, database table, and matching variable, you then choose which fields in the database to import. The Database Linkage Wizard dialog shows a list of fields in the database to import from and is shown on the left, with variables in the current survey on the right.

1. Map the variables between the database and the survey. Snap works out the mapping where possible. To change a mapping, select a row in the right hand column, and click  to display a list of available variables in the survey. Select a variable to load data into, or select a blank if you do not wish to import the field.

   

   If you wish to import a field into your survey that does not already have a variable set up in your survey, select <Create Variable> from the dropdown list. The Name and Label automatically display the name of the database field. You can edit them if you wish. Set the response type and length to appropriate values and click [OK].
   

   If you wish to import data into a multi-response variable, you may need to set up which codes in the database match to which variable codes in the survey. When you select the multi-response variable in the right hand column, a code mapping area appears. Select the appropriate code or define a new one as you can with variables.
   

2. Click [Next] to display a summary of what the link does.
3. Check the details are correct, and enter a Name for the database link.
4. Click [Finish]. Snap shows the Database Linkage dialog including the new link.
   

Setting up a Hot Link to run the link automatically

Making an import link into a hot link means that when you identify a new case by entering a unique value in the link variable, data from the external file is copied into the new case. This means that details such as name, address and email address could be loaded into the survey as soon as the case was identified.

If it is an export link, the data entered in the Snap survey is copied to the external file. If it is a new case, a new record is created. This allows you to keep a database up to date.

1. Select File | Database Link to open the Database Linkage dialog.
2. Select the database link you wish to use.

   

3. Click the [Hot Link…] button. The Hot Link dialog appears.
4. Check the Use As Hot Link box, then select the appropriate option.
   Automatic update: updates the matched variables of each case when the key link field is entered.
   Manual update: update the matched variables when you click the refresh button  on the Data Entry window toolbar.
   
5. Click [OK]. The Database Linkage dialog shows the amended link specified as a Hot Link.

   

6. The link will update the survey when the database changes. Click [Run] to update the survey immediately.