日志
在Access中查询IP地址归属地
||
在Access中处理IP地址的情形是比较罕见的。在这里只是小注一下，万一有网友需要，当作参考思路。
查询IP归属地，我们就以网络上常见的纯真IP库，作为归属地数据库。
下载纯真IP库最新版，导出为Txt。新建一个acced数据库，设计表
因为进行IP地址查询，毫无疑问，把IP地址转化为数字，然后进行比较查询，是最快的。
而IP地址转化为数字，会超过Long大小。它的大小是Ulong，很遗憾，vba没有Ulong数据类型，所以我们用currency代替，并且定义currency小数位为0。
'IP地址转换成为数字的函数
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
'数字转换成为IP地址的函数
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转成ulong的方法
Function LongToCurrency(LongInfo As Long) As Currency
If LongInfo >= 0 Then
'正整数无需转换
LongToCurrency = LongInfo
Else
'进行Long类型负数到正数的转换
LongToCurrency = 2147483647 + (2147483648# + LongInfo + 1)
End If
End Function
'更新IPAddress1表的开始地址转成数字
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结束地址转成数字
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千字
