I use VB 6.0 to create dbase III database. I did some investigating on how to do it, and found out that speed-wise it is better to use DAo 3.5 with dbase files when working through VB. I used VB to create a dbase table and associated indexes (which must be unique) using the following code:

Dim db As DAO.Database
Dim strSQL As String

Set dbvtt = DBEngine.Workspaces(0).OpenDatabase("C:\AVL\data", 0, 0, "dBase III;")
dbvtt.Execute ("Create table VehTrans (VehID LONG, vehIP string(20));")
dbvtt.Execute ("CREATE UNIQUE INDEX VehIDInd ON VehTrans (VehID);")
dbvtt.Execute ("CREATE UNIQUE INDEX VehIPInd ON VehTrans (VehIP);")

The statements above create 3 files in the source directory: .dbf and 2 .ndx(es), as expected. Than I try to add data, and this is where the problem comes:

dbvtt.Execute ("INSERT INTO VehTrans VALUES(12,'test1');")
dbvtt.Execute ("INSERT INTO VehTrans VALUES(12,'test1');")

I do not get any error message if I try to add the above data, even though the second record is a duplicate of the first, and should not be allowed.

Am I doing something wrong?

Also, I've read about the dbf file indexing. I found out that in FoxPro, for instance, in order to disallow the records with duplicate key values, you must set unique attribute on the field in the SQL statement when creating the database -- thus creating a candidate key. When I tried to set unique attribute for both of my fields in the create table statement:

dbvtt.Execute ("Create table VehTrans (VehID LONG Unique, vehIP string(20) Unique);")

I've got a run-time error "Error in create table statement"; it seems that this is not supported

Any suggestions??
Thank you very much