Create an Index 1

[LonnieJohnson]

Here is one that I am sure is very easy for you guys.

I need to create an index on a file. I am an Access/VBA programmer. I have a large dBase IV file linked to my application. My queries run slow. I don't jack about dBase. I do know that if you index a file (any file) you can get better performance. This file is in use by others.


My question:
Can I create my own index file to use? I ask this because when I link to it, a dialog box opens asking which index file I wish to use. If so, how?


I hope I don't sound too idiotic.

Thanks...



prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[RRiley]

If you are connecting via ODBC then the same verbage used in the DBMS you are using should work on the dBase Table. However, to create an Index on a Non-Rational Database Table such as dBase you do need to establish an exclusive lock on the table to perform an index routine. This means all users,except the programmer, must close the file. Then an exclusive lock can be performed, followed be reindexing. The dBase verbage is
Use xTable.dbf Exclus
Index on yField Tag yIndexname /(Unique)(dec)

If this does not work or the Index is unreliable, use the standard SQL index statement;
SELECT [DISTINCT] <column list>
FROM <table reference>
[WHERE <search condition>]
[ORDER BY <order list>]
[GROUP BY <group list>]
[HAVING <having condition>]
[UNION <select expr>]
[SAVE TO <table>]
CREATE INDEX <index name> ON <table name> (<column name> [, <column name>...])

Once the index has been created the index must be included in the query statement in order for the query to use the index. Also beware of creating indicies on multiple fields that have different data types. Each Column must match or be conditioned to match; For example -
Create index NameSeviceIndex on Employee.LastNameField + Employee.Str(yearsofservicefield).

 

[LonnieJohnson]

Thanks RRiley,

You da man. I did not think beyond the box into including it in the SQL statement itself. Thanks again. prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.