473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Link table to itself, programmatically

Hi,

The problem is I would like to use Cascade-to-Null relationship which can be done using nice code by Allen Browne http://allenbrowne.com/ser-64.html, but within one table. For instance in a table "Empoyees" which has a field "RepotsTo" that refers to another employee in a same table. I don't want to delete empoyees by deleting a supervisor!

Thanks.
Oct 14 '08 #1
5 2493
Stewart Ross
2,545 Expert Mod 2GB
Could you perhaps amplify what it is you wish to know? Self-joining tables is straightforward, but I am entirely unclear what you are asking.

To remove the 'reports to' relationship of a particular row in an employee table the 'reports to' field is simply reset to null. The excellent code you link in post 1 above applies to multiple-table relationships rather than self-joins.

-Stewart
Oct 14 '08 #2
OK, I will be more clear.

I have a table which is joined to itself, something like table "Employees" in which there is a field ("ReportsTo") that is related to the primary field of the same table ("EmployeeID"). When I try to delete a record I get a message that either there is a related record in the table or that the record is locked (depending of whether the Cascade Deleted Records is selected in Relationships window). Cascade-to-null is a fine solution that I use a lot, but I don't know how to apply it in this situation. (How to refer in VBA to that phantom table "Employees_1" that is automaticaly created when joining tables in Relationships Window?)
Oct 14 '08 #3
FishVal
2,653 Expert 2GB
OK, I will be more clear.

I have a table which is joined to itself, something like table "Employees" in which there is a field ("ReportsTo") that is related to the primary field of the same table ("EmployeeID"). When I try to delete a record I get a message that either there is a related record in the table or that the record is locked (depending of whether the Cascade Deleted Records is selected in Relationships window). Cascade-to-null is a fine solution that I use a lot, but I don't know how to apply it in this situation. (How to refer in VBA to that phantom table "Employees_1" that is automaticaly created when joining tables in Relationships Window?)
Hello, Zoran.

Use the same table name. Numbered alias is a Relationships pane feature, nothing more.
DRI set this way will not allow to delete PK from table view / form either.
However delete query will work as expected.

Regards,
Fish
Oct 14 '08 #4
Thanks,
this helped. I'm fresh to MS Access and all this VBA stuff...(this is an excuse for such a simple question)
Oct 15 '08 #5
NeoPa
32,556 Expert Mod 16PB
No excuse required :)

Self referencing tables are not a simple concept to grasp. Logical possibly, but not simple.
Oct 15 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Harry | last post by:
CSS Description: Table contains a column of td cells with links. Achieved: 1. Entire link cells highlight when hovered. 2. The entire area of these cells are clickable. Problem:
11
by: Drew | last post by:
I would like to explore some different ways to build a link out of the following string from a db, Fiscal; swvtc/fiscal/fiscaldata.asp: Records; swvtc/records/recordsdata.asp: Which is just a...
12
by: turtle | last post by:
I am creating a database that tracks the Techinical Skills of our engineers. There are about about 200 techinical skills that they will need to fill out by giving themselves a ranking. Some...
7
by: MacDermott | last post by:
I need to programmatically create a link to a table in an MDB on a CD, so I can read data from it. Access objects that it can't create an LDB file in the same directory as the MDB. Is there any...
1
by: darrel | last post by:
I have a form that has a 'sub-form' in it that updates a separate table. I can easily add records to this table from within this page. To delete, though, I've been redirecting to a different...
7
by: ApexData | last post by:
Hello I currently Link the FE/BE using the LinkTables Option and the Linked Table Manager. Any time I need to move the BE to another location, I have to go through this process over again. I...
15
by: Killer42 | last post by:
Hi all. Ok, I'm using VB6 but I think the answer to this (if there is one) is more likely to be found in the Access forum. I have a situation where I've got tens of millions of records, spread...
5
by: Xu, Qian | last post by:
Hello All, I have some problem by simulating a link click using javascript. The webpage uses a js-library named interface (jQuery like) ------------------------------ <a id="foo" href="#">Try...
3
by: Roger | last post by:
I've got a sql server view, that I link as a table in access97 after initial linking, I can open the table and add more records (ie. the >* vcr button is enabled) the connect string for the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.