Problem with Linked Excel Spreadsheet after compact & repair

[zinja]

I have an Excel spreadsheet that is linked to an Access database. The cells in this sheet are referenced on other sheets in the workbook. Everything was fine, except that after doing a compact & repair on the Access database, the cells are not in the same order. What could cause this and how do I correct it? There are almost 1000 cells that would have to be remapped, so it would be better to find another solution. Any ideas?

Thanks,

LJ

LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[longhair]

zinja,
did you make a copy or have a recent backup? if so restore then trouble shoot. doing the compact and repair most likely removed records from access tables that were not removed from your spreadsheet.
hth
regards,
longhair

 

[zinja]

We had a backup (one day old), but after the compact & repair the database was fine. I checked the last order entered (before the crash), and it was there. The problem was that now the order of the cells in the linked sheet is different than before. I am trying to understand what determines the order of rows in a sheet linked to an Access database and why they would change. Thanks for the help though.

LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[longhair]

zinja,
how is the 'link' done? static? dynamic? what i was trying to get at is doing the compact and repair permanently removes deleted records from the db. then, based upon your indexes, the records may be in a different order in your db table. if this is true and the spreadsheet is not updated, it's going to link to the row of the old record, not the new row.
hope i explained this a little better.
regards,
longhair

 

[zinja]

It is somewhat dynamically linked. The data in the Excel spreadsheet is static until the user issues a "Refresh Data" command (in Excel), then the sheet is updated to match the database table. I see what your saying, but the table in question is an inventory table that does not get updated except for one field of each record (Qty on Hand). The rest of the fields for each record are static. There are not records deleted from this table. From other tables, but not this one.

Thanks,

LJ

LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[longhair]

zinja,
is the qty on hand field indexed?
regards,
longhair