Error when creating primary index

[Vatter47]

In order to be able to synchronize 2 databases, I need to convert an
autoincrement field to a standard integer field. I'm using the following
code:

Set tbl = catProj.Tables("Termine")
Set col = tbl.Columns("T_NR")
If col.Properties("Autoincrement") = True Then
tbl.Indexes.Delete "PrimaryKey"
col.Name = "xID"
set col = Nothing
CreateColumn catProj, tbl.Name, "T_NR", adInteger
DoCmd.RunSQL "UPDATE [Termine] SET T_NR = xID;"
tbl.Columns.Delete "xID"
CreateIndex catProj, tbl.Name, "myPrimaryKey", "T_NR", _
adIndexNullsDisallow, adSortAscending, True, True
End If

This all works fine until the new index should be created. I get the
following error message:
Error -2147467259: Index or primary key cannot contain a Null value

Since the old field was the primary index field the new one cannot
contain any Nulls.

Where is my problem?

Thanks for helping
Walter

 

[Douglas J. Steele]

Have you checked to make sure that the Update statement is actually working?

 

[Vatter47]

Have you checked to make sure that the Update statement is actually working?

Yes, and there is no problem in creating the primary index manually
afterwards.

This works with a different table where the index field is of type Text
and sometimes it works with other databases.

Could this be a timing problem?

 

[Vatter47]

Yes, and there is no problem in creating the primary index manually
afterwards.

This works with a different table where the index field is of type Text
and sometimes it works with other databases.

Could this be a timing problem?

I have now reintegrated the referenced mdb which contains the
CreateIndex subroutine into the database and now type Text does not work
anymore as well.

 

[Vatter47]

Having looked at this problem again I have now found that the code works
fine when stepping through one by one in debug mode.
Using catProj.execute instead of docmd.runSQL produces the same error.

It seems that I have to find a way to test for the Update query to
complete before creating the new index.

I have tried

Set rs = cnnProj.Execute(strSQL)
While rs.State = adStateOpen
DoEvents
Wend
Set rs = Nothing

but this didn't help.

Anything else I can do?

Walter

 

[Douglas J. Steele]

Try declaring a Connection object With Events, and wait until the
ExecuteComplete event fires.

 

[Vatter47]

The ExecuteComplete works just fine but the error when creating the
index remains. I have the suspicion that the whole app is corrupt, since
it crashes every time I try to do a compact/repair.

 

[Vatter47]

Importing everything in a new mdb didn't help either, although by using
/decompile it crashes a bit less frequently

Another strange thing: Sometimes (even when debugging) the
tbl.Columns.Delete "xID" command provokes Error -2147467259

 

[Guest]

--
msnews.microsoft.com

Try declaring a Connection object With Events, and wait until the
ExecuteComplete event fires.