"free" ip number when I remove device from database?

  • Thread starter Thread starter Sandroid
  • Start date Start date
S

Sandroid

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));
 
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.
Click to expand...

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?
 
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?
Click to expand...

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.
 
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.
Click to expand...

Lookup tables do more harm than good!

You can create a unique Index on the combination of the four ip fields; open
the table in design view, select the Indexes tool (like lightning hitting a
table), and type UniqueIP in the left column, and select IP in the right
column. On the next three rows put IP2, IP3 and IP4 in the right column under
IP. Check the Unique checkbox and it won't let you enter a duplicate IP.
 
Back
Top