OLE DB / ADO

Version 2.0 is the first major upgrade to VB ELF, and we think you’ll agree that it deserves the distinction of a "major" rather than "minor" upgrade. Not only has VB ELF been extended to generate native SQL Server 7.0 Transact-SQL and native Oracle PL/SQL, it has been fully re-engineered from top to bottom to make full use of the newest data access standard from Microsoft, namely OLE DB (and its ADO "wrapper").

Let’s take a minute to outline the benefits of the new technology. Until this release, English-to-SQL translations were done into a single target SQL dialect, what’s now called Jet SQL (formerly "Microsoft Access SQL"). This system had its upside and downside. It was convenient for us to only have to write a single dialect, but it meant that all database connections had to pass through Jet, which handled the business of actually accessing the data transparently for the ELF program via "attachments". While this meant that we didn’t need to worry about the "flavors" of SQL preferred by various ODBC databases, it also meant that the efficiency of client-server systems was sacrificed whenever you used VB ELF. The reason is that Jet itself is not a client-server system; so it was the weak link in the chain.

Most of you are probably familiar with the main differences between client-server systems and those "desktop" systems like Jet. When you post a query in a client-server system, the request is passed to the server, and only the information comprising the answer is returned to the client. So for instance, if we ask about "Bob in Accounting", only records related to Bob are sent back over the network. In contrast, with Jet or other file-server systems, a request for information about Bob will cause the server to send the entire table of Employee information back to the client, where it will then be filtered to produce the one or two records the user’s actually interested in -- not a very efficient system.

This brings us round to why you might want to use the new OLE DB data access methods against your ODBC databases. Since they no longer rely on Jet, they have the full efficiency of client-server systems. Any ODBC database can be accessed through OLE DB by using Microsoft’s "generic" OLE DB Provider for ODBC. Of course, by now many ODBC systems also have their own finely-tuned OLE DB Providers, and VB ELF will support any of these as well.

The system for using VB ELF with an OLE DB data source is quite simple. You must create a .UDL (Microsoft Universal Data Link) file for each data source you want to access. (This is similar to the process of creating a DSN under the older style of using ODBC data sources.) To create a .UDL file, you can click the right mouse button on the Windows desktop itself, of within the file display pane of Windows Explorer. Next. Select "New" from the pop-up menu and choose Microsoft Data Link. Highlight the newly-created .UDL filename, rename it to something descriptive of your data source, and right-click the file name itself to set its properties (choose Properties). The UDL Properties window will be displayed, giving you the opportunity to select the OLE DB Provider (Provider Tab). Use the Connection Tab to enter most of the other information, including log on and password information. Note that it’s usually preferably to disable the password saving option, since otherwise the passwords will be available to anyone browsing your UDL file. VB ELF will of course give you an opportunity to supply passwords for secured UDL files.

You can also create .UDL files on-the-fly from within VB ELF itself. Just select the OLE DB option, and right-click the file display pane on the Select Data Link File window that appears. You can follow the same process as above to create, or reset properties for, any OLE DB data link.

We’ve put some thought into the VB ELF Select Database window, and hope that you find it easy to use. Individual record sets from OLE DB providers can be selected or deselected by double-clicking; you may want to simply include all record sets available, since you also have the option of excluding individual tables and queries later, during the Custom Analysis process. The three data access methods that were available in the previous (1.2a) release are of course still supported. This means you can continue to access data directly from Jet MDB data sources as before, or continue to use Jet as a frontend, or host, for ODBC data sources (subject to the efficiency limitations described above). This is still an excellent way to handle small amounts of data, particularly from mixed ODBC data sources, since the frontend MDB gives you a convenient way to set up and view relationships between tables in disconnected DBMS.

However, this same ability is now available to you through the Mixed OLE DB option, and of course it also provides client-server efficiency. The way the Mixed OLE DB option works is this: multiple .UDLs can be chosen in turn, and various data sources selected from them. Just as in the older system, where we created a frontend MDB file to host the Jet "attachments", we now create a frontend MDB database to hold the information about OLE DB connections. Note the important difference that, in the latter case, no information is being passed from the external data source through Jet -- only information about the data source itself is handled by Jet.

While we formerly relied on Jet to handle the details of tying these different data sources together, under the new OLE DB regime we’re relying on Jet’s big sister, SQL Server 7.0, to perform the same function. So this capability is only available on networks in which at least a single SQL Server 7.0 server is available. The first UDL selected should contain a reference to a data source hosted by this SQL Server 7.0 server, which will become the "host" domain for any references made through it to other databases like Oracle, Sybase SQL Anywhere, or even Jet.

In this way you can run queries (helped along by VB ELF) which span data sources -- connecting SQL Server to Oracle to Jet data in any combination. Just as in the case when data is all contained within a single source, VBB ELF will need to know how to join up the different tables; the keys and foreign keys that put the "relation" in "relational database". But because SQL Server doesn’t "know" about these remote data sources, you can’t store information about the joins in SQL Server! The solution is to store such relationships within VB ELF’s own internal database; and we’ve made it very easy for you to do this with a new "on-the-fly" relationship diagrammer accessible using the "Relationships from:" button of the Custom Analysis window.

The following section discusses the older "ODBC through Jet" approach. While still viable in some circumstances, we think you’ll prefer the efficiency and ease of use of the newer OLE DB data access methods in many cases.

ODBC Considerations

The ELF interface has been tested using ODBC connections (through the Jet engine) to many types of data sources, including Oracle, Sybase SQL Anywhere, and even Jet Engine databases themselves (via the Microsoft Driver accessed from Delphi). Because Jet is used as a gateway, the SQL constructed has been standardized to Jet's version of the SQL language. However, different drivers support these functions in slightly different ways. In fact, Microsoft's own ODBC Access Driver responds somewhat differently than using Access itself; for instance it fails to process certain queries where there are "ambiguities" caused by identical field and table names (queries which are handled perfectly by Access). The moral is, your mileage may vary. However, we look forward to your feedback so that we can smooth out these idiosyncrasies in the future.

Because of these slight variations, we recommend that whenever possible, you use the Microsoft ODBC library to connect VB ELF to data. Even if your application is using a different driver, say Intersolve's xBase Driver, you should permit ELF to use the Microsoft xBase Driver instead. What this means is that, instead of selecting the ODBC option, and choosing your ODBC data source, you should choose the Mixed option. In the first case, the "gateway" Jet front-end database will be constructed using attachments that use the default ODBC driver for your data source. Instead, if you select the Mixed option, you can add attachments for each table individually, and in the dropdown list of attachment types, you have a choice of either ODBC or native xBase (Paradox, Excel, etc). "Native" in this case means via ODBCJT32.DLL, which is Microsoft's driver for Access and ISAM databases. These native attachments will generally give better results than connecting through a third-party ODBC driver, which is why we've embedded, right in the program, reminders to use this method where possible.

Nothing prevents you from creating an application which uses some third-party ODBC driver for your own database functions, but allows ELF to get to the same data source through Microsoft's ODBC library. In fact, if you're using Delphi to connect to an .MDB file, this is sort of what you must do. That is, you connect the Delphi app to a database using a normal ODBC connection (via ODBCJT32.DLL). In this case the ODBCJT32.DLL plays the part usually played by a third-party driver (since, to Borland, Microsoft is a third-party!). Then, when connecting ELF to a data source, you don't have the normal choice of ODBC vs. Native, since the Jet Engine will not permit you to attach to an Access or ISAM database via ODBC, only via its "native" use of ODBCJT32. So you must select the Jet option. (Or, to create a heterogeneous data source, with Jet and others, select Mixed.)

OCX Programming Interface Definitions
The sample client programs demonstrate the basics of using an OCX control (ELF4VB) to get access to the VBELFOLE language processing services. This is a complete survey of the defined properties and methods of ELF4VB. We've tried to keep the interface simple, yet complete. Feedback is welcome, and of course, if you have the Developer's "Deluxe" Edition, you can make improvements yourself . . .since you have all the code for both the OCX and the VBELOLE server itself.

In the following, "elfcls" represents the class exported from VBELFOLE (which is actually ELF_Interface in the OCX), and elfocx represents the name of the client's instance of the ELF control, which by default is ELF4VB1.

The entries for the methods are not examples of coding, they are descriptions of the effect produced by invoking the method. For example, the actual code for a client's Browse button click event might be:
ELF4VB1.vbelfSelect
mySelectedMDB = ELF4VB1.vbelfJetMDB
The first line invokes the vbelfSelect Method to open the VB ELF Select Database dialog, while the second line assigns the vbelfJetMDB Property (set as the result of the first line) into a variable named mySelectedMDB.

The documentation of the vbelfSelectJetMDB behavior in the following section . . .
elfcls.elfSelect() :
elfocx.vbelfJetMDB = elfcls.JetMDB
. . .shows that the effect is to call the elfSelect Method exported by VBELFOLE's ELF class, and then to assign the class's JetMDB Property back into the vbelfJetMDB Property of the OCX control, where it's available to be read by the client application.

OCX Properties:
vbelfDisplayWarnings See vbelfOpenGrammar()
vbelfErrorCode (1) vbelfOpenGrammar() - False if unsuccessful
vbelfErrorCode (2) vbelfSelect() - False if unable to locate file described by JetMDB
vbelfErrorCode (3) vbelfTranslate()

If vbelfErrorCode > 0 Then ' Successful
If vbelfErrorCode = -5 Then ' ELF was busy doing something else
If vbelfErrorCode = -4 Then ' ELF didn't recognize this word(s) reported back in vbelfErrorString
If vbelfErrorCode = -3 Then ' grammar files have become corrupted
If vbelfErrorCode = -2 Then ' using VB ELF on ASP-enabled machine without Internet/Intranet license
If vbelfErrorCode = -1 Then ' recognized compound, but it's not in the data; eg. "New York City" (reported back in vbelfErrorString)
If vbelfErrorCode = 0 Then ' unable to understand the question

vbelfErrorString See vbelfErrorCode (-4) and (-1)
vbelfExecuteSQL See vbelfTranslate(); legal values "0" through "9" (excluding reserved "8")
vbelfGrammarDir See vbelfOpenGrammar()
vbelfGrammarPath See vbelfOpenGrammar()
vbelfInteractive See vbelfAnalyze()
vbelfJetMDB sets the path to the Jet .MDB file, used by Analyze and Query methods
vbelfQueryText English question, used by vbelfTranslate or vbelfQuery
vbelfResultSQL SQL translation, unless vbelfExecuteSQL = "5", then name of the result query
vbelfSpellCheck See vbelfOpenGrammar()
vbelfDSN Used only by ELF4Delphi, as described above.

Added in version 1.1:
vbelfJetPassword Holds code for password-protected files; used by vbelfAnalyze, vbelfSetMDB

Note: this has been added to allow access to password-protected Jet databases. The passwords are normally set using Tools/Security/Set Database Password in Access 97. This is a simpler security setup than the usual SYSTEM.MDA technique, which keeps user IDs and passwords in a distinct security file. This original security mechanism is still supported in VB ELF version 1.1 by using the SYSTEM.MDA option from the Jet drop-down menu in the Database Design section. (Moreover, it actually works, since we fixed the 1.0 bug preventing this from operating when compiled under VB5 with Jet 3.5.)

Added in version 1.2:
vbelfGraphDescriptor Passes graph description information in either direction, to allow client to format an independently-owned graph object (such as Graphics Server 5.1 graph object).

Added in version 2.0:
vbelfUsesOLEDB True/False read-only property which can be consulted to find out whether the current interface is running off a Jet (or Jet-attached) database; or via OLEDB using a .UDL. Applications may need to know this in order to decide which class of data-bound controls to use with the interface.

vbelfOLEDBConnectionString Applies when the above property is True; gives the Connection String for the current OLE DB connection. Note that these connection strings do not contain password information (if password information is, as is customary, NOT stored in the .UDL). Having this property available allows you to set the Connection property of OLE DB data grids after the user has interactively changed the data source. Of course, you may prefer to keep a tighter rein on the application, by allowing only certain data sources to be accessed; in that case, you’ll probably know up-front what the current connection contains.

To view an example of how this more flexible method may be used, see our OLE_DEMO and OCX_DEMO sample code.

OCX Methods:
vbelfAnalyze
elfcls.NonInteractive = Not elfocx.vbelfInteractive
elfcls.JetMDB = elfocx.vbelfJetMDB
elfcls.JetPassword = elfocx.vbelfJetPassword
elfcls.elfAnalyze()
Effect: create an interface grammar for the selected database. If vbelfInteractive is set False, the "Express" analysis technique is used, no dialog boxes are allowed, and only a mini status meter is displayed (no status messages). If a password is required for access to the MDB file, it should be supplied via JetPassword.

vbelfCustomize
elfcls.elfCustomize()
Effect: pop up the form allowing user to customize the elfAnalysis table. The entries in this table change the behavior of the query processor immediately, and will also be retained for future analysis operations.

vbelfHistory()
elfcls.elfHistory()
Effect: pop up the History form, allowing user to select from past queries

vbelfLexicon()
elfcls.elfLexicon()
Effect: pop up the Dictionary lookup form

vbelfOpenGrammar()
elfcls.DisplayWarnings = elfocx.vbelf DisplayWarnings
elfcls.SpellCheck = elfocx.vbelfSpellCheck
elfcls.GrammarPath = elfocx.vbelfGrammarPath
elfcls.GrammarDir = elfocx.vbelfGrammarDir
elfocx.vbelfErrorCode = elfcls.elfOpenGrammar()
Effect: Open an interface grammar in preparation for queries DisplayWarnings determines whether the ACCELF.DLL displays message boxes in response to errors, or simply returns an error code.
SpellCheck determines whether the ACCELF.DLL opens a correction dialog box when it encounters an unknown word in a query.
GrammarPath is usually set to "VB ELF Directory" which is translated by the program to the actual path where the VBELFOLE program resides. It can also be set to a path, such as "n:\apps\elf\interfaces".
GrammarDir is the name of the View used to define the interface grammar (by default, the name of the .MDB itself) followed by .ELF. (If .ELF is not specified, it is supplied by default.) As an example, if the View is "Northwind", the GrammarDir is "Northwind.ELF" and the subdirectory (under the VBELF directory) where this interface is stored would also be Northwind.ELF. (Or, if GrammarPath was customized as shown above, "n:\apps\elf\interfaces\Northwind.ELF".)

vbelfOptions()
elfcls.elfOptions()
Effect: pop up the VB ELF Settings (tabbed) form

vbelfPatchSQL()
elfcls.elfPatchSQL(elfocx.vbelfResultSQL)
elfocx.vbelfResultSQL = elfcls.PatchedSQL
Effect: See vbelfTranslate effects

vbelfPhrases()
elfcls.elfPhrases()
Effect: pop up the phrase definition form

vbelfQuery()
elfcls.QueryText = elfocx.vbelfQueryText
elfocx.vbelfResultSQL = elfcls.elfQuery()
Effect: pops up the VB ELF query window, with the text of elfocx.QueryText as the current query and returns the SQL into vbelfResultSQL. (With the VB ELF query window open, the user can interactively change the response type.)

vbelfQuit()
elfcls.elfQuit
Set elfcls = Nothing
Is not normally used by client app, since the OCX terminates when its form is closed. Included for completeness only.

vbelfSelect()
elfcls.JetMDB = elfocx.vbelfJetMDB
elfcls.JetPassword = elfocx.vbelfJetPassword
elfocx.vbelfErrorCode = elfcls.elfSelectJetMDB()
Effect: set the database (to perform analysis or queries on) to the value of JetMDB (if ".MDB" is not supplied, it will be added by default).

Note that only Jet database are supported through direct assignment. To handle ODBC-connect database such as Oracle or Sybase SQL server, you must use the vbelfSelectJetMDB() method, which is the "Browse" function. This interactively walks you through the steps necessary to create a Jet database frontend for the ODBC attachments. The name of the Jet database created consists of the first 8 letters of the DSN (Data Source Name) of the ODBC data source (with spaces replaced by underscores). Subsequently, you can use this name to set elfocx.JetMDB prior to calling elfocx.vbelfSelect().

vbelfSelectJetMDB()
elfcls.elfSelect()
elfocx.vbelfJetMDB = elfcls.JetMDB
elfocx.vbelfJetPassword = elfcls.JetPassword
Effect: pop up the browse database form to enable user to select a Jet MDB, or to create a Jet MDB frontend for an ODBC, or mixed source, datasource. Beginning in version 2.0, can also be used to select a .UDL or frontend MDB previously created for use with a .UDL. The OCX's vbelfJetMDB property is set to the name of the selected database. (Note: the password assigned back is NOT the password for the MDB file chosen by the browse facility -- that would kind of defeat the purpose of passwords. Actually, you're just retrieving whatever was last stored into the password variable in the server. So in order to browse to a password-protected database, you need to select it (with this function), THEN set the password, THEN call vbelfSelectJetMDB(), which now can set the correct MDB and password together.

vbelfSQL()
elfcls.elfSQL()
Effect: pop up a window containing the SQL translation of the last successfully-translated query (Note: will still be valid even after a subsequent failure to translate; you must use the error code available after invoking elfTranslate to determine whether the SQL shown will be related to the last query submitted.)

vbelfToolbar()
elfcls.elfToolbar()
Effect: pop up the VB ELF toolbar

vbelfTranslate()
elfcls.QueryText = elfocx.vbelfQueryText
elfcls.ExecuteSQL = Val(Left(elfocx.vbelfExecuteSQL, 1))
elfocx.vbelfResultSQL = elfcls.elfTranslate()
elfocx.vbelfErrorCode = elfcls.ErrorCode
Effect: translate the English question in elfocx.vbelfQueryText and return the SQL translation into elfocx.vbelfResultSQL. The type of action take depends on the value of elfocx. vbelfExecuteSQL. (Only the first character is used, so for instance "1 Datasheet" or "1. . .open a datasheet" are both valid settings for elfocx.vbelfExecuteSQL.)
9 - return SQL and graph descriptor only
8 - Reserved
7 - return SQL and show the answer in the Worksheet style
6 - return the SQL for the last query (used immediately after (5) to get associated SQL)
5 - return the name of the query that will yield the answer as a dataset (instead of SQL)
4 - return SQL and show answer by "best guess" method; datasheet for simple data and forms for queries returning OLE data such as pictures
3 - return SQL and show the answer as a graph, if graphable. If not graphable, uses datasheet display
2 - return SQL and show the answer in a form
1 - return SQL and show answer as a datasheet
0 - return raw SQL and do not process. Note that if using this option you do not actually need to set the value of vbelfJetMDB, since it is never referenced by ELF for query processing. Presumably you, the client, will process the SQL on your own, but note that there are many special considerations.

For instance, the SQL may consist of several statements, each of which refers back to a previous one. Also, the SQL returned may have embedded in it certain functions which are not processable by Visual Basic. Here's an example: given the question "Which products cost more than chai?" (working from Northwind), ELF translates this into:

SELECT DISTINCT Products.[Product Name] , Products.[Unit Price] FROM Products WHERE ( Products.[Unit Price] > DMin ( "[Unit Price]" , "Products" , '[Product Name] = "Chai"' ) or Products.[Product Name] = "Chai" ) Order by Products.[Unit Price] ;

Unfortunately, while DMin is happily handled by the Microsoft Access query processor, it is unknown to the Visual Basic SQL engine, and despite all the documentation to the contrary from VB 4.0 on, Visual Basic cannot handle UDFs (user-defined functions) in SQL. So the only solution is to emulate the behavior of this function by detecting such forms in the SQL statement, and performing a lookup for the value, then patching the value as a constant into the SQL itself. In other words, after "patching", the SQL becomes:

SELECT DISTINCT Products.[Product Name] , Products.[Unit Price] FROM Products WHERE ( Products.[Unit Price] > 18 or Products.[Product Name] = "Chai" ) Order by Products.[Unit Price] ;

<>

Now, the reason that ELF does not simply return this "legal" SQL is that it appears very obscure. The above SQL does not look like a translation of "Which products cost more than chai?" ("what the heck is that 18 doing there?!") Our solution to this problem is to return the "Access" version of the query, and allow the client application to then call a function to patch it into executable form. This way, you can use one form of the translation for reassuring the user that his query has been understood, and the other form for getting the correct results.

Setting elfocx. vbelfExecuteSQL = "5" will greatly simplify your task. Each SQL statement is then automatically patched, and converted into a query in the source database. All you need to do is assign the name of the last query (for instance "elfQ2", or "elfQ3") as the RecordSource of a Data control

When vbelfExecuteSQL = "5", elfocx.vbelfTranslate() does not return SQL, but instead returns the name of this query.

It may be even easier to use response styles "1" (Datasheet) through "4" (Best Guess), since then both the creation AND the display of the resulting record set (or graph) is handled automatically by VB ELF.

If you absolutely need to handle the SQL yourself, parse out each SQL statement by using the ";" (**) terminator. Then,
1) set elfocx.vbelfResultSQL = to the single SQL statement
2) call the elfocx.vbelfPatchSQL() Method
3) use the patched result, now in elfocx.vbelfResultSQL

(**) Note that ";" can also occur within a OpenRowset descriptor -- this applies only to remote heterogeneous data access, aka "Mixed OLE DB".

For example, "Which employees have birthdays in June?" translates into the SQL:
SELECT DISTINCT Employees.[Employee ID] , Employees.[Birth Date] , Employees.[Last Name] FROM Employees WHERE ( ( elfMonthDay ( Employees.[Birth Date] ) >= elfMonthDay ( #06/01/97# ) and elfMonthDay ( Employees.[Birth Date] ) < elfMonthDay ( DateAdd ( "M" , 1 , #06/01/97# ) ) ) ) ;

This uses the unsupported function elfMonthDay (defined in Access ELF, but not VB ELF). When VB ELF processes this SQL, it substitutes for the elfMonthDay function, producing:

SELECT DISTINCT Employees.[Employee ID], Employees.[Birth Date], Employees.[Last Name] From Employees WHERE (((DateValue(IIf(IsDate([Employees].[Birth Date]), DateSerial(Year(Now()), Month([Employees].[Birth Date]),Day([Employees].[Birth Date])),"12:00:00 AM"))) >= DateValue(IIf(IsDate(#6/1/97#),DateSerial(Year(Now()), Month(#6/1/97#),Day(#6/1/97#)),"12:00:00 AM")) And (DateValue(IIf(IsDate([Employees].[Birth Date]), DateSerial(Year(Now()), Month([Employees].[Birth Date]),Day([Employees].[Birth Date])),"12:00:00 AM"))) < DateValue(IIf(IsDate(DateAdd("m",1,#6/1/97#)),DateSerial(Year(Now()), Month(DateAdd("m",1,#6/1/97#)),Day(DateAdd("m",1,#6/1/97#))),"12:00:00 AM"))));

-- which is highly unreadable (!), but uses only functions supported by Visual Basic.

To get the same result, you must do this:
ELF4VB1.vbelfResultSQL = mySQL
ELF4VB1.vbelfPatchSQL
mySQL = ELF4VB1.vbelfResultSQL

A final note on this topic: the reason this technique works is that the most typical requirement for a user defined function is to supply a single value, such as the Unit Price of chai, in the example above. There are cases where this model breaks down, and as a result VB ELF cannot handle the range of queries that Access ELF is currently able to. However, these cases are very rare; for example:

"What was the last order for each employee?" translates into the pair of SQL statements:

SELECT DISTINCT Orders.[Order ID] , Employees.[Employee ID] , Orders.[Order Date] , Employees.[Last Name] , Orders.[Ship Name] FROM Employees , Orders , Employees LEFT JOIN Orders ON Employees.[Employee ID] = Orders.[Employee ID] ; (this is elfQ1)

SELECT DISTINCT [elfQ1].* FROM [elfQ1] WHERE Orders.[Order Date] >= DMax ( "[Order Date]" , "[elfQ1]" , ' [Employee ID] = ' & [elfQ1].[Employee ID] ) ;

Notice that the DMax function in this case is supposed to return the date(s) of the most recent order(s) handled by each individual employee. We are currently unable to handle this, and similar queries, correctly in VB ELF. As soon as Microsoft adds UDF support to Visual Basic, this problem will be corrected.

Version 1.2 note: By this time it’s clear that Microsoft will probably never support UDFs in SQL processed via VB, so we’re working on replacing all such examples with sub-select SQL statements that will run equally well from either Access or VB. We expect this to be a "former issue" by the 2.0 release

Version 2.0 note: We have in fact eliminated the use of patch functions in most cases -- for example, the "chai" example above is now translated into "pure" SQL Server SQL: SELECT DISTINCT TOP 100 percent Products.[Product Name] , Products.[Unit Price] FROM Products WHERE ( ( ( Products.[Unit Price] > ( SELECT DISTINCT min ( Products.[Unit Price] ) FROM Products WHERE ( Products.[Product Name] = 'Chai' ) ) ) or Products.[Product Name] = 'Chai' ) ) Order by Products.[Unit Price] ;

There are no remaining cases, such as the example "What was the last order for each employee?" (above), which are handled by Access ELF but not VB ELF.

Just in case you’re curious, here’s the SQL Server translation: SELECT DISTINCT Orders.[Order ID] , Employees.[Employee ID] , Orders.[Order Date] , Employees.[Last Name] , Orders.[Ship Name] FROM Employees LEFT JOIN Orders ON Employees.[Employee ID] = Orders.[Employee ID] ; SELECT DISTINCT TOP 100 percent max ( [elfQ1].[Order Date] ) AS Lim , [elfQ1].[Employee ID] FROM [elfQ1] group by [elfQ1].[Employee ID] Order by [elfQ1].[Employee ID] ; SELECT DISTINCT [elfQ1].* FROM [elfQ1] INNER JOIN [elfQ2] ON [elfQ1].[Order Date] >= [elfQ2].Lim and [elfQ1].[Employee ID] = [elfQ2].[Employee ID] ;

However, the same principle applies because most of the date-related function are simply incomprehensible in their expanded native SQL form. We expect to take a lot of heat over the complexity of these date formulations, but there’s just no way to make them both simple and applicable over the range of conditions we need them (see for instance the way dates are used in SQL Server pivot tables resulting from queries like "Show total quantities giving customer down and week of order date across.")

So use of the patch function continues to be necessary, and we continue to recommend that you let VB ELF handle the recordset displays.

Custom display formats
The default formats for Date, Currency and Yes/No types can be changed by adding Registry keys under HKEY_CURRENT_USER \ SOFTWARE \ VB and VBA Program Settings \ VBELF \ VBELF1.0
The default setting for key DateFormat is "d-mmm-yy"
The default setting for key BooleanFormat is "Yes/No"
The default setting for key CurrencyFormat is "$#,##0.00;($#,##0.00)[Red]"
The default setting for key DoubleFormat is "Standard" - which is the standard numeric display format.

Tips for developers
1) If you are creating interfaces for use by your end-users, you should know that it is possible to create a grammar on one machine, then transport it easily to a client's machine. The interface grammar for a particular MDB is packaged into a subdirectory under the currently running VB ELF directory. Let's assume VB ELF is installed into c:\vbelf. Using Northwind as an example, when the analysis is run, a subdirectory called c:\vbelf\Northwind.ELF is created, which contains the interface grammar. If the View Name is changed in the VB ELF Analysis window, to say NWIND, the created directory will be likewise named NWIND.ELF. If the Save To path is changed, the interface grammar will be created as a subdirectory of the specified directory.

You can create a grammar on one machine, then easily copy the grammar directory to your user's machine.

2) If you would like a way to verify that queries you've constructed in the past continue to work properly as you customize your interface in various ways, you'll be happy to know we've built this capability right into the program. Simply add a table to the interface MDB, called elfTestSuite. ["interface MDB" means either a) your Jet MDB data source -- if you're using Jet data directly, OR b) the gateway Jet .MDB file created in the VB ELF directory (named using the first eight chars of the ODBC DSN) -- if you're attaching to an ODBC or heterogeneous source.] This table, elfTestSuite, should have three Text fields: Query, [First Cell] and [Last Cell]. Create regression test examples by entering a query into the Query field. Test the query using VB ELF, and copy the first (upper left) field of the datasheet result into the [First Cell] , and the last (lower right) field into the [Last Cell] field. Repeat this for all your test queries. Finally, to verify their correctness, right click on the VB ELF logo on the query window. Answer Yes to "Run test suite?". ELF will run through the queries automatically and display any differences between the recorded data and the new results.

Distributing your ELF applications

ELF Component Distribution
When using the Distribution Setup Wizard, remember to include the files VBELFOLE.DLL, VBELF.MDA, VBELF.HLP, ACCELF.DLL and ELFCLIB.DLL. VBELFOLE can be registered by any setup wizard by instructing it to run REGSVR32 VBELFOLE. If you install these components manually, you can type REGSVR32 VBELFOLE at the command line, or run it from the Windows Run box. You should receive a notification message informing you that the DLL has been successfully registered.

Jet Engine Distribution
It almost goes without saying that VB ELF relies heavily on the Microsoft Jet Engine for large portions of its functionality. When VB ELF standalone version is shipped, it contains an installation package to make sure that the Jet engine is fully installed on the user machine. For the developer version, we assume that Jet Engine distribution is already part of your application setup package. If it isn't, consider using the Visual Basic Application Setup Wizard. Another excellent choice is the Wise Installation system. You will probably want to include the Microsoft drivers for all available data formats, so that you can use their drivers to attach xBase, Paradox and Excel data sources. to a gateway Jet MDB (which can then be queried via ELF).

VB ELF Version 2.0 does not install OLE DB in all its variety on your machine (or your clients’ machines). We rely on you and Microsoft to have negotiated this hurdle together prior to calling on us to access these data sources! (Installing MDAC_TYP.EXE on a client machine is the easiest way to settle this issue.)

Graph Control Distribution
VB ELF uses the Pinnacle-BPS graph control as its graphing engine. As a VB developer, you have a license to distribute this control as part of your applications. You should already have this control (GRAPH32.OCX) since it is included in the Visual Basic environment.

The Setup Wizard won't detect VBELFOLE's dependency on the GRAPH32.OCX, so you'll have to add it to the distribution list manually. When you add GRAPH32.OCX, the Wizard will then automatically add the Pinnacle-BPS support library files, GSW32.EXE and GSWDLL32.DLL.

If you are installing to an NT 4.0 target machine, also include the file GSWAG32.DLL in the installation; in at least one instance it was reported that this file was needed to register the OCX (we're still not quite sure why).

In case of a mix-up, you should know how to register these controls without the good offices of the Setup Wizard. You simply need the four files REGSVR32.EXE, GRAPH32.OCX, GSW32.EXE and GSWDLL32.DLL. (Again, for Windows NT, you may also need the file GSWAG32.DLL.) Make sure these are all in the Windows\System (WinNT\System32) directory, then execute:
REGSVR32 GRAPH32.OCX
To unregister the Graph control, run REGSVR32 GRAPH32.OCX /u

See the complete component list at the very top for an exact accounting of the other required components.

The second change in this release is quite a large extension of the scripting capabilities which were introduced in version 2.0. For instance, scripts can now be stored in external files and included by using #include statements (eg. #include file="c:\scripts\myscript.txt"); global procedures can also be shared between different scripts by including the name of the pane in which they are defined (eg. #include name="SomePane") A number of new capabilities have been added which make it easier to customize scripts. Most of these features are used in the new, more extensive Web demos of VB ELF, and are documented there (www.elfsoft.com/demos.htm) along with examples showing their use. A comprehensive guide to the Scripting features is now also available at www.elfsoft.com/help/vbelf/Scripting.htm. This on-line documentation supercedes the Scripting topic in the VB ELF 2.0 Help File. A guide to the bug fixes and feature additions in v2.1 is also available at www.elfsoft.com/help/vbelf/Whatsup.htm

For instructions on using the demonstration programs in the VB ELF program group, please run the OLE_DEMO program (OLE Server Demo) and click the Instructions button. The other demos work similarly to OLE_DEMO.

Also, please note that the Delphi OCX demo has only partial functionality because it requires an out-of-process version of the VBELFOLE file (VBELFOLE.EXE, rather than VBELFOLE.DLL). Instead of increasing the download size of this distribution by 5 megabytes, we've chosen to let Delphi developers make the conversion themselves, by following the simple instructions in the Developer's Guide. Both in-process and out-of-process versions will be distributed on the CD, so if you require the full-featured Delphi OCX but don't wish to recompile our code, you can also simply wait for the CD distribution.

VB ELF 2.0 is being released as Visual Basic 6 source code only. Because Version 2.0 takes advantage of many of the new features of Visual Basic 6, it can only be recompiled and modified by developers with access to VB6. However, it can of course be used, as shipped, without recompilation or modification.

VB ELF also utilizes a number of controls supplied both by Microsoft and third-party providers. Each one of these tools is bundled with VB6 and available on the VB6 (or Visual Studio) CD. In some cases, because Microsoft is trying to promote the use of its own controls rather than these third-party controls, it has "hidden" them in directories where they must be manually installed. The following section describes the location of all the controls in use by VB ELF 2.0. Note that some of these controls have "Design-Time Licenses" and you must install by incorporating the .REG information accompanying the controls into your system using REGEDIT (Merge).

Version 1.2; Future enhancements schedule (revisited)

We promised some things in the v1.2 release, and while we’ve been able to add many new features, not everything we promised has come to pass. In some cases it reflects a change in direction, while in others it’s just a temporary delay. Let’s review the features that were slated for 1.2 release.

Anaphoric reference -- referring back to previous query, as in "Which of those are red?".
This has been postponed to the v2.0 release.

Query-Using-Form, in other words, "Show the customers from England using the XYZ Customer Form." This kind of query would read all the fields for the display from a prepared form already in the database.
This has been incorporated into our Access ELF product for this release cycle. We have been unable to successfully access the forms in an Access MDB via OLE Automation, in order to replicate them on-the-fly as VB forms. This has been such a popular new feature in the Access ELF beta trials that we will continue to try to make this available under VB ELF; indeed, we've reserved the screen space for this feature on the Responses Tab of the Settings dialog. We expect this will also be included in v2.0.

Graph control that can display over the Internet.
We’ve delivered on this one, in full.

Multiple aggregates in the same query; eg.
Show the average price and the average discount by category.
We were not able to include this additional capability in a fully general way, but the addition of the new Worksheet mode makes the creation of these compound calculations far easier. See our Web Tutorials for more on using this new feature.

Tighter integration with reporting tools, such as Crystal Reports and ReportSmith.
This has dropped in priority, as we have begun to support more complete reporting styles within the application itself. We will continue to look at improving Import/Export, rather than binding to specific reporter products.

Adding the current grammar editing and debugging tools found in Access ELF to the VB ELF environment.
We have changed direction on this. With the flow of technology emerging from Redmond and elsewhere, it is simply impossible for us to duplicate, from scratch, the complex functionality embedded in the debugging functions of Access ELF and still keep up with the required changes. Moreover, it’s now pretty much unnecessary since 1) We’ve decided to bundle Access ELF 97 with every copy of VB ELF. And 2) Microsoft is clearly aiming to integrate their SQL Server and Access 2000 platforms in the future, meaning that we may eventually wind up with a single product anyway. In the meantime, because interfaces created with the Access ELF 2.0 release are bit-for-bit compatible with the interfaces created by VB ELF (in fact, they use the same ELF Language Engine DLL), you can create custom-tailored interfaces using Access ELF and simply save them into the active VB ELF directory.