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