TExcelDSNCreator

Excel DSN Creation Component

Most recent updates to this and other files can be found at: http://www.gwacc.com/software.htm

Registration can be done at Kagi.com, via
http://order.kagi.com/?7HA&S

What is it?
This component is used to Add, Reconfigure and Remove Excel Datasource Names (DSNs) on the fly. Usually this is done via the ODBC32 control panel applet in any standard windows installation.

Why use it?
Ever needed to create an Excel Datasource Name (DSN) on the fly from inside your Delphi code? We certainly have! This incredibly useful little Delphi component allows you to create, modify and delete DSNs at runtime from a Delphi application (32 bit only).

Why would you want to do this? Quite often we (as developers at GWAcc) find ourselves having to climb into the control panel and muck around inside the ODBC32 applet. It gets annoying, especially if you have to do it several times in a row, even more so when it means having to switch between changing user names, passwords, paths, exclusivity... you get the idea!

This control allows you to add or change all the properties of an Excel DSN, in a single movement.

Simply set a couple of properties, add ONE line of code to your application, and you can Add, Reconfigure and Remove Excel DSNs in a flash!

An example application has been included with the component, that you can use to check the assignment of properties and invocation of methods.

Properties

About : Displays an 'About' box, with your registered status and our contact details

Action : TDSNExecuteAction
The action to perform when the Execute method is invoked. The action can consist of the following values:

• ADD_DSN - Add data source
• CONFIG_DSN - Configure (edit) DSN data
• REMOVE_DSN - Remove data source
• ADD_SYS_DSN - add a system DSN
• CONFIG_SYS_DSN - Configure a system DSN
• REMOVE_SYS_DSN - remove a system DSN
• REMOVE_DEFAULT_DSN - remove the default DSN

DSNNameOrAlias : String
The name for the DSN as it will appear in the drop down list of aliases available to the development environment. This is the internal name used by the ODBC drivers. No spaces to occur in this string. The component will accept them, but then if you try to modify the DSN via the control panel, it might get upset with you. Example: "TstDSN"

DSNUserDescription : String
The name that you will refer to it by, kind of like a comment string. After all "My Test DSN" is a lot more meaningful than "TstDSN".

DatabasePath : String
The fully qualified path to an existing Excel Worksheet file (if it exists). The control does NOT check to see if the file already exists, since you may want to create the Spreadsheet on the fly as well, either prior to or subsequent to using this control.
Example "c:\testapplication\test.xls"

DefaultDir : String
The fully qualified path to an existing directory of Excel file(s) (if they exist). The DSN only points to the DIRECTORY, not an Excel file. The control does NOT check to see if the Directory already exists, since you may want to create the directory on the fly either prior to or subsequent to using this control.
If you already have a directory set up with the Excel spreadsheet files in it, you can use the Object Inspector to locate these files. Note that only the path name is stored in the component.
Example "c:\testapplication\Sheets\"

UserID : String
Some Excel databases require a login and a password to be able to open them. This is the login name.
Example: "JoeSoap"
In the case of the Excel DSNs, UserID appears to have no effect on actually granting access to the tables when they're opened. It has been left in as a property to cover future possible expansion

Password : String
Some Excel databases require a login and a password to be able to open them. This is the password that must be supplied if necessary
Example: "TestPass"
In the case of the Excel DSNs, Password appears to have no effect on actually granting access to the tables when they're opened. It has been left in as a property to cover future possible expansion

The following extended options are directly applicable to the DSN type and affect how the ODBC32 drivers will handle your databases. You can check most of these values by opening control panel, running the ODBC32 applet, double clicking any Excel DSN and looking under the advanced options for it.

optImplicitCommitSync : String (Yes/No)

optExcelVersion : TExcelVersion (Excel3, Excel4, Excel5, Excel95, Excel97, Excel2000)

optMaxScanRows : Integer

optFirstRowHasNames : Boolean
optDSNReadOnly : Boolean
optSafeTransactions : Longint
optThreads : Longint
optUserCommitSync : Boolean
In the case of the Excel DSNs, optDSNReadOnly, optSafeTransactions, optThreads and optUserCommitSync appear to have no effect. They have been left in as properties to cover future possible expansion

The full list of the Enumerated Constants (TExcelVersion ) can be picked up from the relevant property in the Object Inspector.

back to top...

Methods:
This component only has one method.

Execute : Boolean
This method triggers the action you defined by the Action property above. Some of the properties may not actually need to be used, for example with the REMOVE_DSN action, only the DSNNameOrAlias needs to be supplied. The action is synchronous, and the function will return only after it has attempted the action you requested.
Returns: True = Your action succeeded, False = Your action failed (for example trying to remove a nonexistent DSN).

Note: For unregistered users, if the DSN already exists, it will return 'False' and the DSN will not be updated. It will only return true and create the DSN if it did not already exist. Unregistered users will need to delete the DSN via the control panel ODBC32 applet before it can be recreated. This is not an issue for registered users or for those who have bought the source code.

Events
This component triggers no events

back to top...

Installation

First, if you have any older or shareware versions of this library, remove all references to these first, this includes removing the package ExcelDSN_Dxx.bpl from your installed packages list. Also check that the path to any of the units has been removed from your library path.

Unzip the package you downloaded.
In Delphi, go to "Components" | "Install Packages" menu item, click on the "Add" button and select ExcelDSN_Dxx.bpl file from the list.
A new page will appear on the component palette (GWACC) with the component(s) on it. In order to compile and run the included example application, as well as any other application you create using this component, you will also need to add the path to the .dcu files to your environment search path.

To do this in Delphi, first locate the file it's looking for (for example c:\Exceldsn\Exceldsncreateunit.dcu), and then by going to "Tools" | "Environment Options" | "Library" and adding the path to the "Library Path" settings. If you don't add the path to the Delphi Library Search Path, you'll get an error when compiling an application that the compiler can not find 'ExcelDSNCreateUnit.pas' or 'ExcelDSNCreateUnit.dcu'.

If you bought the source for the software, simply go to "File" | "Open" in Delphi and select the "ExcelDSN_DX.dpk" package. This was designed for Delphi 4, but will work fine in Delphi 5 and should work for other versions such as Delphi 6, and Borland C++Builder too. In this case it will ask you if you want to upgrade the package to the new format. Answer "Yes", and then click on the "Compile" button. After this is complete, click on the the "Install" button to install the component on the GWACC page (default) or on the page you selected. See above about adding the path to the units to the Delphi library search path if you get any errors during compilation.

1. When you compile, you may get a compile error: File not found: 'DesignIntf.dcu'
2. Open Tools > Environment options > Library, and add a path to ToolsAPI, like this: $(DELPHI)\Source\ToolsAPI
3. After adding that file, if the compile stops in DesignEditors at:
   uses DesignConst, Consts, RTLConsts, Contnrs, Proxies; File not found: 'Proxies.dcu'.
4. In DesignEditors.pas, comment 'Proxies' out, like this:
   uses DesignConst, Consts, RTLConsts, Contnrs;//, Proxies;
5. In DesignEditors, locate:
   function CustomModule.ValidateComponentClass(ComponentClass: TComponentClass):
   and comment out the code line:
   while IsProxyClass(ComponentClass) do ComponentClass := TComponentClass(ComponentClass.ClassParent);
6. In the 'edsncompreg.pas' file, replace the 'DsgnIntf' in the 'uses' clause at the top, with 'DesignIntf'
7. In the 'ExcelDSNCreateUnit.pas' file, replace 'DsgnIntf' in the uses clause with 'DesignIntf, DesignEditors'.
8. Recompile, and your problems should be solved

If you downloaded the shareware trial release, the file will be called ExcelDSN_DxU.bpl where 'x' corresponds to the delphi version you are using. The registered version will simply be named ExcelDSN_Dx.bpl. You will also need to add the path to the .dcu files to your environment search path. Do this in Delphi by going to "Tools" | "Environment Options" | "Library" and adding the path to the "Library Path" settings.

Operational Considerations

These comments apply to opening and viewing Excel 'Tables' via a DSN within an application. They are not DSN related, but ODBC and operational related.

1) TableName is a NAMED RANGE. This means 'A1:B23' will NOT work, but 'ThisRange' will . You MUST set a Named Range, otherwise it will give 'Illegal/Missing Table Name'. The first line is assumed to be the Field names. If not, it will assume F1, F2, etc.

2) If you use a TTable as the datasource linked to a DBGrid, text data becomes MEMO Fields. That is, numeric data is shown , but text data is shown as '(Memo)' for blank cells and '(MEMO)' for non-blank cells. Use a TQuery in preference and directly set the SQL statment based on the columns in the named range. The application shows the effect of using a TTable when we create the DSN, and a TQuery when we modify it.

3) Specifiying the DSN to the TTable / TQuery at design time. Thus when the form is opened in Design mode, and the DSN is specified in the TTable/TQuery at design time, it logs on to the Excel Server, and promptly locks the file. Of course then you get problems with 'file is already open' if you try to do ANYTHING with it outside of Delphi at the same time.

History of changes
March 6^th 2002. 1.0.0.2 First public release.

Future plans:
Apart from any possible bug fixes, there are no major changes planned for this component

Known bugs
There are no known bugs at this time. If you find some bugs, please report them to gwacc@kagi.com. The support email address gwacc@kagi.com is available for all your comments, questions and ideas. If you'd like to see some additional functionality in this component, please let us know!

Benefits of registration
By registering one or more licenses, you have the right to incorporate this component into as many applications as you like, royalty-free. If you decide to purchase the license for this, or any other GWAcc component, you will get one year of free upgrades. New versions of components will be available to you at significantly lower price after the period of one year (during which you get all new versions for free).

How to register
Visit http://www.gwacc.com/software.htm and register online. All major credit cards apply through secure Internet protocol. You can also register via fax, international mail order, bank wire or US Check.

License :
Please read carefully the EULA_license.txt file before using this software.

In addition to this, GWAcc grants you the license to distribute the shareware version ONLY of this component, however you see fit, as long as ALL the files are kept together in one archive. We will not levy any charges for your distributing of the shareware components in any format, electronic or otherwise.

You may NOT redistribute the registered versions of this software to anyone, except with the express, written permission of GWAcc Limited.

back to top...