Avatar of 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 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
Microsoft AccessMicrosoft DevelopmentMicrosoft SharePoint

Avatar of undefined
Last Comment
Jerry Miller

8/22/2022 - Mon
Jerry Miller

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.

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?
Jerry Miller

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes