vba to update a table in access and maintain link to sharepoint

I'm not sure why you would want to do this but you can create a Make Table Query.  Create a select query that selects what you want from the linked spreadsheet.  Then change the query type from Select to Make Table and give it a table name.  An alternative is to run a delete query and then an append query.  Neither has a particular advantage and BOTH will result in database bloat which means that you will need to compact the database more frequently.

So I have closed the gap a little using the following code:

Public Sub Update()
'Creates a new table in Access that is a copy of WISE_IMRQ_Filtered
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, "WISE_IMRQ_Filtered", "WISE-IMRQ"

'Attempt to link new table WISE-IMRQ to SharePoint.
    Dim Site As String
    Dim List As String
    Dim Tbl As String
   
    List = "{D55414E0-CC11-4782-88EA-0D6EA622BACA}"
   
    Site = "http://inside/private/tfse/mcl"
    Tbl = "WISE-IMRQ"
   
    'Relink Sharepoint lists to DB
    DoCmd.TransferSharePointList acLinkSharePointList, Site, List, , Tbl

Unfortunately, this code does not link the updated WISE-IMRQ table, but instead creates a new link WISE-IMRQ_1. What I am looking for is to link the updated WISE-IMRQ to update the SharePoint site. Any help on what I may be doing wrong with this code? Snipet shows what is created in Access.
C--Users-jspenc-Desktop-Capture.JPG

Delete the spreadsheet link first.  Then relink it.

You don't need to relink the SharePoint List to refresh it.

The Excel spreadsheet is what feeds data to the WISE_IMRQ_Filtered query table and eventually the WISE-IMRQ table. I don't see how unlinking that would allow me to link to WISE-IMRQ in SharePoint? I am fairly certain this will require code, just not sure what code can force that link to WISE-IMRQ as opposed to creating a new link table WISE-IMRQ_1?

I thought it was the spreadsheet link that wasn't updating.  If you need to replace the contents of the SharePoint list, you need to delete all the rows with a delete query and then run an append query to copy rows from the linked spreadsheet into the SharePoint List.

I've had MUCH better luck with linking a sharepoint list as a table in access,  I copy (manually) the sp table to a local table.
then I run vba which refreshes (relinks works) the table and then copies the sp table records to the local table (i first delete * from local table)
then I work with the local table.
pros - much faster than referring to the sp linked table and more reliable since our network has problems
cons - if you change the sp list - you must adust the design of the local table.
when you copy the table to local - it will mark this required, char limits, etc in the local table - so they have ot be updated occasionally.

also easy to test - I can edit/create records in the local table for testing ratther than fooling with SP

I've done this for a few years with more than 3500 records and since it only grabs the SP info once, it's much cleaner.
I see this by examining the network traffic