ODBC example (Access)

Each data source has a different configuration and the following is an example (txt) of adding an ODBC user directory connector.

Access (via ODBC) for txt and csv files

Do the following:

1. Verify that the Microsoft Access Text Driver is installed.

2. Set up an ODBC source on the server. You need to store the data in two separate txt files, for example, in this location: %ProgramData%\Qlik\Sense\Temp.

   Information noteThe temp folder is not included in the default installation. You need to create the temp folder, if not already done by another QMC administrator.

   Users.txt contains the users, and Attributes.txt the user attributes. 

   Example:  

   Users.txt contains:

   userid,name
   JoD,John Doe

   Attributes.txt contains:

   userid,type,value
   JoD,email,jod@gmail.com

    

3. Open the QMC: https://<QPS server name>/qmc

4. Select User directory connectors on the QMC start page or from the Start drop-down menu to display the overview. Create a new user directory connector: Access (via ODBC) and edit the properties.

    

   Identification

   All fields are mandatory and must not be empty.

   Identification properties

   Property	Description
   Name	The name of the UDC configuration, defined from the QMC.
   Type	The UDC type.

   User sync settings

   User sync property descriptions and values

   Property	Description	Default value
   Sync user data for existing users	When selected, only the existing users are synchronized. An existing user is a user who has logged in to Qlik Sense and/or been previously synchronized from the configured directory service. When not selected, all the users, defined by the properties for the UDC, are synchronized from the configured directory service. Information noteThe user attributes are only synced when a user logs in to the hub. Even if you delete the user in the QMC, the active session is still valid for the user that has been deleted. If the hub is only refreshed, the user is added to the database, but without any attributes.	Selected

   Connection

   Connection property descriptions and values

   Property	Description	Default value
   User directory name	The name of the user directory. Must be unique, otherwise the connector will not be configured. The name must not contain spaces.	-
   Users table name	The text file containing the users. Include the file extension in the table name, for example: File.txt/File.csv.	-
   Attributes table name	The text file containing the user attributes. Include the file extension in the table name, for example: File.txt/File.csv.	-
   Visible connection string	The visible part of the connection string that is used to connect to the data source. Specify one of the following: A full connection string, for example: Driver={Microsoft Access Text Driver (*.txt, *.csv)};Extensions=asc,csv,tab,txt;Dbq=C:\ProgramData\Qlik\Sense\Temp Information noteIn the default Visible connection string: Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Database.accdb, you must replace name of the driver with Driver={Microsoft Access Text Driver (*.txt, *.csv)};DBQ=C:\ProgramData\Qlik\Sense\Temp, to be able to use txt and csv files. Driver must point to a driver currently on the machine. In the ODBC Data Source Administrator, check which driver to specify. Search for "data source" to find the application. DBQ: Path to the folder where the txt files are stored. A pointer to an established System DSN, for example, dsn=MyDSN; Information noteThe two connection strings are concatenated into a single connection string when making the connection to the database.	-
   Encrypted connection string	The encrypted part of the connection string that is used to connect to the data source. Typically, this string contains user name and password. Information noteThe two connection strings are concatenated into a single connection string when making the connection to the database.	-
   Synchronization timeout (seconds)	The timeout for reading data from the data source.	240

   Example:  

   User table name: Users.txt

   Attributes table name: Attributes.txt

   Visible connections string:  Driver={Microsoft Access Text Driver (*.txt, *.csv)};Extensions=asc,csv,tab,txt;DBQ=C:\ProgramData\Qlik\Sense\Temp

5. Click Apply to apply your changes.

6. Go to the User directory connectors overview and check if the user directory is displayed as Configured and Operational.

   Information noteIf the User directory name is not unique the connector will not be configured. If not operational, check the repository system log in: %ProgramData%\Qlik\Sense\Log\Repository\Trace.

You have added an ODBC data source and initial synchronization will be performed within five minutes (by default).