Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ACCESS Append queries

Status
Not open for further replies.
nablainc

nablainc

Programmer
Oct 23, 2004
25
US
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
 
Sort by date Sort by votes
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:
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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:
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sans2174
  • Locked
  • Question
User Module Access report
Replies
4
Views
384
dgillz
dgillz
JBHILLS
  • Locked
  • Question
Runtime Error '75': Path/File access error on a citrix Portal Gateway install. 1
Replies
2
Views
285
JBHILLS
JBHILLS
ndininno
  • Locked
  • Question
Order Selected for billing. Access Denied 1
Replies
3
Views
270
dgillz
dgillz
junkmail32
  • Locked
  • Question
Creating link from Access to Progression database
Replies
2
Views
184
crystalreporting
crystalreporting
wb4wzz
  • Locked
  • Question
Downsizing access to Macola 7.6.300C Pervasive v9
Replies
4
Views
147
dgillz
dgillz

Part and Inventory Search

Sponsor

Back
Top