Flex/ODBC Frequently Asked Questions Updated 03/12/2002 Q: My application is not directly listed in your help files or manual, and I'm having trouble getting it to run. Some applications have different needs of an ODBC driver than others. Since it is impossible to support every application that does not use the ODBC standard properly, we suggest that you use a supported middleware product. Microsoft Access installs with an MS Access ODBC driver. Most tools support using this driver (Jet engine interface), and Access works well with our ODBC driver. Using Microsoft Access, create a new MDB and create links to the DataFlex files you want to access with the unsupported tool. Then create an ODBC driver link (DSN) in the control panel, which uses the MS Access driver and points to this MDB. Then open the DataFlex files using Access as the driver, and it will in turn call our driver. Q: What is the difference between your ODBC driver and the one I have heard about from Data Access Corporation? They are opposites of each other. A bit of confusion has been caused by a new product from Data Access originally called the ODBC Driver. Data Access, who created the DataFlex product, is adding the ability of the DataFlex Runtime language to talk to ODBC drivers from Sybase, Oracle and the like. What this product actually is is not an ODBC Driver, but a DataFlex system that can talk to ODBC drivers, allowing DataFlex programs to read and write Oracle files, for example. Our product allows any ODBC enabled application to talk to DataFlex format files. Q: The installer refuses to start up. Our installer application cannot run if the VER.DLL and DDEML.DLL files cannot be found. Check that these files exist and are in your path, or use the /M option to install to non-standard windows directory names. Q: Can I use your driver on UNIX/Linux? This is a driver for Microsoft Windows and ODBC applications. PC's running Windows which are able to address data stored on a UNIX host as a virtual DOS drive letter (using NFS for example) will be able to use this data in Windows applications. Another thing to keep in mind is to make sure the DataFlex files are created in DOS format byte layout (Intel style). The DOS products need a certain format, and UNIX DataFlex will work with either style, so if files are formatted in non-DOS layout, they can simply be copied to a DOS system, rebuilt, and then copied back to the UNIX system. The UNIX applications will run as normal after the conversion. Q: I had the evaluation version working, now when I have installed a single-user code it no longer works. The single-user version opens file exclusively, so if another user (or even another application on this same machine) is using the file in question, the open will fail. Network users require at least the 4 user version if other users are in the file along with the ODBC driver user. The evaluation version is multi-user. If you are having trouble with the single-user driver on a stand-alone PC, contact our support department. Q: There is no data source for DataFlex in my control panel ODBC option after I install. You can add one manually by choosing the Add option. Q: Does the 4 user version count my other DataFlex users? No, just simultaneous users of the ODBC driver. Q: My registration code has a serial number, but there is no place to enter it. You have an old version before we had serial numbers. Get the latest version from your dealer or our Internet web page. Q: I installed a new version of FlexODBC and it is running as an evaluation version. All versions of FlexODBC start out as evaluation versions until you install your registration code in the setup screen from the control panel. Current versions of FlexODBC keep the registration code when you update the product. Q: I can't install the driver it conflicts with some file. Make sure *all* applications are closed when you run the installation program. This includes the Microsoft Office Shell, any desktop managers, and any mail programs, reminder programs, whatever. Make sure there is nothing in your startup folder. If you are in Windows 95/NT, bring up the close program screen (CTRL-ALT-DEL) and close all open programs except Explorer. Our install adds standard components to your Windows environment, and they cannot be in use when the install runs. If this does not help, see the entry below. Q: When I try to load the setup screen, it says I don't have enough memory. This happens in certain rare cases. We have seen it happen on laptop machines, and Cyrix chip based machines in particular, with no other apparent difference. Report this to your support person. Q: I can't find by an index using an ASCII field, numeric types work OK. Check the collate.cfg file and make sure it matches the collating sequence your data file is using. Some countries use collating sequences different than the USA settings. Q: There is a setting for the digits of years (4 or 2 digit) in your setup screen. I have some of both, how can this work? This setting affects writing of new data to DataFlex files and doing matches by date type fields. Queries from your data will automatically find and display both date formats in standard ODBC date format. You only need to change this option if you write to files or select by date, and you must choose one format. Q: Dates from my file return in a format different than what I want. Our driver returns dates in standard SQL date format. Most programs expect this universal format and you can change the way it displays in the Windows control panel date layout section. Q: I can't see data in any of my files. First, check that the driver itself is properly installed. During install of the driver we create a test set of database files with a data source name of DataFlex Test32. Start up our VBDEMO application from the menu and choose Connections/Add New Connection. An ODBC dialog will start up offering you various data source names. Double click on DataFlex Test32. Press the Tables button, you should see some table names in the grid. Now click Query and you should see standard DataFlex error messages from the FLEXERRS file. If you see this data your driver is properly installed. If you still have a problem, it could be with your data source. Check the DFPATH setting in the driver setup screen in the Windows Control Panel, and make sure it points to a valid directory name which point to the following files in the DataFlex system: *.CFG *.DAT *.K* *.TAG. The path should not be a relative path (starting with the current directory), but a full directory path to the subdirectory required. No period (.) characters should be used in the path. The reason for this is that you are running an application (Word, Excel) from it's own directory, not from your DataFlex directory, so relative paths do not work. One work-around, if you cannot change your FILELIST, is to set the starting directory of the application to your DataFlex directory, this will force it to work, but may affect other add-on programs. Reindex the file to test it for integrity with DFINDEX.EXE or DFSORT.EXE, or use the reindexing options under the DataFlex Support section of the manual. Check the filename.TAG file and make sure it does not include any "binary" characters such as end-of-file or line feed characters, especially at the end of the file. Make sure the TAG is in the same subdirectory as the DAT file. This is a limitation of the DataFlex API tool we use to read DataFlex files. If the file has more than 100 fields, this could cause errors in 16 bit drivers, which have smaller memory models. Select only the fields you need for the particular query you need to do. If field names in the designated data file contain "_" or "@" or "#" characters, this causes problems for some applications which assign these characters special meaning. Change the field names in the filename.TAG file or make a new set of modified TAG files for FlexODBC and point your DFPATH to this directory first. Some foreign countries use a comma as a decimal separator rather than a period. Some development environments cannot open files where this is the case. Try setting it to the USA settings in the control panel and try it again. Also try the decimal settings in our setup screen. This is a problem with the tool you are using, so you need an appropriate country specific version of your tool to change this back. Q: I can read a file, but I get errors when I try to edit/delete/create records. This typically happens because of one of the following: Invalid header - Some applications have used the DataFlex MAKE_FILE command to create data files. The data files created by this command are not compatible with the API that our program uses to access data files. If you open the desired file in DFFILE and re-save it without change, this will repair the invalid header. File read-only - This user on a network has insufficient rights to update a file on a network, or the file is marked read-only on disk. Overlap fields - These special type fields are supported by DataFlex but by no other system, so sometimes you will get the error "Multi-user conflict - another user changed the record" because this is an untyped binary field and the comparisons always fail. Simply remove the overlap type fields from your query. Q: I get a Syntax Error message or Lexical Element error and can't find the problem. Look at the SQL statement and check for the following: In the help file you will find the reserved words list. Make sure none of your field names is in this list, and if they are, enclose them in double quote characters (") Values to be compared or placed in a file must be surrounded by single quotes (') unless they are numeric, then they should not. Q: My application requires certain functions (create table) that give me a syntax error in your driver. We are adding new features all the time, check with our sales department on your particular needs. Q: In Windows NT I get the following error when closing the control panel: "An error occurred while Windows was working with the control panel file ODBCCP32.CPL". This occurs when the pathname is invalid or the collate.cfg file is not in the named directory. Try it on the Test32 path, that one should work. Q: I get the message "cannot open collate.cfg file or collate.cfg not found". Copy the COLLATE.CFG file from the default FlexODBC install directory to the same directory where you keep FILELIST.CFG - the API must be able to find this file. If the COLLATE.CFG does exist in the directory where the FILELIST.CFG is located, this usually indicates a permissions issue. Q: We get an error that FILELIST.CFG cannot be found. Make sure this file is in the same directory as your DAT, K1 and TAG files. If you can't find it, you may be using PowerFlex, which uses a file list named "pffiles.tab". Copy this file to the name FILELIST.CFG and you should be OK. Q: What is FILELIST.CFG and how do I change it? The FILELIST.CFG file contains the names of DataFlex data files that can be accessed by the DataFlex runtime. This is a binary file and cannot be edited with a text editor. If you have a program called DFFILE.EXE you can run that to edit the filelist.cfg. If not, we have included a utility program with FlexODBC to use in editing this file. Search for the file HEXEDIT.EXE program in our install directory and copy it to the folder, which contains your FILELIST.CFG, and type the following: hexedit FILELIST.CFG You will see a layout of the list of files, type over the filenames with the filenames you want to use. Keep a backup copy of this file just in case! Q: When the list of available databases comes up, is there a way to filter out certain confidential databases so they cannot be selected? Modify a copy of the FILELIST.CFG file and place it in a directory before the data directory in the driver setup path. The driver cannot open files that are not in the FILELIST.CFG. If you do not have the DFFILE program required to make changes to the FILELIST.CFG, use the instructions above for HEXEDIT. You can also use our Security functions, which prevent users from accessing certain tables. They will be able to see them in the list however, but will be unable to open them. We do this because programs like MS Access remember the list of files, and give errors if files appear to be missing. Q: Please explain how FlexODBC does multi-user operations. Similarly to how DataFlex does it. On an UPDATE command, a SELECT is run to find qualified records, when one is found, REREAD is run, then the record is re-qualified, the specified edit fields are changed, the UNLOCK command is run, then we move to the next record. On INSERT a LOCK is run, a new record is saved, and then UNLOCK is run. Q: How can I update a system (1 record) file value that could be changed by other users during the time between my SELECT and UPDATE commands? On your SELECT statement, extract the value of the field, lets say MAIN.INV_NO is found to have a value of "247" and you assign it to variable OldInv. Next you would create a variable NewInv and set it to "248", which is the old value plus one, which is what you want to set it to. Then run an UPDATE statement something like: UPDATE main SET inv_no = :NewInv WHERE inv_no = :OldInv If the number of rows updated is 1, the update worked. If not, the value changed, retry this entire process again until it works. Q: I need the Swedish/German collating sequence for files. These files can be obtained from your dealer or on our web site. Q: How can I find out more information about how this driver and ODBC work? There are many good books available on ODBC programming and SQL, as well as specific references on using ODBC with various languages and tools. If you want to see how the driver works, or you need to provide a log for our technical support department, go to the Windows Control Panel, click on the ODBC 32 icon, Click on the DataFlex Data32 data source, and click the Configure or Setup button. In the window labeled Trace File enter an ASCII file name like c:\mytrace.log. Click OK and exit the control panel. All calls you make to our ODBC driver will now be logged until you clear this filename. Note: Microsoft ODBC has a trace facility too, but this has no driver specific information about DataFlex and will not help us find your problem. Q: When I log in to the driver a FLEXquarters logo screen pops up for about 2 seconds and it goes away. Is it possible to turn this off? This license screen is required so that the registration name can be seen by the user in case the program is not registered to them. It only appears once per windows session. The best solution is to add a small VB program to the startup folder in windows so that the registration screen only appears when starting windows, and not when running your program. Our OEM version has a much smaller and quicker sign-on screen, but that is primarily for deployment of applications outside your company. Q: I can't find the DataFlex driver from Microsoft Office/Word/Excel. The default installation of Microsoft Office (typical or standard installation) does not include the helper application Microsoft Query. This component is required to access any ODBC database from within the Office package. Re-run the Office install program and choose Custom Installation, then choose Data Access tools, this will install Microsoft Query and related tools for accessing external data. It will not re-install the entire package or lose any documents or settings. Under Excel, an additional step is required to go to the Add-ins menu and check the box for ODBC to allow this to work. Microsoft Query is run by the application (Word, Excel) and allows you to choose the data you want to return to the document, and then Query will not be required again until you want to modify the data query. Remember that FlexODBC is an ODBC database to Office, and it will not show up next to dBase and Paradox, it is categorized as either SQL Databases or ODBC Data depending on your version of Office. Microsoft states that standard (non Office) versions of Word and Excel should work with ODBC drivers by using the ODBC Driver Kit, which can be downloaded from their web site at: http://www.microsoft.com. Q: Why do I get "Invalid Connection String" or other errors from Microsoft Word when returning data from MS Query? MS Query appears to work fine. This is a bug in Microsoft Word related to pathing. MS Query returns a connection string back to Word, which is separated by semi-colons. The problem is that they assume only one directory will be mentioned in the path to data files setting (DFPATH for us), and multiple directories are also separated by semi-colons, causing the problem. To work around it, change the DFPATH setting in the control panel under the DataFlex Driver to "." (a period only), and use a DFPATH environment variable loaded in DOS in your AUTOEXEC.BAT file. Q: Why is the driver so slow? FlexODBC is a lightning fast driver for DataFlex files using ODBC. The driver works with all front-end applications, which can access ODBC, some of which are faster than others. This question is almost never asked by our users using C as a front end language for ODBC, nor by report writer users with Crystal, or DBMS users with Delphi. These users will see immediately how fast the driver is because their applications are optimized for ODBC speed. It is almost always asked by users of Microsoft Access, whose first operation is to open the largest file they have in their DataFlex system in an Access Grid control. They notice that the grid comes up quickly, but if they page down to the next screen-full of records, it takes a while. Why Access is slow compared to other systems can be easily explained. Access is overloaded with multi-user functionality, meaning that features like updating all stations when a record changes on-line and other features are easily accomplished by the native Access file format. When Access uses ODBC however, it attempts to do the same thing. For instance, when you open a multi-user grid on an ODBC data file, it must read the entire file into memory before continuing. This continues in the background, and when you press page-down, Access waits until the file has been completely read before beginning another query of the entire file, starting with the last record of the previous screen. Other DBMS systems optimized for ODBC, such as Delphi, query only the number of records needed to fill the current screen with data, and does not continually refresh the database on the screen for no reason. Access only has this problem in Grid controls, which is unfortunately the first thing people use with our driver. However, if you create a query and send it as input to a report, you will see how fast our driver can run under Access. When programming with SQL (the transport language of ODBC drivers) it is a good idea to think of an SQL table as being a "cloud" of data with potentially unlimited records. You should therefore design queries and views of only the fields and records required for the current transaction or report, and not attempt to query all records in a given table, which may overload the system. DataFlex programs are programmed in a single record orientation, while SQL/ODBC is set based. Access attempts to read each result from a data query into memory, as if your data were a spreadsheet, and DataFlex files tend to be very large. Applications built with Access should assume that large files will be used and show only the data appropriate to the current transaction in a grid control. Microsoft Access is optimized to work with its built-in data manager only (MDB files), and any Access programmer who has used ODBC will tell you about the limitations Access has in this area. Other packages work very well with ODBC data drivers. If you would like to see what Access is doing that makes it appear to "lock up" while running a query, you may like to turn on our debug control panel. Details can be found later in this document. Q: Why does Access say there are only 101 records in the file, I know there are more. Access shows 101 records in a grid by default if no index has been specified. Choose an indexed field and sort (a-z) and it will show the full number of records in the file when it has finished reading the file into memory. Q: Microsoft Access will open our files fine, but when we re-open Access to use the files, we get an error saying that the files cannot be found. Microsoft Access does not support the use of multiple path names in a data source in ODBC. If you are listing multiple directories in your data source (DSN) change it to point to only one directory. If your data is in multiple directories you have several options. You can create a DFPATH environment variable and change the DSN to ".", or you can point to the directory where the FILELIST.CFG is found and fully qualify all file names with a full path. Q: Microsoft Access gives me the error "Record too large" on certain files, Why? Access is limited to a record length of 2000 bytes, DataFlex files can get as large as 16,000 bytes. Select only certain fields in a query to lower the size. Q: I can run the first session of Access/Delphi fine, but the second time produces an ISAM error. Certain development platforms make it hard on us because they re-open the same file in several sessions. If this problem occurs, try one of the following settings. Using windows Notepad, edit the file ODBCINST.INI and in the DataFlex Driver section try one of these lines: Deinitialize=Never Deinitialize=EndOfSession Deinitialize=EndOfDLL Q: I have executed a query in Access and it seems like the system is just hanging up. We have added a debug panel for anyone that suspects a problem with Access or bad performance on index jump-ins. To make it work put in the ODBCINST.INI section for the driver: DebugPanel=Yes This will turn the feature on and it will show interactively which index was chosen, the current record filename, and allow you to exit a runaway query. Q: How can I format DataFlex data as it appears in Microsoft Word? Search the Word Help topic: Format merged information To format numeric values in Microsoft Word: Formatted: This letter is to inform you that your current balance is $3033.00 and your sales to date this year are $3154.32 Source: This letter is to inform you that your current balance is { MERGEFIELD BALANCE \# $#.00} and your sales to date this year are { MERGEFIELD YTD_SALES \# $#.00} To show ASCII data as upper case on first letter only (if data is stored all uppercase): Formatted: Elizabeth Scott Source: { MERGEFIELD FIRST_NAME \* caps} { MERGEFIELD LAST_NAME \* caps} Q: I need a version of your demonstration program, which will work with web servers so I can test IIS applications on Microsoft Windows. If you need to test and demonstrate web your project, the best and easiest means of doing so is from the workstation using PWS (Personal Web Server) with a DSN set up for the network data using your current ADO scripts. This will work fine on an intranet basis. This will allow you to click on the demo dialog box, as PWS does not run as a background task or system service, therefore the demo box can be clicked past when using PWS. The demo version will not work with IIS for this reason. PWS is a component of the full installation of Internet Explorer. Q: Is FlexODBC year 2000 compliant? FlexODBC 2.5 32 bit version has been tested in years 2000, 2001 and 2025 under Windows 95 and Windows NT 4.0 with no errors occurring in our standard test suite. We have had no reported year 2000 related issues as of this writing. Our tests were run on fully Year 2000 compliant data with dates stored in full 4 digit year format (12/31/2001). Dates in your application data must use 4 digit years for our driver to work properly across the 2000 boundary, it will not work if your data is stored with 2 digit years. Check your FlexODBC control panel settings for details on this setting. If your data is stored as 2 digit years, you will need to upgrade to a version of DataFlex, which is Y2K compliant. See the web page www.DataAccess.com for a list of these and further upgrade details. Q: I'm having problems opening files in two different directories using two different FILELIST.CFG files. FlexODBC uses a product called the Data Access API to open, read and modify DataFlex files for maximum compatibility. Unfortunately this driver does not support multiple FILELIST's open at the same time. When you open a second FILELIST, it removes the first one from memory. To open files in two different paths, you can create a special FILELIST.CFG, which references both sets of files by their full pathnames, then change your DFPATH to point to this new FILELIST.CFG. If the two files have the same root names, make sure their "DataFlex Name" is different in the FILELIST.CFG so the ODBC tools can tell the difference between the two files. Q: I want to install FlexODBC on a number of machines; can it be installed in an automated fashion? Yes, pass the flag "/quiet" (lowercase) to the installer to accept all of the defaults. After installation you can programmatically set the INI and registry settings and optionally delete the help files and sample data. Q: How does FlexODBC determine the data path? The DFPATH variable is first read from DFB.CFG, then DFB.INI (If windows version), then finally the Environment Variable DFPATH. Q: I'm trying to use FlexODBC with Microsoft Internet Information Server 4.0 and get errors. The primary problem question we get about IIS has to do with security. If the system service user does not have enough rights to the directory where the DataFlex data is contained, you will get errors such as "collate.cfg not found" and "license count exceeded" because it cannot read the data or lock the files in the directory. Click My Computer, find and right click the folder, which has the DataFlex data in it, choose the security tab, and Add the user SYSTEM with full rights to this folder. If you still have trouble, try using Microsoft Personal Web Server, it does not run as a system service, but provides web services and can be used for demos until your rights issues are resolved. It also works with our demo license. Q: in my 4 user version of FlexODBC I sometimes get "User Count Exceeded" messages when only one user is logged in. If 2 machines have different path statements you can get the error. You must have full read/write/lock capabilities for the driver to create the license mechanism. If the license mechanism cannot be created the driver reverts to a single user license. Sessions stay open until the computer is rebooted. When used as a system service, make sure permissions are not an issue. Q: When I try to edit a record in Access 2000, I'm told the record has been changed by another user, but I'm the only user. When using MS Access 2000, you need to change the default behavior of Access 2000 under: Options (Pulldown) Advanced (Tab) Default record locking From "No Locks" to "Edited Record". Once that has been done, you will only be prompted if the record you have edited has been changed by another user or form, and if that is the case, Access will refresh the record and allow you to re-edit the changes you wish to make as expected. Q: How can I keep up with the latest news on FlexODBC? Subscribe to our announcement mailing list via e-mail. http://groups.yahoo.com/group/FlexODBC-announce/join Q: How can I get the latest version of FlexODBC? To download the latest version, go to the Internet page: http://www.flexquarters.com/