A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2010, 12:21 PM posted to microsoft.public.access.forms
Sandroid
external usenet poster
 
Posts: 16
Default "free" ip number when I remove device from database?

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));
  #2  
Old May 11th, 2010, 12:54 PM posted to microsoft.public.access.forms
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default "free" ip number when I remove device from database?

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

  #3  
Old May 11th, 2010, 01:00 PM posted to microsoft.public.access.forms
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default "free" ip number when I remove device from database?

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

  #4  
Old May 11th, 2010, 04:06 PM posted to microsoft.public.access.forms
Sandroid
external usenet poster
 
Posts: 16
Default "free" ip number when I remove device from database?

On 11 touko, 15:00, "PieterLinden via AccessMonster.com" u49887@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?
  #5  
Old May 11th, 2010, 05:45 PM posted to microsoft.public.access.forms
Sandroid
external usenet poster
 
Posts: 16
Default "free" ip number when I remove device from database?

On 11 touko, 18:06, Sandroid wrote:
On 11 touko, 15:00, "PieterLinden via AccessMonster.com" u49887@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 structu ID(primary),ip, ip2, ip3, ip4.
  #6  
Old May 13th, 2010, 02:12 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default "free" ip number when I remove device from database?

On Tue, 11 May 2010 09:45:12 -0700 (PDT), Sandroid
wrote:


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 structu ID(primary),ip, ip2, ip3, ip4.


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.
--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.