Linked Tables NOT Updating

[Littlemalky]

I have a table in Access that is linked to an Excel Workbook that contains part numbers and formulas. However, I've been running accross an issue with my information being properly updated in the access table. Not all of my part numbers are being picked up in Access even though they exist in the Excel Workbook. It's not a formatting issue either because everything is formatted the same and all the other hundreds of part numbers and quantities were picked up.

The real kicker is, if I open up the workbook and the database at the same time, it refreshes the table and the numbers get accounted for. Why is this? The whole point of linking a file to access is so that I don't have to open up both. Why doesn't it pick up the information the first time?

 

[jackd]

What exactly do you mean by "Not all of my part numbers are being picked up in Access "?

When you say the records are accounted for when you reopen, it would seem the proper records are being accessed/stored/presented.
So I think it's updating, but just not presenting the latest data to you.

In earlier versions of Access there was a command, and it may still be there in the latest versions, that would refresh the Access window. I don't know if that is an issue in your case.
It is definitely in Acc2003.

Application.RefreshDatabaseWindow

I used it so that my Tables showed the proper values when running code.
You get the same effect by opening another window --say Forms, and then go back to Tables. When you reopen the window(Tables) it is refreshed.

 

[Littlemalky]

I'm using 2007.

Why would the linked table refresh accurately for all the other part numbers and quantities but only (randomly) leave out a couple. The only way I've been able to get the table to properly refresh is if i open up the workbook that is actually linked to the table and that database at the same time.

Are you saying there is a command i need to run before i attempt to download queries directly out of the database so that it is properly refreshed?

 

[boblarson]

It is best to not depend on linked Excel files as tables. Also, the workbooks would need to be saved before the data would refresh but you would need to requery the "table" in order for any data that was entered while the database was open to show up.

 

[Littlemalky]

I definately save and close the excel file before i download the query that uses that table. If i shouldn't use the linked table, how do i get around this so that this becomes automatic. There are too many tables to feasibly upload a new one to replace the old everytime i have to run this.

 

[boblarson]

I definately save and close the excel file before i download the query that uses that table. If i shouldn't use the linked table, how do i get around this so that this becomes automatic. There are too many tables to feasibly upload a new one to replace the old everytime i have to run this.

The most reliable way is to move the Excel workbook functionality to Access and then give a frontend copy to anyone who needs to update the data that was in Excel.

But, my tests (with Access 2003/Excel 2003) are showing that you shouldn't even need to save the file for the changes to show up. Is it just you in that Excel file? Or are there others using it too?

Is your data text or numeric?

 

[Littlemalky]

I am the only one in it, it's my personal database and excel file. Just to be clear, i'm not linking a table to a spreadsheet and reading it within excel, I'm doing the opposite: linking a worksheet to access to create multiple queries off it it.

My excel working has 4 columns with static formulas in them that go to a far out range. They consist of VLOOKUPS to another workbook that is always open at the same time. The only thing i do to update the worksheet is replace the old part numbers, descriptions and quantities with new ones and then all the vlookups will autofill everything else because the formulas already exist. The VLOOKUPS display dates in a couple of the columns and text in some of the others, and natually the quantities are numbers. I was having issues with Access reading the proper formatting of each column when data didn't exist, but i got around that by implementing an IFERROR to display an arbitrary date/text that would maintain my formatting. The formatting is not the issue, it just leaves out a couple part numbers with the corresponding formulas; yet everything else is completely updated and accurate.

I can't wrap my mind around it.

 

[boblarson]

Well, I don't know why you would be missing anything unless there is something that isn't getting updated for some reason.

Personally, I might suggest not relying on the linked Excel file concept but instead create a bit of code which would import the Excel data and run the query when it is needed. It may seem like a lot to do but it would just be a matter of clearing out the table and then running the import. It shouldn't take but a few seconds and then it would likely have the data you need instead of trying to figure out why it isn't getting everything. Or, perhaps during you setting the import up, the reason why the other data is not showing up might be made clear.

 

[Mudbutt]

I know, it blows my mind as well. Thanks for the suggestion.