Use Microsoft Access or Microsoft Excel

Use the Data Link Properties dialog box to compose a connection string that will enable your project to connect to a Microsoft Access database or Microsoft Excel workbook via OLE DB.

This procedure is a supplement to other procedures that describe how to configure your project to connect to external databases. It assumes you have already opened the Data Link Properties dialog box, which is a standard Windows system interface, and it starts from that point. The Data Link Properties dialog box is invoked by Studio — for example, from the Database Configuration dialog box — but it is not actually a part of Studio. For a more comprehensive description of the Data Link Properties dialog box, go to: https://docs.microsoft.com/en-us/previous-versions/79t8s5dk(v=vs.90)

You do not need to have Microsoft Office installed on your computer in order to use Microsoft Access and Microsoft Excel files with AVEVA Edge; your project can directly read from and write to existing files, as long as the correct OLE DB provider is installed on both your project development workstation and the computer(s) that will host the project runtime / database gateway.

You might need to install the 32-bit version of the OLE DB provider, however, because AVEVA Edge is a 32-bit application and therefore requires 32-bit providers. Whether you need to do this depends on which versions of Microsoft Windows and Microsoft Office you already have installed, and there are too many possible combinations to cover in this documentation, so the only way for you to know for sure is if you can select the correct provider (that is, Microsoft Office 12.0 Access Database Engine OLE DB Provider) as described in the first step of the procedure below. If you cannot, you need to install the provider.

In most cases, we recommend you download and install Microsoft Access Database Engine 2010 Redistributable (https://www.microsoft.com/en-US/download/details.aspx?id=13255). It is not the latest version of that software, but it includes the correct OLE DB provider and it can be installed on top of the latest version of Microsoft Office, if necessary. Make sure you download the 32-bit version (also known as X86), not the 64-bit version (also known as X64).

This procedure was last tested using Microsoft Office 2016.

To compose a connection string for a Microsoft Access database or Microsoft Excel workbook:

1. In the Provider tab of the Data Link Properties dialog box, in the OLE DB Provider(s) list, select Microsoft Office 12.0 Access Database Engine OLE DB Provider.

2. Click Next.

   The Connection tab of the Data Link Properties dialog box is displayed.

3. In the Data Source box, type the absolute file path and name of the Microsoft Access database (.accdb) or Microsoft Excel workbook (.xls or .xlsx).

   The data source file should be located somewhere that can be accessed by both your project development workstation and the computer(s) that will host the project runtime / database gateway. If the file is located in your project folder, you might need to correct the file path when you download your project folder to another computer.

4. If access to the data source file is restricted, so that you need a user name and/or password in order to use it, do the following:

   1. In the User name and Password boxes, type your credentials for the data source file.

      You might need to clear the Blank password option before you can type the password.

   2. Select the Allow saving password option.

5. If the data source file is a Microsoft Excel workbook (.xls or .xlsx), do the following:

   1. Click the All tab of the Data Link Properties dialog box.

   2. In the list of properties, double-click Extended Properties.

      An Edit Property Value dialog box is displayed.

   3. In the Property Value box, type one of the following:

      File Extension	Property Value
      .xls	Excel 12.0; Hdr=Yes
      .xlsx	Excel 12.0 Xml; Hdr=Yes

      The first part of the property value indicates that the data source file is a Microsoft Excel workbook, as opposed to a Microsoft Access database. The second part of the property value indicates that the data source includes a header row with appropriate column names.

   4. Click OK to save the property value and close the Edit Property Value dialog box.

   5. Click the Connection tab of the Data Link Properties dialog box.

6. Click Test Connection.

   A message is displayed to inform you if the test connection succeeded. If it did not, review your settings and then try again.

7. Click OK to close the Data Link Properties dialog box.

The connection string, user name, and password are automatically pasted into the database configuration. Examples of the connection string are shown below:

Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\MyDatabase.accdb

Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\MyWorkbook.xls; Extended Properties="Excel 12.0; Hdr=Yes"

Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\MyWorkbook.xlsx; Extended Properties="Excel 12.0 Xml; Hdr=Yes"

You can replace any part of the connection string with a string expression in order to change the value during project run time. This is typically done when the data source file is located in the project folder but you don't know where the project folder will be located after it is downloaded to another computer or device. If you replace the absolute file path with an expression that gets the file path of the project folder, you can ensure the connection string will work after it is downloaded. For example, using the GetAppPath function:

Provider=Microsoft.ACE.OLEDB.12.0; Data Source={GetAppPath()}MyWorkbook.xlsx; Extended Properties="Excel 12.0 Xml; Hdr=Yes"

As you finish the database configuration, there are a few other things you might need to do.

First, in the database configuration's advanced settings, select the Disable Primary Keys option. If you do not, your project will not be able to connect to the data source. For more information, see Database Configuration.

Second, if the data source file is a Microsoft Excel workbook, be aware that the sheets of the workbook are equivalent to tables in a database, and when you need to specify a table in your database configuration, you actually need to specify a sheet. The name of the sheet should be formatted as follows:

[<sheet name>$]

Third, make sure the specified sheet includes a header row with appropriate column names, because you will need to reference those column names when you use the specified sheet with your project.

More generally, please keep in mind that Microsoft Access and Microsoft Excel are desktop office applications, and they cannot efficiently handle large amounts of data. If you try to save all of your project's historical data in a Microsoft Access database or Microsoft Excel workbook, the queries will become slower over time and you might get unexpected results. Therefore, we recommend you use such a data source only as a Secondary Database, with the Store and Forward option selected.

To handle large amounts of historical data, we recommend you use either the AVEVA Edge proprietary format or a dedicated relational database such as Microsoft SQL Server or Oracle.