The GetData wizard retrieves database data from an ODBC data source. Using the GetData wizard, DriveWorks allows you to connect to existing data within your company. The wizard walks you through the database connection, assists in the selection of the table or view from which to pull the data, and then helps sort and filter the data accordingly.
This is then a dynamic read only link to the data, and can again be used to create lists or lookups within DriveWorks. You can choose when this data gets updated, whether it’s as a manual process or performed automatically. This function is more suited to retrieving large amounts of data.
If the data in the database changes then updating this link in DriveWorks either manually or automatically will refresh the Table in DriveWorks with the new data. Using this method, it is important that the table structure of the external data is accessible and understood.
GETDATA( [" Database"], [" Username"], [" Password"], [ Table], [ Field], [ Where] )
The GetData wizard produces the query language required to connect, filter and display the data. The following explains each step required.
The DSN name of the database to connect to
The username to gain access to the database
The password to gain access to the database
The table that contains the data in the database
The field in the table the data is to be retrieved from
The Where clause relies on some knowledge of the SQL Query language. Examples of typical Where clauses are listed below:
CustomerName Like ‘A%’
Where CustomerName is the field in the table of the database. Like is the comparison operator. % is a wild card symbol.
This Where clause will display all customers in the CustomerName field where the name begins with A
CustomerName Like ‘ “&LetterReturn & “%’
This Where clause will display all customers in the CustomerName field where the name begins with a letter selected from a control on the user form called LeterReturn.
Other comparison operators that can be used with the where clause are listed below:
|<, >||Less than\ Greater than|
|Not Like||Does not contain|
|Is Null||Is empty|
|Is Not Null||Is not empty|