Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

AS400/Access 2007 Table relationships

Posted on 2012-03-16
1
Medium Priority
?
545 Views
Last Modified: 2012-03-22
I work with files on an AS400. I have no control over the file structure. As a simplified example, the AS400 file structure is:

Table Name: CUSTOMER

(k)ACCT#.....DECIMAL ........ (8.0)
   FName ....CHAR................. 30
   LName.....CHAR................. 30
   Date.........DATE................. 10

The Access 2007 file structure is:

Table Name: tblNOTICE

 (k)Index .............  AUTONUMBER...
(fk)NACCT# ...........DECIMAL............0
     NoticeDate ......DATE ................10

What I want to do is send out notices to delinquent customers. The demographic info (name, address, date of first order, etc) is stored on the AS400. I keep a separate Access database that I use to send out notices. I want to set up a form that prints a delinquent notice and records the date the notice was created. I want the [NACCT#] to be automatically populated with the [ACCT#] from the AS400 table to create a 0ne-to-many relationship with the AS400 file. The [NoticeDate] will be entered by the user.

The problem: When I try to create a One-To-Many Relationship between the AS400 and the Access table I do not have the option to select REFERENTIAL INTEGRITY. How can I get REFERENTIAL INTEGRITY between the two files automatically?
0
Comment
Question by:ergenbgr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1500 total points
ID: 37729805
<<When I try to create a One-To-Many Relationship between the AS400 and the Access table I do not have the option to select REFERENTIAL INTEGRITY. How can I get REFERENTIAL INTEGRITY between the two files automatically?>>

  RI only can be applied within the MDB container (the tables must be local to the MDB file).   But you really don't need that.  You can create a relationship without RI enforced.

Jim.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question