SQL PERVASIVE CREATING INDEX GIVES ERROR IN VB6

I use this to create table , works ok
        mystring = "DSN=BIAPRO"          
        dbconn.Open mystring '
        adocat.ActiveConnection = dbconn
        On Error GoTo NoExist                'IF NOT IN TABLE not to delete thus
        klant$ = "MUT01"
        dbconn.Execute ("DROP TABLE " & klant$)

NoExist:
'======
    On Error GoTo Foutje
    strSQL = "CREATE TABLE " & klant$ & "("
    strSQL = strSQL & "MutRecord identity,"
    strSQL = strSQL & "MutYear char(2) NOT NULL,"
    strSQL = strSQL & "MutKlant char(3) NOT NULL,"
    strSQL = strSQL & "MutBoek char(2) NOT NULL,"
    strSQL = strSQL & "MutStuk char(6) NOT NULL,"
    strSQL = strSQL & "MutDate char(6) NOT NULL,"
    strSQL = strSQL & "MutPlace char(1) NOT NULL,"
    strSQL = strSQL & "MutRek char(4) NOT NULL,"
    strSQL = strSQL & "MutBtwFlag char(1) NOT NULL,"
    strSQL = strSQL & "MutBedrag FLOAT NOT NULL,"
    strSQL = strSQL & "MutBtw FLOAT NOT NULL,"
    strSQL = strSQL & "MutReden char(55) NOT NULL,"
    strSQL = strSQL & "wenkilos real NOT NULL,"
    strSQL = strSQL & "MutNewkey char(13) NOT NULL,"
    strSQL = strSQL & "FREEMORE char(151) NOT NULL)"
    dbconn.Execute (strSQL)                 '   ctreate tabel first


but then i need to create index, I do like this in 3 steps

 strSQL = "CREATE UNIQUE INDEX MutRecord ON MUT01 (MutRecord)"
 dbconn.Execute (strSQL)
'''GIVES ERROR  ""

 strSQL = "CREATE INDEX MutNewkey ON  MUT01 (MutNewkey)"
 dbconn.Execute (strSQL)
'''GIVES ERROR  ""

 strSQL = "CREATE INDEX MutBoek  ON  MUT01 (MutBoek)"
 dbconn.Execute (strSQL)
'''GIVES ERROR  ""

BIAPROAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Bill BachConnect With a Mentor PresidentCommented:
Did you try changing the index name yet?  Add a prefix like "I_" to the start of the index name to make it unique.
0
 
mirtheilCommented:
What error?  
You probably don't need to create the key on MutRecord since the field is an Identity and the field should already have an index.  

If you run the queries through PCC, do they work?  
0
 
BIAPROAuthor Commented:
Since I could create the table in my source, after that i entered manualy the index using the pervasive console,
BUT!  i have mut01  upto mut99  and preferre not to do this by hand 99 times

index.jpg
table-names.jpg
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Bill BachPresidentCommented:
Fyi -- since both field names and index names are stored in the same table (X$File), you cannot have indices with the same names as fields.

I echo Mirtheil's sentiment that you shouldn't need the first one, because of the Field type.
 
0
 
BIAPROAuthor Commented:
Ok did retry with only

the get error still:

error.jpg
0
 
BIAPROAuthor Commented:
skipped the 1st index, then got the attached error number
 strSQL = "CREATE INDEX MutNewkey ON  MUT01 (MutNewkey)"
 dbconn.Execute (strSQL)
0
 
BIAPROAuthor Commented:
Bingo!   that did  the trick!, and true I see the 1st key index generated without my call for key autoinc
Thanks again Bill,

Regards Jack (aruba)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.