Link Tables Not Refreshing

[goodguy]

I created a blank database and linked to a few tables from a secure database, in order to let users access some custom queries and reports. However, I find that the linked tables do NOT update automatically each time a query or report is clicked, the user has to open the tables in order to refresh them.
Why is this happening? Aren't linked tables supposed to refresh automatically every time?


Note: the source database is on a server location mapped to the local PC and does not require any password prompting.

[RuralGuy]

What leads you to believe the tables are not updating?

[goodguy]

When users call the report, it opens with the records to the date the report was last run. If the report was last run on Friday 31Dec, and they open it again Friday 3Jan, it will display records upto 31Dec; but after the table is refreshed, the report too will be updated.

[RuralGuy]

What is the RecordSource of the Report?

[goodguy]

A query that runs through four tables. The query has no direct parameters. The parameters are set from a report filter form prompting for Start and End dates.

[RuralGuy]

What are you doing to "refresh" the tables?

[goodguy]

Simply by opening the link table. It is very distressing!

[RuralGuy]

Is the BackEnd Access? Can you show us the SQL for the "...query that runs through four tables"?

[goodguy]

SELECT a.CHECKDATE, DEPARTMENTS.DEPTNAME, a.USERID, USERINFO.Name, Max(a.inTime) AS inTime, b.outTime,
Format((b.outTime-intime),"Short Time") AS Duration
FROM DEPARTMENTS
INNER JOIN
(
(
(SELECT DateValue(CHECKTIME) AS CHECKDATE, CHECKINOUT.USERID,
CHECKINOUT.CHECKTIME AS inTime, CHECKINOUT.CHECKTYPE
FROM CHECKINOUT
WHERE (((CHECKINOUT.CHECKTYPE)="0"))
)
AS a
INNER JOIN
(
SELECT DateValue(CHECKTIME) AS CHECKDATE, CHECKINOUT.USERID,
CHECKINOUT.CHECKTIME AS outTime, CHECKINOUT.CHECKTYPE
FROM CHECKINOUT
WHERE (((CHECKINOUT.CHECKTYPE)="1"))
)
AS b
ON (a.USERID=b.USERID) AND (a.CHECKDATE=b.CHECKDATE)
)
INNER JOIN USERINFO ON a.USERID=USERINFO.USERID
)
ON DEPARTMENTS.DEPTID=USERINFO.DEFAULTDEPTID
WHERE (((a.inTime)<[b].[outtime]))
GROUP BY a.CHECKDATE, DEPARTMENTS.DEPTNAME, a.USERID, USERINFO.Name, b.outTime;

[RuralGuy]

I see nothing in that query that would alter a table. What are you doing in the Report to request the Dates?

[goodguy]

A form prompts users for Start Date and End Date values, clicking the OK button passes the parameters to the report.

[RuralGuy]

A form prompts users for Start Date and End Date values, clicking the OK button passes the parameters to the report.

Is this done in the WhereCondition argument of the OpenReport command?

[goodguy]

Yes. This is the command: (DoCmd.OpenReport "JobsReport", acViewPreview, , "outTime>=#" & (StartDate) & "# and outTime<= #" & (EndDate) & "#").

[RuralGuy]

The WhereCondition argument simply applies a filter to the RecordSource of the Report. I wonder if it is not updating the filter. It would be useful to run an intervening report with no WhereCondition argument and see what happens.

[goodguy]

I will try and revert with the results.