Database functions

As a help for the definition this function can be selected from the menu entry Format/Functions (or with the right mouse button on the cell).

Attention

At the moment only reading access is possible.

You can use MS Access databases and databases with OLEDB provider. Use the following syntax for that.

1. MS Access database

Syntax: =sqldao ("database","SQL query",Para1,Para2)

2. Databases with OLEDB provider

If you want to use a database with an OLEDB provider, replace the link to the database with an OLEDB initialization string. You can enter this string manually or more comfortably by clicking the button Create OLEDB provider.

After clicking the button OLEDB Provider the dialog Data connection properties appears. You can configure all the properties for the connection to your database here: OLEDB provider type, data source, user data, etc. After confirming the dialog, an initialization string is generated and inserted in the previous window under Syntax.

Syntax: =sqldao ("database","SQL query",Para1,Para2)

Attention

The initialization string must always begin with Provider=" . The string is case sensitive! Please take care of this case sensitivity, otherwise the string will not be recognized.

Settings for MS Access and OLEDB

Transfer parameters	Description
Database	Name of the .mdb database. Storage directory: Project folder which is superoriented to the Runtime folder. E. g.: If the Runtime is created as a subfolder of C:\Users\Public\Documents\zenOn_Projects\Multi\TEST_PROJEKT, the file .mdb must be saved directly in folder C:\Users\Public\Documents\zenOn_Projects\Multi\TEST_PROJEKT. The project folder is defined in the project properties.
Provider	OLEDB provider; the initialization string must always begin with Provider=" (case sensitive).
SQL query	You can use standard 'select' database queries here. with "%d %d" 2 integer parameters or with "%s %s" 2 string paramaters can be transferred
Para1, Para2	parameters for SQL query

Examples for MS Access

Example database: Address.mdb

Name	First name	Street	City	Telephone	CanAddress
Müllner	Egon	Am Rheinberg 22	5020 Salzburg	0662/329354	1
Roider	Jürgen	Mozartgasse 7	5020 Salzburg	0662/329354	2
Leitner	Hans	Strubergasse 6	5020 Salzburg	0662/329354	3
Müller	Peter	Blumengasse 6	80003 München	089/234243	5
Weinberger	ww	Hafnergasse 5	7020 Klagenfurt	0222/2222	6

Example

Example 1:

gets all entries from the table address with the field name name.

Entry in the cell:

=sqldao("Address.mdb","SELECT Address.name FROM Address ;",1,1)

Address.mdb	Access database (must be in the project database path)
SELECT	Tells the Microsoft Jet database module, tu return information from the database as a group of records
Address.name	table.field name
FROM	states the table of query in which to find the fields stated in the SELECT statement
Address	Table

Result:

Müllner

Roider

Leitner

Müller

Weinberger

Example

Example 2:

Lower limit: Contents of cell E1

Upper limit: Contents of cell E2

Field name for evaluation: CanAddress

gets all entries from the table address with the field name name and first name.

With the condition CanAddress must be higher than contents of cell E1 and lower than contents of cell E2.

Entry in the cell:

=sqldao("Address.mdb","SELECT Address.name,Address.first name FROM Address WHERE (((Address.CanAddress)>%d) AND ((Address.CanAddress)<%d)) ;",E1,E2)

Address.mdb	Access database (must be in the project database path)
SELECT	Tells the Microsoft Jet database module, tu return information from the database as a group of records
Address.name	table.field name
,	SQL seperator
Address.first name	table.field name
FROM	states the table of query in which to find the fields stated in the SELECT statement
Address	Table
WHERE	States, which records of the table defined in the section FROM are effected by a SELECT-, UPDATE- or DELETE-statemant
(((Address.CanAddress)>%d)	Condition: table.field name
AND	connection
(((Address.CanAddress)<%d)	Condition: table.field name
;	SQL statement end
E1, E2:	Cell references (integer)

Result for E1 = 1, E2 = 6

Roider	Jürgen	2
Leitner	Hans	3
Müller	Peter	5

Info

For further information on SQL statements refer to the Online-Help of Access.

Example for databases with OLEDB provider

Example

=sqldao('Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestSQLDAO;Data Source=RechnerName\ZENON_DEV;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID= RechnerName;Use Encryption for Data=False;Tag with column collation when possible=False','SELECT TestTable.* FROM TestTable;',0,0)