Transfer Content from table into another table with attachment

[Adam9595]

Hi everyone!



i want to transfer my table "db_SelectUploadFiles" (Source) into a new SharePoint-table "db_Belegverzeichnis" (Target). The columns are as follows:

Source.Belegnummer = Target.Beleg
Source.Buchungsdatum = Target.Buchungsdatum
Source.Feld1 = Target.Anlage

"Feld1" and "Anlage" are attachment fields where files can be attached. Unfortunately, I cannot copy the tables using the INSERT INTO syntax because Access gives me an error stating that an INSERT INTO query cannot contain a multivalue field.


My code:

******
Private Sub cmd_02_01_01_Save_IstKosten_Click()
Dim db As DAO.Database
Dim AddFileSQL As String

Set db = CurrentDB

AddFileSQL = "INSERT INTO db_Belegverzeichnis (Beleg, Buchungsdatum, Anlage) SELECT Belegnummer, Buchungsdatum, Feld1 FROM db_SelectUploadFiles;"
db.Execute AddFileSQL, dbFailOnError
*****


How can I solve this problem?

Thank you!
Adam

[Welshgasman]

Copy the table and delete the relevant fields you do not want from the new table..

[Adam9595]

Hello Welshgasman,
A simple copy of the table is not sufficient for me, as the Source table is dynamic and can change regularly whenever the user adds a different attachment via the form or changes the "Belegnummer" or "Buchungsdatum."
There is also a button in the form that writes the record from the Source table into the Target table as well.

[Welshgasman]

I have never used sharepoint, but can that link to a query?, then use a query from your table.
You're initial logic would not work if that table is so dynamic, it would soon be out of date.
Trying to keep two tables in sync is very difficult. Better to have one and get another view of the data?
Why the need to reduce the data, just grab what you need with a query.

[June7]

By "SharePoint table", do you man a SharePoint list?

Never used SharePoint either. From what I've read, a list can support one Attachment field. But this appears to use an Export operation, not an SQL INSERT.

Copying records that have multi-value fields (Attachment is a type of multi-value) gets complicated. Because copying multi-value field to another table actually involves a separate INSERT action for new records. Multi-value fields really just hold "pointers" (or links) to a hidden dependent table where each value is a record.

This is one user's solution: "I have found that I can still run the Append query if I do not use the "*" function but list all fields except the attachment. I can then manually copy the attachment column (CTRL C) and paste it (CTRL V) into the updated table."

Fairly common topic. Here is one discussion https://www.access-programmers.co.uk...nother.246743/