An insert into cannot contain a multi-valued field

[psamu]

i am trying append a table To archive table. Table has attachment and values come from other table. I am able copy and paste into archive table. For multi-valued fields, is there any way can I do this by VBA code instead of query . Thanks for the help.

 

[HiTechCoach]

Have you tried an append query? See: https://support.office.com/en-us/ar...ries-6F64F92D-659F-411C-9503-B6624E1E323A#bm7

There are many issue with the multi-value field in the (Access) ACE database format (.accdb). It was a "feature" added to allow web database to work in Sharepoint.

I avoid this new multi-value data type because it is not support in MS SQL Server. I have started using SQL Server as a back end now for most projects.

i am trying append a table To archive table.

Are you moving it into a table in a separate back end? If not, then it is best to NEVER move records between tables. I use an "archive" flag. Use the archive field to to filter records.

This has many benefits.

It also avoids your issue with the multi-value fields. Since it is Best Practice to not move records but use an archive flag would explain why I have not seen people having this issue.

 

[psamu]

Have you tried an append query? See: https://support.office.com/en-us/ar...ries-6F64F92D-659F-411C-9503-B6624E1E323A#bm7

There are many issue with the multi-value field in the (Access) ACE database format (.accdb). It was a "feature" added to allow web database to work in Sharepoint.

I avoid this new multi-value data type because it is not support in MS SQL Server. I have started using SQL Server as a back end now for most projects.


Are you moving it into a table in a separate back end? If not, then it is best to NEVER move records between tables. I use an "archive" flag. Use the archive field to to filter records.

This has many benefits.

Thanks. The reason I wanted to move into separate archive table is active table will be faster. I don't know is that right ?

 

[HiTechCoach]

Thanks. The reason I wanted to move into separate archive table is active table will be faster. I don't know is that right ?

It usually helps performance when the database has a poor design. A well design database you be able to handle many years worth of data.

Archiving transaction normally means you would need to move records from multiples tables to the archive.

Are you also arching all the lookup tables and all other the related data? If not then you will have to create duplicate relationships with the production table and the archive tables which do impact performance.

I find most performance issues with Access application is not due to the number of records. It is from poor design of tables (schema), indexing, , and queries. Basing a form on a table not a well design query hurts performance.

Performance:

A database that is properly normalized is key to performance.

Well designed queries for forms and reports that only return the records and fields need are key to performance. This is true for any Database, include MS SQl Server, not just Access

How I learn to design for performance is to have a good test back end. This is one with lots of data. Usually I try to create a minimum of 10+ years worth of data to really stress test my application.

In my experience most developers never work with large test datasets from the start while building and testing there application. They usually have just a few records in each table. If you wait until the application is almost done to stress test snfd you find as design issue, it rarely gets properly fixed. That would take to much resigning. That is why Agile development has become popular.

I also test on lower end machine, not a high end computer like a lot a developers have.


If performance is critical, ther are 15+ concurrent users, or 10,000+ transactions added a year then I would upsize the back end to an SQL Server. You can still use Access for the front end. This way you get eh RAD development with Access and the power of a true client server RDBMS.

 

[psamu]

Thank you so much. I think that is good idea to do the stress test. Sure I will follow the advice. Appreciate and Thank you again for your time.

 

[HiTechCoach]

You're welcome.

Sounds like you are on the path to great success with your project.