Get next available address (1 Viewer)

[rothjm]

Looking for some assistance to steer me in the right direction.

I have an equipment form with the IP address on it. We use static IP's (don't ask). I need to place a button on the form attached to some code to find the next available IP address.
We have numerous subnet's.
I want to prompt them for the subnet.
I.E. Have them put in the net address of 10.10.10.X and have the code find the next available IP address of say: 10.10.10.123.
The fields are all seperate (IP_01, IP_02 etc.) and they are integer data type.
The code should be able to handle any subnet.
We support other entity's and they all have a different subnet's (like 10.50.X.X, and some that start with 192.168.254.X) ETC.

Any advice would be greatly appreciated. Anyone done anything like that?

TIA

 

[dcx693]

I assume the ip's could be reused? So if one device gets removed, you'd like to reuse the ip address?

You can use a custom function that searches for the subnet the user inputs and finds all matching ip addresses. Then have another function find the least available ip that's not been assigned.

 

[namliam]

try searching for "custom primary key" or something like that. Lotzzzz and lotz of samples out there...

GL

Regards

 

[rothjm]

Yes, the IP addresses are reused.
Thanks

 

[rothjm]

Hey dcx693,
Do I need to store the result of the first function in somekind of an array or tmp table?
How would you handle that?
Thanks again for helping the rookie!

 

[dcx693]

Actually, it could be very simple and just one function. Say, the user types in 10.10.10 and you want to find the find the first unused address. You could just look for 10.10.10.0 and keep going until you find the first unused address. If you didn't care about re-using address, you could just use a DMax function to find the largest value and add 1 to it (unless you're at 255 already).

 

[rothjm]

Cool, good insight.
I didn't think of that.
Thanks again

 

[rothjm]

I am pretty much a rookie with the VBA.
Can someone help get me going with the code?
I have the form to prompt the user for the subnet they wish to search and I have the entries in the variables.
I used: varIP1, varIP2, varIP3 and then our workstations start at address 100 so I start varIP4 at 100 and I know I need to increment by 1 and do the search.
Lets say my table with the IP addresses is named tblNet and the quadrants are stored seperately as integers. So there are 4 columns: IP1 IP2 IP3 IP4.
How would I setup a loop?
TIA

 

[dcx693]

See the attached db8.zip file. I actually handled it a bit differently than I originally suggested.

I set it up a form that would assign the next IP address by attemtping to add it to a table of existing IP address. I set up the table so that the combination of all four IP fields created a unique primary key. Thus, when it encounters a duplicate IP address, it does not add it, but attempts to add the next avavilable address. When it successfully adds an address it displays it. If all the ips from 100 to 255 are all taken it will display a message.

Look at the code in the form. It does 90% of the work. The other 10% was setting up the table so that all four ip fields together constituted the primary key.

 

[rothjm]

Great!
Thanks again, I truly appreciate the assistance!

 

[rothjm]

ONE LAST QUESTION

dcx693
Your code actually inserts the new address in the table.
If I just wanted to display the next IP address without inserting it into the table would I just change the INSERT statement to a SELECT statement? Would I also then need a WHERE clause?
Do all the IP fields need to be defined as a Primary Field for this to work?
Currently I have a MAC address of the NIC as the primary field.
Sorry to bug you again.
Thanks

 

[dcx693]

Yes, you can absolutely change the code so you do a select. That would search for whether or not the ip address exists in the table. Unfortunately, there is then no clear and easy way to just say "address found". You must open the query as a recordset, do a recordcount, and then see if it's 1 or 0 (hopefully, it will only be one of those values!). You must be careful though, to load the proper type of recordset, then you must be sure to move to the end of the recordset then go back to the front to load it all before you do your recordcount. Sounds complex, but it's the "proper" way to do it.

The easiser method is to use the skip the whole SELECT statement and do a Dlookup function to look to see if a particular combination of ip fields exists. It's a much slower method, but it does work. Look it up in the Access onlne help.