日志
在Access中查詢IP地址歸屬地
||
在Access中處理IP地址的情形是比較罕見的。在這里只是小注一下，萬一有網(wǎng)友需要，當(dāng)作參考思路。
查詢IP歸屬地，我們就以網(wǎng)絡(luò)上常見的純真IP庫，作為歸屬地數(shù)據(jù)庫。
下載純真IP庫最新版，導(dǎo)出為Txt。新建一個acced數(shù)據(jù)庫，設(shè)計表
因為進(jìn)行IP地址查詢，毫無疑問，把IP地址轉(zhuǎn)化為數(shù)字，然后進(jìn)行比較查詢，是最快的。
而IP地址轉(zhuǎn)化為數(shù)字，會超過Long大小。它的大小是Ulong，很遺憾，vba沒有Ulong數(shù)據(jù)類型，所以我們用currency代替，并且定義currency小數(shù)位為0。
'IP地址轉(zhuǎn)換成為數(shù)字的函數(shù)
Function IPToLong(ByVal IP As String) As Currency
Dim tmp As Long
Dim TmpIP As String
Dim i As Long
TmpIP = Replace(IP, ".", "\")
For i = 3 To 0 Step -1
tmp = Val(TmpIP)
IPToLong = IPToLong + tmp * 256 ^ i
TmpIP = Replace(TmpIP, tmp & "\", "", , 1)
Next
End Function
'數(shù)字轉(zhuǎn)換成為IP地址的函數(shù)
Function LongToIP(ByVal IPLong As Long) As String
Dim a(3)
Dim i As Long, idx As Long, m As Long
For i = 3 To 0 Step -1
m = 256 ^ i
a(idx) = IPLong \ m
IPLong = IPLong Mod m
idx = idx + 1
Next
LongToIP = Join(a, ".")
End Function
'在這里用不到，不過這是將long轉(zhuǎn)成ulong的方法
Function LongToCurrency(LongInfo As Long) As Currency
If LongInfo >= 0 Then
'正整數(shù)無需轉(zhuǎn)換
LongToCurrency = LongInfo
Else
'進(jìn)行Long類型負(fù)數(shù)到正數(shù)的轉(zhuǎn)換
LongToCurrency = 2147483647 + (2147483648# + LongInfo + 1)
End If
End Function
'更新IPAddress1表的開始地址轉(zhuǎn)成數(shù)字
Sub UpdateStarIPLong()
Dim strsql As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
strsql = "update IpAddress1 set StarIPlong = ipTolong(StarIP) "
conn.Execute strsql
End Sub
'更新IPAddress1表的IP結(jié)束地址轉(zhuǎn)成數(shù)字
Sub UpdateEndIPLong()
Dim strsql As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
strsql = "update IpAddress1 set EndIPlong = ipTolong(EndIP) "
conn.Execute strsql
End Sub
更新完后的情況：
接下來，就很容易查到歸屬地了。此處略去1千字
