troubleshooting Question

Updating/Appending Microsoft Access 2010 table data to SharePoint 2007 Lists

Avatar of fatalblitz
fatalblitz asked on
Microsoft AccessMicrosoft SharePointMicrosoft Development
3 Comments1 Solution840 ViewsLast Modified:
I currently have a Main database that generates reports based off of query results. Each month, new data replaces the current tables in the Main database, ultimately changing the report output data. I have SQL code to capture this query output and put the results into a new table in a seperate Prototype database. The SQL statement is:

SELECT * INTO table_CES IN 'C:\Users\UserName\Desktop\Prototype.accdb'
FROM [SKILL AREA VR - All Info CES];

From the Prototype database, I will have 16 tables, each linked to a SharePoint list.
The purpose of this second database is to house these 16 linked tables, each table having it's own report data that will be distributed to a leadership team.

My concern is figuring out how to keep these SharePoint lists updated, without breaking the connections or overwriting the existing linked table names. To my knowledge, i've read DELETE and APPEND queries seem to be the approach to take, but does that mean I would have to write 16 DELETE and 16 APPEND queries? And would there be a way to do this programatically via VBA. Thanks

Heres a reference article that I posed the question in:
EE - Supplemental question
ASKER CERTIFIED SOLUTION
Jerry Miller

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros