Several Indices disappear when running a series of queries/compacts via VBA

[NCSUAaron]

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?

[orange]

I haven't heard of the issue you mention, but perhaps there are others with operational systems that have.
Can you tell us more about the volumes and the frequency of the various processes?
Do you DROP tables?

[NCSUAaron]

I do not Drop tables in this particular project. Instead, I delete the rows, compact/repair, and then append to the now-blank table. I did this so I wouldn't have to re-assign indices every time, even though that isn't happening right now.

Not sure exactly what you're looking for as far as volumes and frequency, but below is kind of a high-level walk-through of the process. I am kind-of self taught for most things involving databases, SQL, VBA, etc, so while I know that there are things that probably aren't the 'best' way to do things, they usually get the job done. The basic premise here is that I work for a company that was bought-out by another company, and there are still 2 different point of sale and inventory and pricing systems in play, so I am pulling data from various places to create a table that has a quick view of all products the combined company offers, and how the pricing and other information compares from Company A's records versus Company B's records to see where we need to focus on aligning this information between the Companies. The results table is basically set up with the left side being the information from Company A's files, and the right side is Company B.

-Run 8 Delete queries that clear out the results tables and several 'helper' tables.

-Compact/Repair several databases that contain the results tables and the 'helper' tables.

-Run a series of pass-through queries on network tables that combine Company A's part attributes, pricing, discount, and sales information for about 1.5M SKUs in to 1 table. That table is then appended to a local table for quicker access for future steps. ~1.3M records.

-Run a similar set of queries for part, pricing, discount, ans sales information from Company B's systems. ~1M records.

-Pull down the current Cross-Reference file that tells what SKU from company A equals what Part Number from Company B.

-Append all of Company A's records to the blank, indexed Results table.

-Update that table with the 'matching' Part Number from Company B.

-Update that table with the attribute, pricing, discount, and sales information from Company B's file.

-Do a left-join query to determine which parts are in Company B, but do not have a cross reference to Company A, and then append those records as well.

The resultant table is about 1.5M records. About 780k of them exist in the Cross Reference, so there is information on them from both Companies. So in the table, each row of for those 780k would include data on both the left side and the right size, so all information on that particular product is on 1 row so the setting from each Company can be easily compared.

For the rest, the product only exists in one Company or the other, so either the left or the right side of the table is filled out, but not both.

[orange]

Can you give us a sample--I'm not following the left and right side of the table?

[NCSUAaron]

Sorry - probably not the best description.

Below is the field list from my table. The first field is a unique key field, and then from SKU through CompanyARetail_Flowed is data from Company A (the 'left' side of the table), and then the rest is from Company B (the 'right' side of the table). For SKU/Parts where a linkage has been established (i.e. we've determined that SKU 12345 from Company A is the equivalent of Part BDR98765 from Company B), then both the 'left' and 'right' sides would be filled in, and the unique key would be 12345_BDR98765. If SKU 67890 only exists in Company A and doesn't have a match in Company B, then only the right 'left' side of the table would be filled in, and the unique key field would be 67890_. If a part BDR43210 only exists in Company B with no match in Company A, then only the 'right' side would be filled in, and the unique key would be _BDR43210.

SKU_Line_Part

SKU

CompanyA_Description

CompanyA_PART_NUM

Vendor#

Discontinued

DIVISION

DIVISION_DESC

GROUP

GROUP_DESC

DEPT

DEPT_DESC

CLASS

CLASS_DESC

SUBCLASS

SUBCLASS_DESC

BRAND

CompanyA_Silver_Setting

CompanyA_Gold_Setting

CompanyA_Platinum_Setting

CompanyA_Diamond_Setting

CompanyA_ICP_Setting

LIST

RETAIL

BASELINE

CompanyA_Calc_Silver

CompanyA_Calc_Gold

CompanyA_Calc_Platinum

CompanyA_Calc_Diamond

CompanyA_Calc_ICP

StkDlr_Code

StkDlr_Price

JOBBER

SAC

COST

SKU_Added_Date

R13_DIY_Units

R13_DIFM_Units

R13_Total_Units

CompanyA_Retail_Flowed

CompanyB_MFG

CompanyB_PART

CompanyB_CLEAN_PART

CompanyB_Description

SL

LineName

SLName

Silver_Setting

Gold_Setting

Platinum_Setting

Diamond_Setting

Mvt_Code

ReplMFG

ReplPart

WHSE_PACK_QTY

PF1

PF2

PF3

PF4

CalcSilver

CalcGold

CalcPlatinum

CalcDiamond

PF6

AftDiscCost

WD

PF12

Strategic_Baseline

CompanyB_R13_DIY_Units

CompanyB_R13_DIFM_Units

CompanyB_R13_Total_Units

CompanyB_Retail_Flowed

[NCSUAaron]

Also - just noticed that my profile had me listed as an Access 2010 32-bit user. That was true when I signed up for this forum several years ago, but I am now using Access 2013 32-bit. Sorry if that caused any confusion.

[aytee111]

I'm running 2010 64-bit and have experienced the same problem. On a table with 5.5 million records, impossible to change the table. It is very irritating! Mine happens on copying or importing the table or making a copy of the database. I have not been able to pin it down as you have and have not found out why or how to prevent it from happening.

[orange]

NCSUAaron,

That's an interesting table and process.
Who is replacing you at the end of the week? Hopefully they have been working closely with you.

For you and aytee111, what do you do when you identify that indexes are missing?
Have you considered importing without indexes and then create index(es) after the copy or import?
Not sure what impacts may arise, but have done large import in other systems and did indexing as separate process.

[aytee111]

(Mostly) I remember to check the table before running my queries. I am doing conversions so it is a temporary situation, fortunately. How do you import without indexes?

[NCSUAaron]

When I see that the indexes are missing, I make a blank copy of the table (so keep the structure intact), add the indexes to the blank table, then append the data into this temporary table, and then finally replace the master with the now-indexed version.

How would I go about creating the index after importing?

If I go to the finished table that now has no indexes, and I open it in Design view, and I go the field I need an Index on and toggle it to Yes and try to save the table, I am told the process cannot be completed and the index is not added. I assume this is due to the large size of the table (on smaller tables I am able to add indexes in this manner), which is why I was trying to have the index on the table PRIOR to starting the process so that it was already indexed when the process finished. If there is another method I can use to add the index back in at the end, that would be good for me (even though it's still curious as to why it gets dropped during the building process).

[orange]

You can use SQL DDL (Data Definition Language)
Create Index

Allen Browne has a series of examples using DAO.
Here is a link to his Create Index

Other info re DDL for Create Index and related statements
Good luck.

[CJ_London]

as an addendum to Oranges comments, with large datasets it is often faster to import to a table without indexes then add the indexes as Orange suggests. The only thing to be careful of is unique indexes - they will fail if there is a duplicate. From the table as described, it doesn't look like there is a primary key so should be achievable. EDIT: missed your comment 'The first field is a unique key field' so providing you are not using it as part of a process or processes to update the rest of the record, suggest remove the index here as well and add back (initially) manually at the end - if there are duplicates, this could also point to an issue.

The other point about indexes is it is usually pointless to index fields which either have lots of nulls or a very limited range of values. Your discontinued field comes to mind if it is being indexed.

So for those indexes which disappear, it might be worth checking the range of values that are entered, just a thought but perhaps a limited range is causing access to drop them.

One of the index options, if you look at the index properties on the ribbon, is an ignore nulls option. I've never really played around with it, but if a field for a disappearing index does have lots of nulls, you could try setting it to yes.

[NCSUAaron]

I didn't know that Index Options pane even existed... Thanks!

The main field I am concerned with that keeps dropping the index is SKU, and this is indeed a field that would have a lot of Null values, as if it only exists in Company B but not in Company A, there would be no SKU. I am going to attempt to first try to re-build this after changing that Ignore Nulls flag to Y, and see if that solves it. I will also look in to building the table with no indexes and adding them in at the end, as it seems that might make the whole process more efficient. I plan on building this out tomorrow, so I'll post back if the Ignore Nulls flag approach works or not.