DV1103 - Linking a DOVICO database with Microsoft Access

A DOVICO database can be linked to Microsoft Access in two ways. The first way is with a two-way live link, using a Microsoft Access Project. The second is to create a one-way active link with a Microsoft Access Database.

Live link with a Microsoft Access Project: this is a bi-directional link which displays all the information in the DOVICO database. It allows you to view all the information within the database and also add, edit or delete information.

Active link to a Microsoft Access Database: this is a one-way link which displays the information from only the tables and views that you choose in the DOVICO database. This is a read-only link and does not allow you to modify information within the DOVICO  database.

Creating a live link with a Microsoft Access project

1. Start Microsoft Access.
2. Choose File, New, Project (Existing Data)

   

3. Save the Microsoft Access Project file.

   

4. A Data Link Properties page will be displayed.

   

5. Enter the Server Name (by default: SERVERNAME\DOVICO)
6. Choose Use a specific username and password.
7. Enter the User name (by default: sa)
8. Enter the Password (by default: dovico)
9. Select Allow saving password.
10. Select the DOVICO database.
11. Click OK.

    

12. Click Save password.

You are now connected to the DOVICO database with a live link, and you are ready to create reports in Microsoft Access. Changes made in DOVICO Timesheet/Track-IT Suite will automatically be reflected in the Microsoft Access database you’ve created.

IMPORTANT:: this is a two-way live link to the SQL database, any changes made to the data or database structure within Microsoft Access will also be reflected within application. Please refrain from adding, editing or deleting existing column definitions within the Design View of Microsoft Access, as this may cause the software to malfunction.

Creating an Active link to a Microsoft Access database

1. Start Microsoft Access.
2. Create a new Microsoft Access database or open an existing database that you wish to use.
3. Select the File - Get External Data - Link Tables option from the menu.

   

4. Select ODBC databases for the File of Type: drop down box.

   

5. Select New to create a new data source.

   

6. Select SQL Server as the driver for your data source. (Next)

   

7. Give this data source a name. (Next) (Finish)

   

8. Select the server that contains the database you want to connect to. (Next)

   

9. The authenticity of the login ID will be verified using SQL Server authentication. By default the ID is "sa" with the password “dovico”. (Next)

   

10. Change the default database to the DOVICO database you wish to connect to. (Next)
11. Click Next then Finish.
12. Select the data source that you just created.
13. Enter the password “dovico” again if required.
14. Select the Tables that you wish to link and click OK. It is recommended that you only select tables that begin with the name dbo.ACCESS, dbo.DOVICO or dbo.DOVREP.

    

15. You will be prompted to Select a Unique Record Identifier for each table. IMPORTANT: Don’t select a Unique Record Identifier; simply click OK to continue for each table.

You are now connected to the  database with an active link, and you are ready to create reports in Microsoft Access using information entered and recorded using DOVICO Timesheet/Track-IT. Changes made in the application will automatically be reflected in the Microsoft Access database you've created. Please note, this is a one-way link, the data cannot be modified in Microsoft Access.

Suggestion: For ease of use, access the Tables starting with the name DOVICO, as these Views have been setup for time & expense entry reporting including the custom fields applicable to the time or expense entry entered by an employee.

• See also this developers documentation on Microsoft Access and the creation of Database Views
• See additional information on Database Schema's, Visio Documents etc. by visiting our developers center