Access ELF FAQ (Frequently Asked Questions)
Questions about configuration . . .
Q) How do I uninstall Access ELF completely?
A)
To completely uninstall Access ELF:
1) Use Add-in Manager to uninstall Access ELF (click Uninstall).
2) Use Control Panel / Add/Remove software to remove Access ELF from your system.
3) Use Windows Explorer to find and delete all remaining copies of elf32.mda on your hard drive. Any remaining
copy will probably be the copy placed by Add-in manager into your AddIn subdirectory.
4) Remove ELF macros and reference from any ELF-enabled database (see next answer)
More details on this process (including how to search hidden AddIn directories using Explorer) is available in the uninstall section of the Setup topic.
Q) What changes does Access ELF make to my database? How can I uninstall ELF on one particular MDB?
A)
First, to reclaim the space taken by the interfaces you've built for this database, delete the .ELF subdirectories. These subdirectories will be located in the parent directory shown on the View tab of the Settings window. If the "Show Views matching this database only" box is checked, you'll be able to tell which .ELF subdirectories were created for use with the particular MDB file you've got open.
As of version 4.0, Access ELF makes only two kinds of changes to a database. One is to write into the database container the queries which are the translations of your question. These queries are usually named elfQ1, elfQ2 etc. They are meant to be temporary, in the sense that they're overwritten each time a new question is translated. Simply delete them as you would any other query which is no longer required.
The other change to your database is made, usually invisibly, each time Access ELF is loaded from the Add-ins menu. This change is to insert a reference to itself in the References list, and to copy (or restore, if missing) a set of macros into your database. These macros are used to trigger code within the Access ELF library whenever you click one of the Access ELF toolbar buttons. To delete these macros permanently, use Tools/Options. On the View tab, check the "System objects" box. Click OK to close the Options window and click on Macros. The ELF macros all begin with the words "Usys ELF".
To remove the reference to the ELF library (elf32), click Tools/References from within any module, uncheck elf32
and close and save the module. For more details, see the Remove References topic.
A) Access ELF should run on any system that can run Access. Because the Analysis process is highly processor-intensive, we also suggest running the Analysis using the fastest machine available.
Q) How do I use Access ELF on a low-resolution (640x480) monitor?
A)
To use Access ELF in low resolution modes, follow the steps outlined in the Scroll radio button topic. (This control appears only on low resolution displays.)
Q) Why does the Worksheet "strobe" (flash on and off repeatedly) as it's being generated?
A)
This problem has been observed under Windows XP. It's caused by the process that calculates the best display width for each column in the Worksheet. It's especially noticable on some flat-panel monitors, particularly with Worksheets containing many columns. You can switch this option off by unchecking the Best Fit preference setting.
Questions about the analysis . . .
Q) Are there any common database practices that interfere with ELF's ability to understand questions?
A) We suggest you avoid prefixing your fieldnames. Many Access developers use prefixes for all their types, for instance tblOrders, qryMonthlyOrders, fldOrderID, frmOrderEntry, rptQuarterlySales, mcrRunYTDTotals, modOrderProcessing. You might notice that, with the exception of the "fld" prefix for "field", all the others match tabs on the MS Access database window. We think there's nothing wrong with using these prefixes, if they help you keep objects straight in your application. But unlike the other objects, fields are often named explicitly by users when creating queries, and they're unlikely to abide by your naming convention. Access ELF does make every effort to "look past" the field prefixes if they exist, but it can become confused in some situations where it's trying to associate field names with commonly used expressions. Q) The data base where I have ELF installed gets updated once a month with sales infomation, usually monthly buckets, YTD figures, etc. As the year goes along, do I have to re-analyze the data base after every update?
A) You don't have to re-analyze unless
1) you change the database structures -- in other words, add tables or fields or
2) you add a lot of text data such as new clients AND you expect the system to recognize which field that data comes from.
Let's say you get a new client named Jones. Before you re-analyze, Show me all information about Jones won't work, but Show me all information where last name is "Jones" will work. After you re-analyze, both versions will work because "Jones" has been entered into the dictionary. If you're just adding numeric information like monthly dollars, there's no need to reanalyze. Q) When working with a large database, I'm getting a message that says "Exceeded dictionary capacity." What's my next step?
A) The error message means that the Analyzer underestimated the requirements of your lexicon. It's caused by having lots of long Text or Memo fields. There are two ways to address this problem. One is to block the Memo data from being entered into the lexicon in the first place. (See the excluding fields topic for more on why you might want to do this.)
The other approach is to increase the size of the lexicon. For this, use the Custom Analyze feature, and click on a value larger than the AutoSelect mechanism chooses.
Q) I have Access set up on a network drive and I'm trying to create interfaces for MDBs on other drives. I don't want the ELF directories to go into the Access directory because they're taking up too much space. Help!A) You can set the destination path of the .ELF directories by changing the setting of the Search In drop-down box on the View tab of the Settings window. By default, it says "Access ELF Directory", and all grammars get stored as subdirectories within your Microsoft Access directory. Beginning with version 4.0, these subdirectories are themselves contained in a subdirectory called "ELF". (Previous versions placed the subdirectories one level higher.)
If you select "Current Directory" instead, grammars get stored as subdirectories of whatever directory your MDB resides in. You can also choose to type-in a path, to place the .ELF grammars anywhere you want. The View display options apply only to the selected directory. Of course, this means that you won't get to see all your .ELF interfaces in one neat list, simply by unchecking the "Show Views matching this database only" box.
The same options for changing the default location of the .ELF directories is available on the Access ELF 2002 Analysis window which pops up when you click the Analysis toolbar button.
Q) How do I stop the Access ELF Query window from appearing when an analysis is complete?
A)
To change this behavior, you must set the HideQueryWindow property prior to invoking the analysis function. See the Express Unattended topic for details.
A) Access ELF has no problem will tables without relationships --assuming that answers to your questions don't need to reference more than one table at a time.
If the tables have implicit relationships, like the relational tables in Northwind, the matching keys have to be set via the Tools / Relationships window in Microsoft Access. This allows the required joins to be automatically generated by Access ELF later, when you type in questions. For instance, if you ask "which employees sold beverages", the link from the Employees table to the Categories table requires accessing fields of the Orders and [Order Details] tables.
By default, Access ELF will use the built-in relationship collection of the Access system to determine how tables relate to each other. This is how fields are selected as keys for the join operations that connect data from interrelated tables. Alternatively,youcan specify that a given Master query be used in place of, or in addition to, the Relationship map. Any joins that are shown in the master query will be added to Access ELF's respository of known links.
If you don't want to have relationships permanently saved into your application, but still want Access ELF to make the proper connections, you have two choices. First, you can set the relationships prior to running the analysis and delete them afterwards. Access ELF keeps its own records, so you don't need an accurate relationships map at the time questions are posted. Second, you can enter relationships directly into the Access ELF system tables by using the Joins button on the Analysis editor. For each relationship, there should be two rows:
| Function | Type | Arg 1 | Arg2 | Result |
|---|---|---|---|---|
| JOIN | C | TableName1 | TableName2 | KeyField |
| JOIN | C | TableName2 | TableName1 | ForeignKey |
The fieldname in the Result column must be a field of the table in the Argument 1 column. In many cases, the KeyField and ForeignKey have the same name, but if you link two tables for instance on fields Birthplace and City, make sure the fieldnames match the proper table.
Questions about the Access ELF GUI . . .
Q) The Query History form is always empty, no matter how many questions I enter!
A) You've probably neglected to turn on the question logging feature. When logging is enabled, the green light on the Log Queries button (Query History window) appears "lit". If the light is off, click the button -- or check the Log Queries check box on the Preferences tab of Settings.
Another way to view previous queries is to use the up/down arrows on the Access ELF Query window, or the Worksheet's query dropdown. But these methods only show you queries entered during the current session.
Q) How do I convince the History log function to record questions that aren't understood -- that is, which are not translated into SQL?
A)
Check the Log Failed Queries checkbox.
Q) How do I speed up the word lookups from the Lexicon Lookup window? Lookups are taking too much time on my largest databases.
A)
Read more about disabling the AutoBrowse feature.
Q) How do I interrupt Access ELF? For instance, how can I interrupt an analysis, a translation, or a graphics operation that's taking too long?
A)
Use the "break connection" button on our progress meter to cancel an analysis. The Cancel button on either the Query window or Worksheet will halt a translation. Use the Escape key to interrupt a graphing operation -- then wait until the blank or partially-completed graph appears before attempting to continue.
Questions about graphs and the Worksheet . . .
Q) How can I graph key values, like an Employee ID number?
A)
While we're not sure why you'd ever want to do this (maybe to show the inverse relationship between Employee Number and number of stock options?!!), here's how it could be done in Northwind. Open a blank Worksheet and add the Employees table. (Or click Open to clear out the Worksheet and start fresh.) The SQL is SELECT * FROM Employees; Now click Add. Highlight (click its header) and delete the first column of the new query (elfQ1.*), then add the following two columns: EmployeeID and LastName. Place the cursor in the EmployeeID cell, press <Shift-F2> to open the Zoom window, and change this entry to:
ID: Val(EmployeeID)
You'll find that when you click the graph buttons, Access ELF no longer objects to graphing the ID field.
Q) How can I use scale factors to "even out" graphs with multiple graphed-value columns?
A)
Please see the scale-factor example discussed in the Add button topic of Worksheet. Another related example is mentioned in the (retired) example of combined aggregates.
Q) How can I copy each type of graph to the Clipboard for transfer to other applications?
A)
With Pinnacle/BPS-style graphs, use the More button to open the window with Export options. Set the DrawMode to your choice of WMF or BMP formats, select Clipboard from the Copy dropdown and click Copy To. With Microsoft Graph 5 charts, activate the Graph program by double-clicking the chart. Then select Edit / Copy Chart. For Microsoft Graph 6, double-click the chart to copy it directly to the Clipboard.
Q) How can I make the worksheet display a graph on one side, but a totally unrelated grid on the other side?
A)
Here's a example of how (and why) you might want to do this. Let's say we're doing a presentation, using Access ELF to help display information quickly. We ask the question: "How many customers are there in each country?" To help our audience visualize the answer, we click the Split button. Someone asks a question concerning the 1997 quarterly shipments. (Maybe these two are related in some tangential way.) We want to display the [Product Sales for 1997] query, while leaving the Customers per Country graph onscreen. To do this, click the Drop button, once for each SQL statement in the original translation. (In this case, twice.) That clears the grid half of the Worksheet and prompt you to open another record set. Right-click the empty Query Designer pane, select Show Table, switch to the Query tab, and select [Product Sales for 1997]. Delete the placeholder (Field1) column, and double-click the * in the query's field list, to include all fields from [Product Sales for 1997]. Close and save the query. Now we have [Product Sales for 1997] shown on the left, and the graph of Customers per Country shown on the right.
Because the two halves are unrelated, you can't use the Grid, Graph and Split buttons to switch between full- and half-sized views of this mixed Worksheet. (If you try, you'll get a graph of 1997 sales, and lose the Customers graph.) But you can accomplish the very same thing by dragging the Slider control all the way to the left or the right.
Q) Is it possible to display two different graphs at once?
A)
Although you won't have the ability to customize each in round-robin fashion, you can create two quite independent graphs at once. Here's how it's done, using first the Worksheet then the Graph response style in tandem.
Q) How can I change the order of columns in a Worksheet graph?
A)
You can change the order of columns in several ways, all of which involve using the Chart panel in the Worksheet footer. One way is to first clear any pre-selected ordering (with the Clear button), and reorder grid columns using drag-and-drop. Or you can leave the grid as-is and specify field positions by the order they're listed in the the Chart pane's value/label selector.
Q) How can I add a title to the BPS style graph?
A)
Click the More button on the Worksheet footer to get access to the Title control.
Q) How can I drop columns out of a Worksheet?
A)
Press the Delete key after highlighting a column to remove that column. This will work for any columns except those introduced by the SQL star symbol, or those in a pivot table. For example, "Show the full address of the employees." creates a grid including the EmployeeID. To remove it, click the EmployeeID header and press Delete. In contrast, is we ask "Show everything we have on employees.", the grid also includes the EmployeeID (and much more) but none of the columns can be removed using the Delete key. They can be hidden, however. Highlight the column as before and press Alt-O then H. (Or select Format / Hide Columns from the Microsoft Access menubar.) For more on removing specific columns from pivot table graphs, see the Chart topic.
Q) How can I add fields to a Worksheet?
A)
To add fields into a Worksheet, click the Edit button or place the cursor on the first record and press the Insert key. This launches the Microsoft Query Designer on top of the current Worksheet query. For example, assume that we've defined supervisor as ReportsTo. We then ask, "Show the supervisor for each employee." and get a list including the ReportsTo, EmployeeID and LastName fields for each staff member. To see the name of the supervisor, we can do this: click on the first employee, press Insert and answer Yes to "Add fields to this worksheet?" Right-click the Microsoft Query Designer, pick Show Table, and add the Employees table. You'll now have two Employees tables showing in the Designer, the second labelled Employees_1. Double-click the LastName field from Employees_1, adding it into the fields to display. Scroll to the bottom of the first Employees field list, then drag the ReportsTo field of Employees (at left) and drop it onto the EmployeeID field of Employees_1 (at right). For extra credit: change the Employees_1 LastName entry to Boss: LastName Now close and save the query. The Worksheet will list each employee with the name of his boss.
To perform more complex calculations using hierarchical relationships such as Supervisor/Subordinate, see the Organizational Chart topic.
Q) Is there an easier way to size the Worksheet than aiming for those tiny header and footer arrows?
A)
To toggle the header and/or footer on or off, double-click any blank (dark grey) area within a grid. Double-clicking from the middle up affects the header, from the middle down, the footer. You can also cause the Worksheet to resize to fill the screen area, by double-clicking any blank area of the footer, for example next to the Export button.
Q) What's the fastest way to flip from one display mode to the next in the Worksheet?
A)
To move from Grid to Graph to Split display modes and back, use the <Ctrl+Tab> key combination. This works as long as the Worksheet (not the grid itself) has the "focus", so click on any header or footer control first, before using this "flipper" technique.
Questions about Views, Scripts and automating Access ELF . . .
Q) How do I associate a given view with a specific form?
A)
One excellent way to use Access ELF is to associate a different View with each important form in your application. Since each View can be highly customized, it may be better to use this technique than to try to have one interface answer all possible questions about the database. To synchronize a specific View with the open form, place the following line in the form's OnLoad event:
result = elfOpenView("MyViewName")
MyViewName will be the name of some View than you've created with an analysis of this database, or portions of it. Now each time this form is opened, questions entered into the Access ELF query window or Worksheet will use the rules of this View (including any attached scripts) to translate questions into SQL.
Q) Can I add a simple text box to one of my forms to allow users to enter natural language questions?
A)
Yes. It's possible to use Access ELF without opening the Query window or, for that matter, showing the Access ELF toolbar. Here's the simplest possible Access ELF application.
To initialize a shell application that will recognize Access ELF commands, open Access ELF from the Add-in toolbar. In Access 2002 that's all you need to do. (In Access 2000 you must also clear the Remove Ref on Exit checkbox.) Now create a query form with a single command button and text box, eg Command0 and Text1. Add the following to the forn's load event:
Private Sub Form_Load()
Dim result
elfSilentStart = True
result = StartAccessELF()
result = elfOpenView("MyViewName")
End Sub
The elfOpenView() line is optional if you're using the default View; ie the one with the same name as the MDB. This code ensures that Access ELF is initialized behind the scenes each time this form is loaded. To implement question answering, add one line of code to the OnClick event of the command button:
Private Sub Command0_Click()
Dim result, recorded as Integer
elfRespondToQuery([Text1], recorded)
End Sub
This will bring up a datasheet response to the question typed into textbox Text1 when Command0 is pressed. The result variable will return True if successful. (The recorded variable reports whether the question was saved into the query window's scrollable question list.)
To change the reply style, use the line AESetReplyStyle 7 for Worksheet style, placing this instruction before the call to elfRespondToQuery. Other styles are: 1 Datasheet, 2 single-record form, 3 multi-record form, 4 graph, 5 best guess.
Q) How can I run an analysis from program code?
A)
This code will open the Express/Custom Analysis window.
result = aetbAnalyze()
To run an Express analysis without pausing at this initial window, preset the elf32.ExpressOptions properties as described in the Express Unattended section.
Q) How do I use variables within scripts?
A)
See this example of referring to the User ID within a script.
Q) How can I change the Script language setting when opening a View that uses a different scripting language than the default?
A)
It's possible that developers adding scripts to Access ELF interfaces may have different preferences in scripting languages. Because the default scripting language is stored in the Access ELF library, you'll need to take special precautions to switch from one scripting language to another
Normally a Question script simply returns the name of the View to launch in its place. In a multi-script language situation, if the View that must be launched will itself trigger scripts, you must set the target View's script language before returning the name of that View (handing off control to that View). Setting the scripting language is done, via code, by passing an UPDATE SQL statement to a special SQL handler for scripts. Since the scripting language setting is held in the elfScriptingLanguage field of the elfDefaults table of the ELF library (also called the CodeDb), the SQL statement is:
UPDATE elfDefaults SET elfDefaults.elfScriptingLanguage = "JScript"
This SQL must be passed to the ExecuteSQLCustom function, specifying the CodeDb as the database to perform this update on. Here is an example of a Question script which simply hands-off control to a View called JView, which could be a View that runs its own (JScript) scripts. Before launching JView, this script resets the scripting language to the langage that will be used by the new View.
function ViewChange
Dim result
result = ExecuteSQLCustom("UPDATE elfDefaults SET elfDefaults.elfScriptingLanguage = ""JScript"";" , "Codedb")
ViewChange="JView"
end function
JScript and VBScript are the only two scripting languages currently supported.
Q) How can I debug my JScript scripts when I can't get alert() to function on my desktop?
A)
Some configurations seem to prevent alert(), confirm() etc. from presenting within Access. If alert() doesn't yield a message box when triggered from within a script, there's at least one workaround. To print debug messages to the screen, pass a MsgBox statement into Microsoft Office VBA by wrapping it into the Evaluate function, like so:
function JSDebug() // a typical JScript function, except it uses VBA!
{ var result;
alert('Hello world'); // if this statement isn't working, the next one will
result = Evaluate("MsgBox('Hello world')");
}
Questions about how to ask questions . . .
Q) Some of my queries have returned meaningless responses. For instance, "Which members are male?" or "Which members are female?" return the same answer, all the member records. Is there something I'm missing?
A) What ELF can't understand it ignores, so you probably need to add this to the Phrases table:
When I Type: male I Really Mean: Sex = "M"
When I Type: female I Really Mean: Sex = "F"
You can access the Phrase Editor via the ABC button on the Access ELF toolbar. Q) My field names are not exactly "natural language", so I get a few unpredictable results. What's the best way to handle this?
A) Again, try using the Phrases (ABC) button on the toolbar to open the Phrase Editor. There you can enter substitutions for the "un-natural" field names. For instance, you can enter:
When I Type: product I Really Mean: f_pdctKey1
Q) I cannot get Access ELF to understand my questions! For instance, I ask "How many mammals" and I just get "Sorry, unable to interpret the question."
A) Two points to keep in mind. Access ELF is unlike other query processors in that it expects true English input. So talk to it in full sentences: "How many mammals are there?" might work better. On the other hand, it's still a computer program and quite literal. So if mammals is a category in your MDB, Access ELF might count the categories like "mammal" and come up with an answer of 1! A better question is, "How many animals are mammals?" Keep experimenting till you get the feel of it.
One of the best parts of the ELF system is that once you've discovered a trick like this, it's easy to teach it back to the system. That means that you won't need to remember it. Even better, if you're designing systems for other users, it means that they may never encounter the problem in the first place.
For instance, you could easily add a phrase definition to fix this problem. The most straightforward way would be:
When I Type: [how many mammals are there]
I Really Mean: how many animals are mammals
Of course, if you're this specific, you'd have to do the same thing for the Birds, Reptiles, etc. Assuming that Mammals, Birds, and Reptiles are all entries in a field called [Type of Animal], you could solve all these problems at one time.
When I Type: [how many {Type of Animal} are there]
I Really Mean: how many animals are {1}
Q) How come there are brackets around the When I Type section in the "How many mammals" example above, but no brackets around the word product in the previous example? Is it because product isn't a multiword phrase?
A) Actually, no. There are really two entirely different types of Phrase substitutions, and the brackets are used to choose between them. One is called "string" substitution, and it's used when the When I Type trigger is enclosed in square brackets (or quotes). String substitutions use the advanced features of the Phrase definition window -- for instance, they're applied in the order specified by the Sort Key. They can also take advantage of the kind of data-aware placeholder that was used when {Type of Animal} was inserted for Mammal, Bird or Reptile.
The other kind of substitution is always applied left-to-right as the words appear in a question. While compound words can still be used as triggers for these replacements, advanced features like data-aware substitutions aren't available. On the other hand, these simpler "word substitutions" often handle conjungated verbs and plural forms of nouns more flexibly. Since no advanced features are required for the f_pdctKey1 example, we simply left off the brackets. This means we don't have to do any special testing to know that both "product" and "products" will be replaced, just as we'd expect.
Q) How do I "break" an association between a word and a database field?
You're probably weren't expecting to see the customer residing at Garden House who purchased dairy products. To fix this, open the Lexicon Editor. Enter garden house into the lookup box. You should see a database entry (PNoun: DATA CUSTOMERS_ SHIPADDRESS) showing that this information comes from the ShipAddress field of the Customers table. To remove this association, click the PNoun radio button, then Delete. To the prompt "Do you want to delete the PNoun graden house?", answer Yes.
Q) How can I ask for multiple aggregates in the same question?
Q) How do I refer to a specific table and field combination?
Q) How do I ask questions about calculated data, such as queries, expressions and Access Basic functions?
Q) How can I write two phrase rules which apply to the same trigger phrase?
Q) How can I chain more than two phrase rules for the same trigger phrase?
Q) I understand that your phrase substitution rules let me change A into X if B is present and Y if not. On top of that, I can say to ignore the rule if C is in the question.
Q) Sometimes I can't quite make out why Access ELF has generated left or right joins. Is there an easy way to tell what effect these have?
Q) Using the Query Translation (SQL Text ) window, I can type in SQL that directly references functions in my database's library modules. This SQL works fine when I run datasheets and stand-alone graphs. Why do I get #Name? error columns when I try the same thing in the Worksheet?
But code written by you (or by others for you or your company), can't be understood by the Worksheet's SQL interpreter, unless those functions are packaged up and exported into a library of functions. Creating and then registering the export library with Access ELF only needs to be done once. After that you can simply add functions to this library, and have them instantly visible to Access ELF.
A function library is just like any other Microsoft Access database. It can either be in MDB, MDA or MDE formats. All that's required is that your functions be placed -- along with any code that they depend on, of course -- within this container (separate from the database that will host Access ELF). Finally, to make Access ELF aware of the location of the function library (or libraries), set a reference within the Access ELF library (elf32.mda) to your function database.
The elf32.mda library is located in your Add-In directory. The location of the add-in directory depends on which version of Windows you have. For instance, under Windows 2000, if your user name is John Smith, it may be Documents and Settings\John Smith\Application Data\Microsoft\AddIns. Under Windows 98, it's probably Windows\Application Data\Microsoft\AddIns.
A quick way to locate elf32.mda is to use the AddIn directory link on the About ELF window (copyright symbol on the toolbar). Close the About window and minimize Access, and you'll also be able to create a short-cut to your add-in directory, by dragging its folder from the left-hand panel of Explorer to your desktop.
Once you know where elf32.mda is located, close all other running copies of Access, then open elf32.mda using Microsoft Access XP (2002). (Or you can double-click elf32.mda in Explorer.) Highlight the Access ELF module and click Design. In the Microsoft Visual Basic editor, click the Tools menu and select References. You'll need to add your function library to the five references already there. Use the Browse button and locate the function library, first setting the "Files of type:" drop-down box to match the 3-letter extension of your function library. To add the reference, highlight your file and click Open.
A)
Sometimes Access ELF makes a connection that we didn't intend between a word or phrase and information in our database. You'll need to know how to make your interface "unlearn" these wrong judgements. For instance, say we expanded our food inventory to include home and garden equipment. Then we type in: Which are garden house and backyard products?
Ship Name Category Name address Company Name
Island Trading Dairy Products Garden House
Crowther WayIsland Trading
A)
Make sure to use an aggregate keyword (for example, "sum" or "average") for each field you want aggregated. In the Northwind database, you could ask: "Show total quantity and average discount for each product". However, the paired values should be at the same level of detail. For instance, the [Order Subtotals] and [Order Details Extended] queries define two calculated fields, Subtotal and ExtendedPrice. The ExtendedPrice calculates receipts off UnitPrice, Quantity and Discount. The Subtotals performs the same calculation, but of all the items in a given order. Subtotal is in effect the sum of the ExtendedPrices for each order. While "Show total Subtotals for each country." and "Show total ExtendedPrice for each country." yield identical sums, combining the two questions into a single question ("Show the total Subtotal and total ExtendedPrice for each country.") causes Access ELF to become confused between the levels of detail required. The Subtotal figures will be inflated because they will have been added in for each line item, not as should be the case, for each order.
If fields are part of the same table, it's safe to combine them in any query. Examples include "What is the average UnitsInStock and total UnitsInStock for each category?" or "What are the total units in stock and total units on order for each category?"
A)
Every query language has its peculiarities. One of the unintuitive things about the "natural" query language supported by the ELF processor is its use of table qualifiers. To refer specifically to a table (or query), you must append the recordset indicator to the end of the word. Th Employees table is referenced as employees_ just as order details_ refers to the [Order Details] table. Brackets, although they're understood by Microsoft Access, are not a part of the ELF query language..
Often it's not necessary to refer directly to tables, because the tables are inferred from the fields mentioned. Access ELF does a good job of deciding when a word means a table, and when the field of the same name within that table. In rare cases, when ELF just does not seem to grasp the meaning of your question, you may find it necessary to resort to "pointing" directly at the table you want. That is the purpose of the trailing underscore (recordset indicator).
As an example, a crosstab chart of the summed Order Subtotals (subtotal) by employee and country of order seems quite hard to coax from Access ELF. The Employee's country appears in many versions. Reverting to the explicit "Compare the subtotals showing employee's lastname, and customers_.country down the side." solves the problem nicely.
A)
Handling of queries and functions has much improved with this release. For more details, please consult the Phrases and Phrase Primer topics.
A)
The trick here is to use the square brackets for one rule, and double-quote marks for the other. Either of these bracketing symbols will denote a string substitution phrase. So you can write a pair of wules like this:
When I Type: "surname"
I Really Mean: [ Last Name | surname ] * employee
When I Type: [surname]
I Really Mean: [ Customer's ContactName, CompanyName | Supplier's ContactName, ContactTitle ] * customer
You must make sure that the rules fire in the order you want them to. In this case, we have three possible outcomes. The first rule handles one of the cases (Employees) and passes "surname" along untouched if "employee" doesn't appear in the question. To make sure this rule is triggered first, either use the quotes, as done here, or enter A in the key field for this rule and B for the "clean-up" rule. The second one decides whether to treat surname as meaning either the Customer's or Supplier's ContactName -- and include different information about each one. (Maybe we're already very familiar with our suppliers' companies, but not every customer.)
With these two rules, we'd get the following translations:
A)
Notice that the second rule of the two-parter above makes sure that "surname" gets substituted for no matter what. That being the case, it didn't really matter whether the trigger was "surname" or "emanrus" or Trigger705. So we can change the first rule to either use Lastname (for employees) or swap in Trigger705. Then we can write a two-part rule handling three cases (just as we originally did for "surname"), but this time for Trigger705. In effect, we've now got four different outcomes for the single trigger word "surname". And so on . . .
When I Type: [A]
I Really Mean: [ X | Y ] * B * C
But what can I do if I want to change A into X unless C is present -- never mind B!
A)
Take a deep breath. Then look at this:
When I Type: [A]
I Really Mean: [ A | X ] * C
Questions about special topics . . .
A)
In the SQL panel of the Worksheet, or the stand-alone SQL Text window, you can double-click on a LEFT, RIGHT or INNER keyword to change it to the next in the sequence. Rerun the SQL with the new orientation, and you'll see the difference.
A)
Expressions that use only Access Basic keywords can be employed with any of the response styles. An example of this kind of generic expression is:
Int(DateDiff('d',[Employees].[BirthDate],Now())/365.25) As Age
Note: This directory is Hidden, so it won't appear unless you specify "View hidden files and folders" as one of the display options in Windows Explorer. To change this setting, select Tools / Folder Options / View tab and click the "View hidden files and folders" radio button.
Select LastName, Security_Clearance(LastName,HireDate) FROM Employees
The next step would be to add this function definition to your interface, so that Security_Clearance will be available using plain-English questions.
Q) Is it possible to distribute Access ELF with my Access RunTime projects? If so, is this legal?
A)
Not only is it possible, but with our new licensing policy, it's completely free (and hassle-free). Because the Access ELF installation program is now fully redistributable, you don't even have to keep track of your distributions.
Follow these steps to include Access ELF with an application distributed with Microsoft's Access RunTime. (We'll talk about the simplest possible scenario, you can take it from there.) First, place a copy of the Access ELF library file, elf32.mda, into the same directory as the database you'll be distributing. For the purposes of this example, let's call it Northwind.) Open Northwind.mdb and open any module, then click Tools/References. Add a reference to Access ELF by browsing to the elf32.mda file now located in the same directory. If there's already a reference to elf32 (located in the AddIns directory of the development machine) remove this reference first, then add a reference to the "local" copy.
Step two is to make sure that there's a way to get to Access ELF's dialogs. In a RunTime situation, you can't start Access ELF from the AddIns menu. The best way is to import the Access ELF Toolbar directly into your application. To do this, use the Microsoft Access File/Get External Data/Import dialog. Open elf32.mda and, on the Import Objects window, click the Options >> button. Check off "Menus and Toolbars", uncheck "Relationships". That's it, you don't need to select anything from the Tables . . . Modules tabs. Just click OK and you're done. Now you can control when the Access ELF toolbar appears by setting the Toolbar property of any form in your application to "Access ELF".
Of course, when packaging your application using the Package Wizard, you should include elf32.mda. The Access ELF installation will also need to be performed on all client machines. You can either make this an integral part of your installation, or just instruct users to download and run the ACCELFXP.EXE installer.
The only features of Access ELF that will not operate in the RunTime environment are those features of Access itself which are disabled. For instance, you cannot use the Edit button from the Worksheet to open Microsoft's Query Designer, because the Designer is incompatible with RunTime mode. Another feature that Microsoft has seen fit to disable is the ability to adjust column widths. Because of this, the Best Fit checkbox on the Responses tab of Settings will have no effect.
We also recommend using the "Current Directory" choice for the Search In directory. This will simplify the process of distributing interfaces, since the .ELF folder(s) can then be placed in your application's own directory. However, if you wanted to centralize all .ELF folders (for multiple applications running under RunTime) you'd only need to make sure to create an empty ELF -- not .ELF (dot ELF) -- directory in the RunTime's home directory. That would probably be c:\\Program Files\Common Files\Microsoft Shared\Access Runtime\Office10\ELF.
Q) Is there some way I can get Access ELF to work applications distributed as MDE files?
A)
Access ELF cannot work with MDE applications. For one thing, the purpose of an MDE is to lock the application so that no changes can be made to it. While Access ELF does not make any substantial changes to a database, it needs to have the freedom to write temporary objects (such as the queries it's writing for you) into the database container. This basic conflict makes it impossible to use Access ELF with MDEs.
Q) Can Access ELF generate pass-through queries for other ODBC databases, such as SQL Server?
A)
No. Access ELF can generate SQL in Microsoft SQL Server's T/SQL dialect, but it can't pass the query though to the SQL Server processor. To query SQL Server databases using Access ELF, you'll need to use linked tables (set up to point at DSNs defined using the ODBC Control Panel options). You can also cut-and-paste SQL into tools such as the Query Analyzer. See this more detailed discussion of pass-through queries.
Q) Can Access ELF work with Access Data Projects in order to query SQL Server databases?
A)
Again, no. We may address this in future releases; however, for the time being, the only way to access enterprise data sources such as SQL Server, using our natural language system, is by upgrading to our ODBC / OLE DB conversant products. Please visit ELF Software's Web site for continuing updates.
Q) Is Access ELF designed for multi-user environments? Can it be placed on a local area network and accessed simultaneously by different workstations being served Microsoft Access data from the LAN?
A)
Yes. Access ELF 2002 supports up to 27 simultaneous users. All queries written into the ELF library, as well as temporary session information like questions and their SQL translations, are keyed to the individual user. Access ELF also has a robust self-monitoring system, to make sure that any temporary information which is not deleted at the time its owner logs off the system, will be removed shortly thereafter.
Q) How can I use Access ELF with replicated databases?
A)
The installation procedure for replicated databases is a little different than the usual. You can't install Access ELF on a Replica of a database, only on the Master. To move Access ELF into the Replica, you must synchronize the two copies. Once this has been done, Access ELF can be started within a Replica normally, by using the Add-Ins menu from the Tools dropdown.
Before beginning the synchronization, you should check to make sure that there are no queries in the Master that have the "elfQ" prefix (or any Custom prefix you choose on the Advanced tab). These queries are temporary work queries, which are usually deleted when closing down the system. If you've just used Access ELF within the Master (for example, to test), then some elfQ queries will show up in the Queries tab of your Access database window. You must delete these prior to the synchronization, or the Replica will be unable to store its own on-the-fly queries. (In other words, natural language questions won't work in the Replica.)
Once Access ELF is installed into the Replica in this way, you have complete flexibility as to where the analysis is performed. You can run analyses on the Master, then publish them by sending the .ELF directory to your clients. If you prefer, the analysis can be run on the replicated copy. Or you can provide a "suggested" interface with your application, but allow your users to wield all the customization tools built into the program at their option.
Access ELF will ignore the "Replica of" part of any database name, so you won't have to go to any special trouble to get replicas to recognize Views created in a Master, or vice-versa. However, if you have a different naming system for your replicas, you'll need to take reasonable precautions to make sure that Views you create for distributed replicas are recognized as belonging to that replica. For instance, if you make a replica of Northwind called NWCopy, the name of the View that Access ELF expects to find for that replica is "NWCopy". This means that you'd probably want to name the View you publish with this replica "NWCopy". Especially if your users don't have their own Access ELF license keys -- because the redistributable key is held within the View (which Access ELF won't otherwise be able to locate).
Another issue is that, with this kind of private naming scheme, Access ELF can't know for sure which of the Views in a directory are actually related to the open database. (This only makes a difference to which Views are shown on the Views tab with the Show Views matching this Database only checkbox set. It doesn't affect which Views can be loaded, because any View can be loaded by any database.) The compromise we use is to consult the Title property of the Database Properties Summary tab. If the two match (as they normally will for a Master and Replica) we'll assume they're related.
This works beautifully for the situation where you're publishing replicas and related Views. In the opposite case, where a client has created a View off one of your published replicas -- and, say, emails it back to you for debugging assistance -- the View won't appear in your View list. But in this case you know exactly what View you're looking for anyway. Just uncheck the "Show Views matching . . . box to find and load the problem View.
By default, Access ELF will set the field status of the following replication fields to NoAck (Do Not Acknowledge) during the analysis process: s_GUID, s_ColLineage, s_Generation, s_Lineage. This assumes that you will not normally want to make natural language inquiries about the system data used to implement replication. If this assumption is wrong and you're doing some special "meta-programming" experiment, reset the field status using the Field Selection window.
Here's one more suggestion for working with replicated databases. Assuming that no special customizations have been made on one copy, and that the data involved has not changed since synchronization, the same questions should be answered in the same way on either copy. If you find there is a difference, the first thing to try is to rerun both analyses with the Use Statistical Sampling option set to No. This will smooth out any differences due to conflicting "guesses" during the respective analyses.
Q) I'd like to make sure that, as I make adjustments to the ELF dictionary or settings, queries that once worked continue to work. Is there some way I can automate the process of checking?A) Yes. We use regression testing to check all our test databases, and the test functions are available to you as well. Just create a Table called elfTestSuite with text fields Query, [First Cell] and [Last Cell]. Then create a macro with action RunCode =elfRunTestSuite( ). Add the queries you want to check into the elfTestSuite Table, and cut and paste the first (upper-left) cell of the datasheet response into the [First Cell] field. Paste the last (bottom-right) cell of the response into the [Last Cell] field. Now open the ELF Query window and run the elfRunTestSuite macro. All the queries in the table will be processed, and the datasheet answers will be checked against the stored values. Any differences will be flagged with a message box. As you make changes to the system, you can continue to run this check to make sure everything keeps working.
We also have an advanced Regression Test tool available for more rigorous testing environments. This is a database application which can accept a table of databases. Each database will have been set up with its own elfTestSuite table. The regression test tool will cycle through all databases creating a detailed log of the differences between the recorded (expected) and current observed results. It also logs various other variables of interest such as time elapsed, database sizes, and so on. See the list of currently available tools, or contact ELF Software for more information.
Q) How can I distribute a modified Access ELF library? For instance, if I want to disable the scripts button within the elf32.mda library.
A)
We suggest distributing modified libraries as a two part process. First, have the user install the publicly-available copy of Access ELF which can be obtained from the ELF Software site. Make sure this step is performed before your customized installation process. As part of your installation, overwrite the elf32.mda library written into the Microsoft Office directory. When your user goes to install Access ELF in the Microsoft Access Add-In Manager, he'll be grabbing your modified copy of the library instead.
Modified libraries may be distributed only to specific parties (such as your clients) who have been instructed not to distribute them generally. They may not be posted on freeware boards or otherwise made available for unlimited redistribution.
Q) What is the correct state of the References for the elf32.library? I've been experimenting and want to make sure I'm back to the factory defaults.
A)
Access ELF 2002 ships with five references, in this order:
All but the last will appear by default in the list of available references for any Microsoft Access 2002 installation (though you may have to scroll a bit to find them). The last one is located in your Windows\System or WinNT\System32 directory as Mschart.ocx.
Last Updated: March, 2002