Removing Access Index used by system 2

[1932gh]

I have an MS Access database with a table named Job which has a 6 byte field named JobID. When I click on "properties" in the Access design window, the "Ordered by" is Job:JobID.

My code is VB 6.0

I want to increase the size to 8 bytes
I create an 8 byte field called newJobID
I copy JobID into newJobID
I then try to delete JobID
db.Execute "DROP INDEX JobID ON Job;"
db.Execute "ALTER TABLE Job DROP COLUMN JobID;"

The ALTER statement results in an error message:
"Database Error 3280-->Cannot delete a field that is part of an index or is needed by the system"

If I REexecute the statement:
db.Execute "DROP INDEX JobID ON Job;"
it results in an error message indicating that that index does not exist.

If I look in the database, the field still exists and it is "Indexed"

For this table the JobID is the primary key
However, other tables behave in a similar way with the index in question NOT a primary key


thanks

gat

 

[PeterWallace]

If this is a ONE OFF task .....

Then do it in Access

Copy The Table Stucture & Data

Then Change Size of ID Col

Then Copy Data

Delete Old Table

Rename New

Peter

 

[SBerthold]

The Index probably has a different name than the field name if it was a primary key.

Open Access and the table in design mode, and under the Menu "View" click "Indexes" and see what the name(s) of the index for this field is.