 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / May 2007Error when creating primary index
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 Have you checked to make sure that the Update statement is actually working?  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > 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 [quoted text clipped - 24 lines] > Thanks for helping > Walter > 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? >> Have you checked to make sure that the Update statement is actually >> working? [quoted text clipped - 6 lines] > > 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. 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 >>> Have you checked to make sure that the Update statement is actually >>> working? [quoted text clipped - 10 lines] > CreateIndex subroutine into the database and now type Text does not work > anymore as well. Try declaring a Connection object With Events, and wait until the ExecuteComplete event fires. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Having looked at this problem again I have now found that the code works > fine when stepping through one by one in debug mode. [quoted text clipped - 31 lines] >> subroutine into the database and now type Text does not work anymore as >> well. 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. > Try declaring a Connection object With Events, and wait until the > ExecuteComplete event fires. 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 > 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. > >> Try declaring a Connection object With Events, and wait until the >> ExecuteComplete event fires. Signaturemsnews.microsoft.com > Try declaring a Connection object With Events, and wait until the > ExecuteComplete event fires. [quoted text clipped - 34 lines] >>> CreateIndex subroutine into the database and now type Text does not work >>> anymore as well. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.