ACCESS Append queries

[nablainc]

Hello forum members,

A client is using MACOLA ES 9.5.700. We sometimes use Microsoft ACCESS Update queries to make wholesale changes to certain fields such as prices or costs when there is no convenient method available using MACOLA screens. On occasion we have tried using Append queries to add records when only one field (a key field) differed from an existing record. Even though the key fields differed, the query fails because of key violations. Can Microsoft append queries be used to add records? We are aware that MACOLA might require related tables to be updated and wonder if this is why the queries fail. We also realize that queries outside the COBOL code require extreme caution. We have so far managed to find alternate ways to add records and prefer not to use append queries, but we are curious whether they can be used.


Thank You

Jerzy

 

[dgillz]

A fuller explanation of precisely what table you are trying to append to is needed, but the short answer is it depends on the table.

Many ES tables you cannot do this to because the tables themselves are too complex for Access. The error message you described - key violation - means you aren't populating a required field or you are trying to add a duplicate record.

Macola ES comes with limited XML import capabilities, you need to consider that as well.

Finally, the Wisys objects are a great tool to use to add records and they will follow all Macola business logic, ie - updating all tables a particular process does at once.

So please indulge me, what table are you trying to append to?

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[nablainc]

Thank you for the quick and informative reply.
We were trying to add additional locations to the IMINVLOC table after a batch of new items had been added to the database. The production manager discovered how to do this using MACOLA's input screens. We were curious because we had taken care not to duplicate the key fields and wondered why we still got key violations. We don't like doing anything that could
circumvent MACOLA's logic and use external queries only as a last resort.

I have a learned a lot over the years from your postings in this forum and I want to thank you for them.

Best regards

Jerzy

 

[dgillz]

As I mentioned the other thing that could cause a key violation is leaving a field NULL that does not accept nulls. This can be very tricky.

As you mentioned, the ability to mass create item locations is built in to both Progression and ES, so there no need for MS Access.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html