MS Access how to delete all records in a table and insert new records in that table without deleting-CodePudding

Info: Today I use a SELECT INTO query in a MS Access database to generate table A. In MS Access I export this table A monthly to a SharePoint List to publish the data.

My question is: I want to use a linked table A (not an exported table). That means I can't use SELECT INTO anymore because the SELECT INTO first deletes the linked table A and then generates a new table A. This new table is no longer linked. How can I replace all content in the linked table A with new data without deleting the table first?

Help please?

CodePudding user response：

You can run two different query:

The first one allow you to delete all records in table A so your table is empty:

DELETE * FROM tableA

The second one allow you to populate table A with new values from table B (for example):

INSERT INTO tableA (field1, field2, fiel3, ...) SELECT field1, field2, field3 ... FROM tableB WHERE ...

This can be done by vba (for example on button's click):

Dim sql1 as string
Dim sql2 as string

sql1 = "DELETE * FROM tableA"
sql2 = "INSERT INTO tableA (field1, field2, fiel3, ...) SELECT field1, field2, field3 ... FROM tableB WHERE ..."

Docmd.RunSQL sql1
Docmd.RunSQL sql2

or you can create two different query and run the delete query first and than your insert into query.