Temporarily Disable Multifield Index

[datsyuk]

Is there a way to temporarily disable a multifield index? I have a button on one of my forms that is used to update a number of records at once. This update is accomplish through using VBA and the updated field is a date value. In the process of updating values there will be duplicate values at a point but no duplicate values once the update is complete (ie. if the date is moved forward a day on one record, this may be indentical to another record until the code loops through to update that date a day forward as well). The code works as intended when I turn off my multifield index; however, when it is on, it will not run. Is there a way that I can temporarily disable this multifield index while I am running this code and then reenable it?



Thanks!

[John_G]

Hi -

I know of no way to temporarily disable and index like that, other than to use table design. However, if all you are doing is moving dates as you describe, then you can avoid the problem by running the update loop backwards, i.e. instead of starting with the first date, you start with the last one so that moving it forward will not create a conflict, and will leave a "gap" for the previous date to move into.

HTH

John

[datsyuk]

That would be a perfect solution! I am a little bit at a loss for how to implement it however. Currently I loop through the entire recordset and for all records that match a certain criteria, I update the date for those records. How the data is currently in the table and with the restrictions on how any future data can be added I believe that the later dates are always after the early dates in a table (therefore I simply going through the records in the table in reverse *should* avoid this problem). However, I am confused as to how to loop through the table backwards? Or if there is a more robust solution to running the update look "backwards", I'm all ears.
Thanks.

[John_G]

To go backwards through a recordset, do this:

Open the recordset as usual - lets call it rst:
To avoid the problem with dates maybe not being in the correct order, you could sort the recordset by date (Order By)

Then:

rst.movelast
while not rst.bof
'
' Make changes to this record as required - no change in how you do it
'
rst.moveprevious
wend

Changes from the usual way highlighted in red. That should work for you.

John

[datsyuk]

Darn. The multifield still index complains. I guess I will have to add them to a temporary table, delete the records from the original table and then append the temporary table. I don't think it is feasible to sort the records by date as the date field is actually a foreign key to a master date table that contains other information

[orange]

I can't see any way to temporarily diable an index. But you could Drop the index, then recreate it when your code has finished.

DROP INDEX index_name ON table_name

If this is a recurring issue, you may have to rethink your table update logic.

[John_G]

What are the two fields in the multifield index? And when you run the update VBA, which update causes the error - the first one, or somewhere in the middle of the loop?

Maybe you need to look at the index vs. what is in the table, and see if you can in fact make the updates at all without causing an index violation.

"I don't think it is feasible to sort the records by date as the date field is actually a foreign key to a master date table that contains other information"

I don't see why it wouldn't be feasible - sorting a recordset has no effect on the source data for the recordset, so you needn't worry about messing up the master table.

John