Access database externally linked to Excel not updating/working?

Hey everyone,

I have been trying to link an Access database to an excel doc through the external link tab under the data tab. The data connects fine, but when it does one of my calculated fields (columns) in Access does not calculate correctly in excel, giving me the wrong value.

I have exported the table I've been linking to excel and when I do, the data turns out fine. It is just the data linking that is messing up the calculation.

What would you recommend I do to solve this problem? I have tried creating a new database and redoing all the calculated fields and linking to a new excel doc, and it still gives the wrong numbers. It almost seems like instead of just mirroring the data Excel is trying to do the calculation for that field in Access, and then failing at it.

I appreciate all the help!

How are you importing the Access data? Are you importing it using the Access Link or MS-Query? Either way it comes in as an Excel Table.

What might be happening is that if you are using a user-defined function or something like NZ() that is only understood by Access on the MS-Access side. When this is imported into Excel I don't know what will happen.

However, since the data is coming into a table, you can add a helper column to do the calculation. The formula will be copied down automatically by the table to match the exact number of rows that you have.

You can write a query on the MS-Access side not to contain the calculated field and use that as the import.