Hi all, Im hoping there are some people around who have worked with SharePoint and Access before to help me out with this one.
I have been tasked with expediting the report generation based on SharePoint (SP) lists at work. The SharePoint server is located in Sweden, while our office in Perth needs to create and run the reports based on the SP information. MY initial solution was to link the tables to an Access application and query the linked tables to produce the reports. A nice interface would then allow upper level management to generate the reports in a click or two. The application then expanded to allow operational staff to enter data into SP via the Access application interface which runs in an online mode with SP. The staff previously had to enter the data by using the SP web interface and needed to enter the same data in a number of lists. I created queries and modules to automate much of this in Access.
The problem is, SP does not allow server side querying without building a web part to do so. The SP server is owned and managed by a third party, who are the only ones authorised to make changes to the SP lists and the underlying SP code. So what this means is that every time the Access application executes a query, every row in every SP list required by the query is downloaded to the local machine's cache in the background by Access and the query is then executed. The typical wait time for a report in this case is about 8-10 minutes. The same applies when users try to enter data using the Access application, since there are background queries that run when various forms are opened.
So, my proposed solution was to create a backend database in Access and link to the SP lists using the backend. Then take the backend offline from SP and have the users front end link to those linked tables. I figured that since the backend is in offline mode, that a copy of the data should be resident on the local server here hosting the backend. But then I find out that Access does not allow you to link to a linked table
So the solution I am currently thinking about is as follows:
1. Create linked tables to the SharePoint lists and take the backend offline
2. Programmatically make a copy of the SharePoint lists
3. Issue an ALTER statement to each of the copies to add an IsModified column (a modified date column already exists)
4. Link the front end applications to the copied tables
5. Users make changes to the copied tables
6. Execute an update query that updates the linked tables with the information in the copied tables for each copied table row that is flagged as 'IsModified'
7. Append any rows that exist in the copied table to the linked tables
8. Bring the linked tables online programmatically, at which time they will sync with the SP server
9. Since data is being entered by the Sweden office, drop the copied tables and run through steps 2 to 8.
I dont need to worry about entire row deletions at either end, as no data is deleted in the SP lists or the local copy.
It seems like an inordinate amount of work simply to link to some linker SP lists. If anyone has encountered this issue before please reply back if a more efficient solution exists.
Many, many thanks in advance
I have been tasked with expediting the report generation based on SharePoint (SP) lists at work. The SharePoint server is located in Sweden, while our office in Perth needs to create and run the reports based on the SP information. MY initial solution was to link the tables to an Access application and query the linked tables to produce the reports. A nice interface would then allow upper level management to generate the reports in a click or two. The application then expanded to allow operational staff to enter data into SP via the Access application interface which runs in an online mode with SP. The staff previously had to enter the data by using the SP web interface and needed to enter the same data in a number of lists. I created queries and modules to automate much of this in Access.
The problem is, SP does not allow server side querying without building a web part to do so. The SP server is owned and managed by a third party, who are the only ones authorised to make changes to the SP lists and the underlying SP code. So what this means is that every time the Access application executes a query, every row in every SP list required by the query is downloaded to the local machine's cache in the background by Access and the query is then executed. The typical wait time for a report in this case is about 8-10 minutes. The same applies when users try to enter data using the Access application, since there are background queries that run when various forms are opened.
So, my proposed solution was to create a backend database in Access and link to the SP lists using the backend. Then take the backend offline from SP and have the users front end link to those linked tables. I figured that since the backend is in offline mode, that a copy of the data should be resident on the local server here hosting the backend. But then I find out that Access does not allow you to link to a linked table
So the solution I am currently thinking about is as follows:
1. Create linked tables to the SharePoint lists and take the backend offline
2. Programmatically make a copy of the SharePoint lists
3. Issue an ALTER statement to each of the copies to add an IsModified column (a modified date column already exists)
4. Link the front end applications to the copied tables
5. Users make changes to the copied tables
6. Execute an update query that updates the linked tables with the information in the copied tables for each copied table row that is flagged as 'IsModified'
7. Append any rows that exist in the copied table to the linked tables
8. Bring the linked tables online programmatically, at which time they will sync with the SP server
9. Since data is being entered by the Sweden office, drop the copied tables and run through steps 2 to 8.
I dont need to worry about entire row deletions at either end, as no data is deleted in the SP lists or the local copy.
It seems like an inordinate amount of work simply to link to some linker SP lists. If anyone has encountered this issue before please reply back if a more efficient solution exists.
Many, many thanks in advance
