Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

ACCESS Append queries

ACCESS Append queries

ACCESS Append queries

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


RE: ACCESS Append queries

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: www.gainfocus.biz/exceladdin.html

RE: ACCESS Append queries

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


RE: ACCESS Append queries

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: www.gainfocus.biz/exceladdin.html

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close