Runtime error 3825 in MS Access

I am trying to merge 5 databases into one. The 5 databases have the same tables, same structure and same fields for each tables and the same name for the tables and fields as well. Just that the data in each database is different as it has been taken by 5 different members. So, I am trying to merge all the records into one. So, I am basically trying to write a query for each table and then run a VBA code to run the que开发者_C百科ries one after the other. The query is

INSERT INTO SoilMeasurementTable IN 'C:\Users\vtalreja\Desktop\Common.accdb'
SELECT * FROM SoilMeasurementTable;

where Soil measurement is one of tables in the present database and I am copying it over to the same table in a common database.

I have VBA code for running all the queries one after the other. I have saved this VBA code as a module and running it as a macro in every database so that the records get merged into one database. So my problem is when it comes to run a particular query for a table it pops out this error:

"Select * cannot be used in an INSERT INTO query when the source or destination table contains a multivalued field."

I am not sure what it means. I have tried to search a lot but without success. Can anyone please please help me out and let me know what is the error likely to be and what could be changed to make it to work?

---

Multi-valued field is a new feature introduced with Access 2007, and is supported only in the new ACCDB format, not in the older MDB format.

New Access users frequently favor multi-valued fields because they seem convenient. And they may be so for interactive use. But when you're trying to use code to manipulate data, multi-valued fields present challenges. You found one; the error message is clear ... you can not use SELECT * for an INSERT statement with multi-valued fields.

I don't know if there is a simple work-around for your situation because I avoid multi-valued fields. If it were me, I would prefer to re-design to replace the multi-valued field with a separate table to hold those multiple values for each record ... one record per value with each associated with the parent record's primary key.

If that is too much work, wait to see if someone else offers a simpler solution which will work with your current database structures.

---

As @David-W-Fenton mentioned in the comments of the other answer, modifying your SELECT statement to explicitly list the fields should solve the problem. It did for me. So in other words, change:

INSERT INTO SoilMeasurementTable IN 'C:\Users\vtalreja\Desktop\Common.accdb'
SELECT * FROM SoilMeasurementTable

to:

INSERT INTO SoilMeasurementTable (field1, field2, field3) IN 'C:\Users\vtalreja\Desktop\Common.accdb'
SELECT field1, field2, field3 FROM SoilMeasurementTable