Link to home
Start Free TrialLog in
Avatar of fatalblitz
fatalblitz

asked on

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

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
Avatar of Jerry Miller
Jerry Miller
Flag of United States of America image

You could do the delete with a VBA method, by supplying the table name as a parameter.

Public Sub deleteTableContent(byVal tableName as string)

Delete * from ' & tableName & '

End Sub

The append depends on how different the structure is between the tables. You may be able to create a function to do it, but we will need more information as to the table structures.
Avatar of fatalblitz
fatalblitz

ASKER

Would I just have to type the delete query for each table in that deleteTableContent sub? The table structures differ with each table, because each one deals with a different department. Would an update query work in this situation, if table structure was an issue?
ASKER CERTIFIED SOLUTION
Avatar of Jerry Miller
Jerry Miller
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial