Monday, June 1, 2009

FAQs – May 2009

Index Property not showing accurately

When viewing the table properties page the Index property of a particular field may indicate that the field is not indexed even though the field is part of an index. In order to determine if an index exists for a particular field ARC compares the field name to the index expressions. If an index expression consists of the fieldname then the Indexed property is set to yes. This index is also checked to see if it is Unique or Primary and the appropriate Indexed property is displayed.

However, if the field is included in a complex index expression ( i.e. Upper(<fieldname>) ) ARC may not indicate that an index exists. In the screenshots below the LastName field index property is no, however, the LastName field is contained in the Name index (LastName;FirstName).

Index Property Index Expression

This index property does not affect how the Advantage server uses the available indexes. If the field is part of an index it can still be used to optimize filters even if ARC reports no index on the field.

Restoring a Database

With Advantage version 9.0 new default user groups were added. One of these new groups is the DB:Backup group. Members of this group have permission to backup the data dictionary. However, to execute sp_RestoreDatabase  you must supply the Adssys password for the dictionary you wish to restore. The sp_RestoreDatabase command can be run from a Dictionary or Free Table connection.

Setting Rights-Checking Off for ODBC

The Advantage ODBC Driver Setup utility does not have an option for setting the rights checking for free tables. By default Advantage clients check the network rights for free table connections. Therefore the ODBC clients require network rights to the free tables.

To change this setting so the client ignores rights and does not need permission to the share you must change this setting in the registry. User DSNs are located in HKEY_CURRENT_USER\Software\ODBC\ODBC.INI and System DSNs are in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI.

Open the key that matches your DSN name and add a new string (REG_SZ) value named RightsChecking. To ignore rights set the key value to OFF and to check rights set the value to ON. Additional information about the Advantage ODBC registry keys is available in the help file.

ODBC Setup Does not List all Available Character Sets

When creating an ODBC connection there is an option to select the Character Set you wish to use when making the connection. This is particularly important when connecting to Visual FoxPro (VFP) table types.


The list of character sets comes from the adscollate.adt table which is included with all of the Advantage 9.x servers and clients. This ODBC Driver Setup dialog attempts to open this table using the Advantage Local Server (ALS). If the table cannot be found or an error occurs when opening the table the dropdown will only display ANSI and OEM.

If you are creating a DSN using a remote desktop or terminal server connection then a 5185 error will occur and the table will not be opened. You can verify this by creating the DSN directly on the machine, the entire list of character sets should be visible. Otherwise make sure the adscollate.adt and adscollate.adm are on the machine.


Anonymous said...

You referred above to the Advantage ODBC driver administrator, which I do not think exists.

I think you meant the Advantage ODBC Setup Dialog, to which you referred correctly later. The driver administrator is a Windows MDAC component, in the environment pictured.

Chris Franz said...


Thanks for the comment. I changed the text to avoid any confusion.


Anonymous said...

Hi Chris,
What is the dsn-less connection string for ODBC driver 9.1 for connection to VFP tables and databases locally or remotely.

Thanks in advance

- noel

bbavare said...

Hello Chris
I have a few questions regarding accessing data using this type of connection
First it will ask me to free the table from the data dictionary,
once that is done i was able to successfully access the data using SSIS and add to sql server.
My concern is if i free it from the data dictionary will it affect my application ?
Any help is appreciated
thank you