Hello. I have a piece of VBA code that basically executes several dozen queries interlaced with several Compact/Repairs on other databases that are being built as a result of those queries. The queries basically pull in data from multiple data sources on the company network, and then combine it all in to a dashboard-type view for quick and easy queries by other team members.
The problem I am running in to is that sometime during running of the VBA, several indices get dropped on the table that is the main output of this process. Due to the size of that table (~1.5M records), I can't just open the table in design mode and add the indices back in - it's too large to do that.
Up to this point I've kind of tried to poke around and see if I can figure out what's causing this, and in the meantime I just create a blank version of the table, ensure the indices are on there, and then append the records to the indexed version. But, I am leaving my company next week and want to try to get this issue fixed so my successor doesn't have to do those extra steps at the end.
Last week I stepped through the VBA, checking the indices after each step to see when the disappear, and they seem to disappear right after a step that compact-repairs the database where the table lives. But, I compact that database several other times during the process, including before and after that step, so I don't know why it would drop the indices at that time. What I don't know is if it always drops it at the point or if it varies. But I do know that it happens at some point every time.
When I first developed this project, this issue was not there. It just appeared one week when I was running it, and hasn't gone away since.
I am assuming it has something to do with compacting the database - I just don't know why compacting it would cause the indices (and not ALL of them on the table - just a few of them) to get dropped.
Anyone ran in to this before?