Omron CX-Supervisor, User Manual

Page: 187 / 334

Configuring a Connection SECTION 12 Databases
186
12-2-6 Creating a Read/Write connection to an Excel file
Connections to Excel files may result in the error "Operation must use an
updateable query". This is because the Provider installed with ADO version
2.0 is read only by default hence Records can not be added or amended. To
override this function you must manually change the Read Only property in the
Connection String. On the "Modify Connection" dialog box click "Advanced…"
and set the connection property as shown in the last line below:
Driver={Microsoft Excel Driver (*.xls)};
DBQ=C:\WorkBook.xls;
ReadOnly=False;
Excel files can also be updated by accessing the file via the ODBC DSN driver.
This is achieved by carrying out the following steps:
• Create a File DSN for the required Excel file with the following options (see
Chapter 12, Creating a Data Source Name file)
• Select the Microsoft Excel Driver (*.xls). If this option does not exist, you
will need to install the Microsoft ODBC driver for Excel from the Excel
setup.
• Ensure that the "Read Only" check box is clear.
• Load the Excel spreadsheet and create a table to access the data by
creating a Named Range as follows:
• Highlight the row(s) and column(s) area where your data resides
(including the header row).
• On the 'Insert' menu, point to 'Name', click 'Define' and enter a name for
your range.
• Create a connection in the CX-Supervisor Workspace specifying the File
DSN as its source.
• Add a Recordset to the connection and select the Named Range (which
will appear in the list of available tables, if the connection is live) as the
Table name. Records in this table can now be added or modified as with
any other database table (Note: If records are added to this type of table
the Named Range will increase in size accordingly automatically).
The example below demonstrates a valid range selection named:
"CustomerInvoice" (note the name displayed just above column A):
Note: The first row of the range is assumed to contain the Column Headings.