Start SQL query (routing action)

Use this action to create a routing instruction that will start an SQL query. You can specify an SQL query to update or add new data in an external database.

To do so, in the DSN box, you enter an ADO string. For example: Provider=Microsoft.Jet.OLEDB.4.0;data source=C:QesMyDatabase.mdb;

If you are using a Microsoft Access database, you can set an ADO string as follows:

Provider= Microsoft.Jet.OLEDB.4.0;Data Source=c:Database1.mdb

If your Access database is protected by a password, then, you need to provide the password in the connection string, for example:

Provider= Microsoft.Jet.OLEDB.4.0;Data Source=c:Database1.mdb;Jet OLEDB:Database Password=askia;

Note: As the ODBC imports and Start SQL routing instruction are able to manage the ResPath, you can integrate an MDB file as a miscellaneous resource file into your QEX. This can, for example, allow you to manage a contact database for a CAPI Windows survey. In this situation, the connection string will be as follows:

Provider= Microsoft.Jet.OLEDB.4.0;Data Source=??ResPath??Database1.mdb;

In the query box, enter your SQL request. It should be structured in the following way:

UPDATE [table] SET [field]=value WHERE [condition];

or

INSERT INTO [table] [field] VALUES [data];

The different values that you can use in your SQL queries are:

To retrieve the text value of an item in a closed question or open question, you should use '??Shortcut??'. Examples:
- UPDATE Table1 SET Email='??MAIL??' WHERE PASS='??Pass??'
- INSERT INTO Table1 (ID, NAME, Email) VALUES (1, '??Name??', 'j@j.com')
To retrieve the response order of a closed question, you should use !!??Shortcut??!!. Examples:
- UPDATE Table1 SET ID1='??Id1??' WHERE DEP=!!??Dep??!!
- INSERT INTO Table1 (ID, DEP, Email) VALUES (1, !!??Dep??!!, 'j@j.com')
To retrieve a value from a numeric question, you should use ??Shortcut??. Examples:
- UPDATE Table1 SET NAME='??Name??' WHERE ID=??Id??
- INSERT INTO Table1 (ID, NAME, Email) VALUES (??Id??, '??Name??', 'j@j.com')

Note: When using SQL databases, you must obey certain conventions when naming databases, tables and fields. For more information, see SQL database conventions.

Create your own Knowledge Base