Image
Creating lookups and dropdowns from Access database
There are circumstances where you may want to load values to a dropdown list or use them in a lookup from an Access database. You can do this directly in the metadata field, document schema or manage imports configuration.
The remainder of this article assumes the reader has good knowledge of Windows administration concepts. FileHold Professional Services can provide assistance to configure your system. Support for this material is not covered under FileCare.
The feature needed to support this operation is called User Configured Database Server Connection. This allows you to tell the document management software to access the data with a method specified by the user. The document management software does not require any prior knowledge of this data access method, but it does require the user to have full knowledge of how to install and configure this method.
In this example we will create a document schema database lookup used in the metadata panel, but the principals here could apply equally to a drop down menu field or with manage imports. We will use the example Northwinds database.
In order to use this feature, you will need a license with Custom Providers and Queries enabled. Upon request, a new license file can be sent to you at no charge. To request a license with this feature enabled, please email [email protected].
Preparing the Access database
To start, we need to download the Northwinds database template from Microsoft. You can search for the Northwinds access database template on Google. The database file should be stored in a location accessible from the FileHold application server. The FileHold service user will need permission to the file and FileHold will need exclusive access to the file during configuration and synchronization.
Preparing the data provider
The metadata field configuration will expect us to provide a dot net compatible data provider. The data provider is the technology that knows how to read data from the data source. For this example the data source is an Access database file, so we will use the data provider included with Microsoft Access 2010.
If you do not have Microsoft Access 2010 installed on the document management software server you will need to download and install the Microsoft Access Database Engine 2010 Redistributable (link is external)from Microsoft. These drivers can read a number of database and file formats including Access, text, and Excel.
If you are interested to know what OLEDB providers are available in your system you can run the Data Link Properties dialog. Methods for starting this dialog vary depending on your operating system version. Perhaps the easiest method is to create an empty text file with the extension UDL. Simply double click on this file and this dialog will appear.
Setting up the lookup
- To start, go to the lookup tab on your document schema configuration and put a check in the Enable Schema Lookup box.
- Select the User Configured Database Server Connection radio button and select the .Net Framework Data Provider for OleDb data provider.
Image
- The connection string must be entered before the connection can be verified. This connection string is specific to each OLEDB data provider. Documentation will typically be provided with the vendor’s driver. For the Microsoft Access Database Engine 2010, with the Access database file located at C:\Projects\Northwinds\Northwinds.accdb the connection string should be specified as follows:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Projects\Northwinds\Northwinds.accdb;Persist Security Info=False;
A good source for information on the correct connection strings is available at www.connectionstrings.com.
- Press the Verify Connection button to confirm that you have entered the parameters and setup the environment correctly. You can select the database; the only possible value will be ---- as there is no actual database in this scenario.
- At this stage the remainder of the setup will be as for any database lookup.