Integrating with an Access database

Integrating with an Access database

First, mind that what is presented here is only a possible solution for integration, based merely on experience and not necessarily recommended as a generic solution.

Steps to take:

1. Create the linked server (dblink):
- On SQL Server Enterprise Manager, go to folder <server>\Security\Linked Servers, and choose right-click option "New Linked Server".
- On tab "General"
. give a name to the linked server,
. select "Other data source" with provider "Microsoft Jet 4.0 OLE DB Provider",
. and fill "Data source" with the path to your .mdb file.
- On tab "Security" you can simply select option "Be made using this security context" with login "Admin" and an empty password.
- Press "Ok" to create the linked server (dblink).
- The new linked server will appear under the folder <server>\Security\Linked Servers.

2. Create views to the required Access tables:
- In the catalog used by OutSystems platform create one view for each Access table that needs to be accessed. Example:

3. Import the created views using Integration Studio:
- In Integration Studio, open / create the extension where you'd like to expose the external entities (tables).
- Right-click folder "Entities" and select "Import Entities from Database...".
- Select the database used by OutSystems platform and where the views were created.
- Add the views you've created and press "Finish" after it.
- Eventually fine-tune some of the entity attributes, for example:
. on each entity, mark a field as "Auto Number" and set it as entity identifier,
. length and number of decimal digits for Decimal fields
. add descriptions...
- Publish the extension

4. Using the external entities in Service Studio:
- Due to the limitations in transactional support from Access databases you'll have to perform non-query statements (insert, update, delete...) out of the context of a transaction. Hence, in an advanced query, you'll have, for example:
INSERT INTO {<name of the imported entity>} ...
- Very important: mind that no rollback operations will be available this way: these will have to be performed "manually" with "inverse" operations.