Hey, Basically I have a linked t

[SpankYou]

Hey,

Basically I have a linked table linked using one to one relationship with a single table I have created in access. The problem is when a new jobID is entered in the external table, the jobID must be replecated in my access table. This should be a straight foward case of using the cascade update for the relationship but this is blanked out. The table can only be a one to one so I was wondering if there was anyway of coding this automatic update of data?

Cheers for any help.

Sam

 

[Darrylles]

Hi,

Why must the table be a onene?

A onene relationship is simply a 1:many that YOU physically ensure only exists as a 1:1.

1:many actually means: 1 to 0 OR 1 OR many records.

Create a relationship as 1:many and then try cascade update.

As you control the logic, you control how records can exist on the many side.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[SpankYou]

Darrylle,

The new table is basically an add on to the existing table that it is linked to. So it just holds more information about the job or project. I know this is really bad design but it is what they want. As the table is a linked table, it only allows a one to one relationship, and disables the cascade update/ delete features, so I was wondering if there was any code that I code set it up as I want to?

Cheers

Sam

 

[jason246day]

If it is a one to one relationship, and every entry will need an entry in this other table, why not just combine both tables into one. This will consolidate all the information into one place, making it easier to work with. This is called horizontal composition.

 

[Darrylles]

Hi,

The point is that a 1:1 IS a 1:Many !

1:Many means:
1:0
OR
1:1
OR
1:Many.

Think about it: when you first start your database with 2 tables having a 1:Many relationship you'll start with a record in the ONE and no records in the MANY. Giving 1:0.
You then add ONE record in the MANY table - giving a 1:1.
You add another to the MANY giving you 1:2 or 1:MANY.

Change the relationship DEFINITION to 1:Many, but only allow it to PHYSICALLY ever be 1:1 (i.e. once it has one record - don't allow another to be added, e.g. every time an attempt to add a record is made - check that one doesn't already exist).

You can then use the cascade - this is a correct way of doing it.

Honest.

Regards,

Darrylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[SpankYou]

Hey,

Ive tried both methods, and afraid niether worked.

The relationship can not be changed at all, as it is a linked table, and microsoft will not allow linked tables to be adjusted (this is the reason I could not add fields to the table). I understand your theory Darrylle and completely agree with it, but as Microsoft have disabled this function, it is automatically set as a 1:1 relationship, and can not be altered manually, and hence the cascade update/delete functions are blanked out. The only possible way, may be to code the relationship (according to microsoft) who provide useless code.

Thanks for all your help, I know I seem to be being a bit awkward, but microsoft in their infinite wisdom seem to have forgotten about this function and it makes my few weeks work obselete.

If anyone knows the code to create this relationship I would greatly appreciate it,

Thanks again Sam