ODBCConfigureData

Description: This function configures an ODBC data source and returns its error code.

Returns: Numeric

Usage: Script

Format: ODBCConfigureData(Mode, DriverName [, Settings])

Parameters: Mode { numeric } { required } { no default }

Any numeric expression for the mode as follows:

Mode	Meaning
0	Add data source
1	Configure data source
2	Remove data source

Note: In order for 64-bit VTS to work with 64-bit data sources, add 64 to the Mode parameter. 32-bit VTS cannot configure a 64-bit data source.

DriverName { text } { required } { no default }

Any text expression for the ODBC driver name, as configured in the ODBC setup menu under Microsoft Windows™; in the case of Excel, use "Microsoft Excel Driver (*.xls)".

Settings { text } { optional } { no default }

A variety of optional parameters that are text expressions giving the variable and value pairs used to specify the configuration of the ODBC data source. Any number of these parameter pairs may be used by simply listing the text string containing the variable name, followed by the value that the variable is to be set to. The variables to use include:

Settings	Description
DBQ	Name of the workbook (database) file
DefaultDir	Workbook directory
Description	Text description for data source
Driver	Path to the driver .DLL
DriverID	Integer ID for the driver (see below)
DSN	Data source name
FileType	File type
FirstRowHasNames	Sets if first row contains column names
MaxScanRows	Rows to scan in setting data type of column (range is 0 - 16)
ReadOnly	Sets the database file as read only

If the DriverID option was selected, the Excel driver values that may be used are as follows:

Value	Driver
534	Microsoft Excel 3.0
278	Microsoft Excel 4.0
22	Microsoft Excel 5.0/7.0
790	Microsoft Excel 97

Microsoft SQL Server requires additional keyword-value pairs. It uses the keywords in the following table. Note that only the SERVER keyword is required for this function; all other keywords are optional.

Keyword	Description
ADDRESS	The network address of the SQL Server database management system from which the driver retrieves data.
DATABASE	The name of the SQL Server database.
DESCRIPTION	A description of the data in the data source.
LANGUAGE	The national language to be used by SQL Server.
NETWORK	The network library that connects the platforms on which SQL Server and the SQL Server driver reside.
OEMTOANSI	Enables conversion of the OEM character set to the ANSI character set if the SQL Server client machine and SQL Server are using the same non-ANSI character set. Valid values are YES for on (conversion is enabled) and NO for off. The default value is set by using the SQL Client Configuration Utility.
SERVER	The name of the network computer on which the data source resides.
TRANSLATIONDLL	The name of the DLL that translates data passing between an application and a data source.
TRANSLATIONNAME	The name of the translator that translates data passing between an application and a data source.
TRANSLATIONOPTION	Enables translation of data passing between an application and a data source.
USEPROCFORPREPARE	Disables generation of stored procedures for SQLPrepare. Valid values are NO for off (generation is disabled) and YES for on. The default value (set in the Setup dialog box) is YES.

Comments: If the data source already exists, it will be reconfigured as per the specs given and "0" (no error) will be returned.

The data source name may not contain apostrophes or an ODBC error will occur; the function will return the error code.

Some types of ODBC data sources, such as Microsoft™ Excel, do not require the file to be created prior to executing SQL commands on the data source, but will create a blank file when the first SQL Create Table command is executed. Others, such as Microsoft™ Access require the file to be created prior to execution of any SQL statements.

Please note that configuration of ODBC Data Sources requires write permission to the registry key "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI". If the current user doesn't have write permission to the key, then the string "Insufficient permissions" will be returned. If running in Windows Vista, this means that VTS must be run as Administrator for the function to work.

The table below identifies some possible errors and their meaning:

Error Message	Significance
General Installer Error	An error occurred for which there was no specific ODBC installer error.
Invalid Type of Request	The Mode parm was something other than 0, 1, or 2.
Invalid Driver or Translator Name	The DriverName parm was not a valid ODBC driver name (found in the registry).
Invalid Keyword-value pairs	One of the Settings parms contained a syntax error. For example, spaces are not permitted around the equals sign in the keyword-value pair, nor are certain illegal characters (listed below under the "Invalid Data Source Name" error).
Request Failed	The installer could not perform the operation requested.
Could Not Load the Driver or Translator Setup Library	The driver setup library could not be loaded.
Out Of Memory	The ODBC installer could not perform the function because of a lack of memory.
Invalid Data Source Name	The length of the data source name exceeded the maximum length of 32 characters, consisted entirely of blanks, or contained one of the following illegal characters: [ ] { } ( ) , ; ? * = ! @ \

Examples:

{ Create a data source }

If ZButton(10, 30, 110, 10, "Create DS", 1);

[

{ Make sure name doesn't contain apostrophes }

Replace(DSName, 0, StrLen(DSName), "'", "");

ODBCConfigureData(0, "Microsoft Access Driver (*.mdb)",

"DSN", DSName,

"DefaultDir", Concat(DSDrive, DSPath),

"DBQ", Concat(DSFileName, ".MDB"),

"Description", DSDesc,

"FileType", "Access",

"ReadOnly", 0);

]

{ Create the data source's file }

If ZButton(10, 70, 110, 40, "Create file", 2);

[

ODBCConfigureData(1, "Microsoft Access Driver (*.mdb)",

"DSN", DSName,

"CREATE_DB",

Concat(DSDrive, DSPath,

DSFileName, ".MDB"));

]

{ Delete the data source }

If ZButton(10, 110, 110, 80, "Delete DS", 3);

[

ODBCConfigureData(2, "Microsoft Access Driver (*.mdb)",

"DSN", DSName);

]

{ Create a MS SQL Server data source }

If ZButton(10, 150, 110, 120, "Create SQL DS", 3);

[

ErrorCode = ODBCConfigureData(0, "SQL Server", "DSN",ODBCName,

"Description",

"SQL server connection created by

VTS.","SERVER",

ComputerName, "NETWORK",

"DBMSSOCN" );

]

To remove a 64-bit data source (using 64-bit VTS):

ODBCConfigureData(66, …)

To add a 32-bit data source (using either 64-bit or 32-bit VTS):

ODBCConfigureData(0, …)