"free" ip number when I remove device from database?
in [Form]
|
Prev: Dlookup Question
Next: Sizing Access 2007 Forms
From: Sandroid on 11 May 2010 07:21 Access 2007. Problem is when I delete the device (which has foreign key, ipID) from device table the IP number from IP table (has ID, primary key) also deletes (relation 1:1). I want to "free" ip number when I remove device from database so IP number is not deleted and can be used later. How to accomplish this? Form looks for "free" addresses as follows: SELECT tblIP.ID, [ip] & "." & [ip2] & "." & [ip3] & "." & [ip4] AS [IP- number], tblDevice.ipID FROM tblIP LEFT JOIN tblDevice ON tblIP.ID = tblDevice.ipID ORDER BY tblIP.ip, tblIP.ip2, tblIP.ip3, tblIP.ip4 WHERE (((tblDevice.ipID) Is Null));
From: PieterLinden via AccessMonster.com on 11 May 2010 07:54 Sandroid wrote: >Access 2007. Problem is when I delete the device (which has foreign >key, ipID) from device table the IP number from IP >table (has ID, primary key) also deletes (relation 1:1). I want to >"free" ip number when I remove device from database so IP number is >not deleted and can be used later. How to accomplish this? > >Form looks for "free" addresses as follows: > >SELECT tblIP.ID, [ip] & "." & [ip2] & "." & [ip3] & "." & [ip4] AS >[IP- >number], tblDevice.ipID >FROM tblIP LEFT JOIN tblDevice ON tblIP.ID = tblDevice.ipID >ORDER BY tblIP.ip, tblIP.ip2, tblIP.ip3, tblIP.ip4 >WHERE (((tblDevice.ipID) Is Null)); Why are you deleting from tblIP at all? The usual way to do this is to either update the IP field(s) of the Device table, or delete the device entirely. In my opinion, this is definitely a case where you would want to store the separate octets *and* the full IP address so you can index and join on it. Then all the hard parts of this just plain go away. Or I suppose you could create an expression like you did in a plain select query (no joins) and then use that in a Find Unmatched query wizard... -- Message posted via http://www.accessmonster.com
From: PieterLinden via AccessMonster.com on 11 May 2010 08:00 Sandroid wrote: >Access 2007. Problem is when I delete the device (which has foreign >key, ipID) from device table the IP number from IP >table (has ID, primary key) also deletes (relation 1:1). I want to >"free" ip number when I remove device from database so IP number is >not deleted and can be used later. How to accomplish this? I must be forgetting how to read... or not awake yet. Do you have cascading deletes turned on between the two tables? If so, turn it off. -- Message posted via http://www.accessmonster.com
From: Sandroid on 11 May 2010 11:06 On 11 touko, 15:00, "PieterLinden via AccessMonster.com" <u49887(a)uwe> wrote: > Sandroid wrote: > >Access 2007. Problem is when I delete the device (which has foreign > >key, ipID) from device table the IP number from IP > >table (has ID, primary key) also deletes (relation 1:1). I want to > >"free" ip number when I remove device from database so IP number is > >not deleted and can be used later. How to accomplish this? > > I must be forgetting how to read... or not awake yet. Do you have cascading > deletes turned on between the two tables? If so, turn it off. > > -- > Message posted viahttp://www.accessmonster.com cascading turned off - no help referential integrity turned off - no help which way to create the relation from IP to Device or vice versa, does it matter?
From: Sandroid on 11 May 2010 12:45
On 11 touko, 18:06, Sandroid <santeri.virta...(a)gmail.com> wrote: > On 11 touko, 15:00, "PieterLinden via AccessMonster.com" <u49887(a)uwe> > wrote: > > > Sandroid wrote: > > >Access 2007. Problem is when I delete the device (which has foreign > > >key, ipID) from device table the IP number from IP > > >table (has ID, primary key) also deletes (relation 1:1). I want to > > >"free" ip number when I remove device from database so IP number is > > >not deleted and can be used later. How to accomplish this? > > > I must be forgetting how to read... or not awake yet. Do you have cascading > > deletes turned on between the two tables? If so, turn it off. > > > -- > > Message posted viahttp://www.accessmonster.com > > cascading turned off - no help > referential integrity turned off - no help > > which way to create the relation from IP to Device or vice versa, does > it matter? I don't want to delete IP address from tblIP, can I make tblIP lookup table then? how to make make sure that each ip address is used only once? tblIP structure: ID(primary),ip, ip2, ip3, ip4. |