SharePoint 2007 and Access 2003

[Gitche Gumee]

I am linking to a SharePoint list from an Access database. The SharePoint
list updates a Project Information table. A query on the Project Information
table and several other tables is the record source for a Project Information
form.

When the Project Information form is opened, an update query runs to load
the Project Information table with the most recent data from the SPN. This
works fine.

What I would like to be able to do is periodically refresh the Project
Information table with the SPN data while the Project Information form is
still open. A Refresh button runs update queries to collect the data from the
SPN. A requery of the form then shows the most current data from the SPN.

The problem is that the update queries and form requery do not work UNLESS I
open the linked table in the background. That is, before I click the refresh
button, I have to manually open the SPN table. Then clicking the refresh
button causes the most recent data from the SPN to be displayed on the form.

Why is that? And what can I do to get around it?

 

[Dale Fye]

My first question is why aren't you just using the Sharepoint list as the
source for your form? I'm doing this with an application that contains about
15 "data" tables, and another 15 lookup tables.

I assume that you are running other queries to dump your new and updated
data from your local table back into the list?
--

Dale

email address is invalid
Please reply to newsgroup only.

 

[Gitche Gumee]

The projects on the SharePoint site are a subset of all the projects. The
Access form must display all projects, so the SharePoint list cannot feed the
form directly. Update queries run when the form is opened to load data from
projects on the SharePoint site into the Project Information table in Access.
I have tried using a single query as recordsource for the form, but it takes
several minutes to run and is not updateable.

I did resolve the problem of the refresh not working unless the SharePoint
table is open in Access. I built a dummy form with the SharePoint list as the
recordsource. Now clicking the Refresh button opens the dummy form in the
background, then runs the same update queries that were run on the Project
Information Form_Open event. The refresh takes about two seconds. Perhaps
someone can explain why the dummy form is needed.

'pull new data from the SPN

'-------------------------------------
'Open dummy form to open SPN recordset
'-------------------------------------
Application.Echo False
DoCmd.Hourglass True
DoCmd.OpenForm "f_Product_SPN_Dummy"

'update queries run here

'-------------------------------------
'Close dummy form
'-------------------------------------
DoCmd.Close acForm, "f_Product_SPN_Dummy"
DoCmd.Hourglass False
Application.Echo True

Me.Requery