live updates from excel

[ArtChr]

Hello,

I am trying to get real time updates from an excel sheet and put them into Access when ever I pull up a report.

I have a sheet named Access Data that has 5 columns named Item, Company, Balance, Limit and Location. I created this sheet as a link from the other sheets because these are the only 5 pieces of information I want in Access.

I have a database set up with a table, a form and a report.

Whenever I add a new Item to excel, I use the form in Access to add that same Item.

The balance is the field that changes all the time in the spreadsheet and when I pull up a report for the table I want the most current balance data from excel to show up in the report as well as all of the other column data.

As of now I can only accomplish this by manually updating the data in Access.

[alansidman]

Typically, tables do not hold calculated fields. This goes against relational db principals. Calculations are usually performed in queries and forms. I can think of three ways for you to achieve your expected results.

1. Make a query your record source for your report and have a calculation performed in the query.

2. Instead of entering the data twice, once in Excel and the second time in Access, link your Excel table to Access and use that linked table as your record source for your report.

3. My preferred method would be to link your table as in 2. above but do the balance calculation in a query and make the query your record source for your report.

Alan

[ArtChr]

ok great, it looks like option 2 is what I will try. I am not doing the calculation in access, those are done in excel and then referenced on the "Access Data" sheet i.e. !2011D12.

Previously I have imported the spreadsheet into a table format. It also updates the information that has changed in the original spreadsheet. But the report is already linked to the hand input table that I created at first. Is there a way to just link the imported data (the balance) into the one column of the report? example, the report spits out a form with all five pieces of information I created in the table, is there a way to have one of those pieces of information coming from a different table?

[alansidman]

The simplest and most efficient solution given that you are opting to linking the table is to change the record source in your report (in the properties section) for each field to the new table. Why create a work around for the obvious. Keep it simple and it will reward you down the road with less issues.

[ArtChr]

I tried changing the control source for that one text box to the newly imported sheet, but in the report I only get #ERROR in that column.

[alansidman]

Without seeing your db, I would only be guessing as to what is happening. Suggest you post your db, however, you will have to manipulate what you post to show the linked table as an imported table or we won't be able to see what is happening. Make sure you dummy up any confidential data and be sure to do a compact and repair to reduce the size.

Alan

[ArtChr]

Let me see if I can explain in a clearer way. I have a report that is created from table1. Let's say that table1 has 5 fields. In the report I want to display all five of those fields, plus another field that grabs data from table2. Table2 is an imported sheet named Access Data Sheet (External Data > Excel > Link to the data source by creating a linked table) that only has one field of data named Balance. So in my report I go to properties to change the control source for that Balance text box. In the drop down menu I only have the option to select from the fields that are in table1, so I click on the expression builder. I build =[Access Data Sheet]![Balance]. Now when I view my report I get a pop up box that asks me to "Enter Parameter Value" for the Access Data Sheet, no matter what I enter the Balance text box only shows #ERROR.