A simple VBScript to update MS Access Database
Posted: Fri Dec 30, 2005 8:50 am
Here's a quick and dirty VBScript that will update a Microsoft Access database with the list of AP's in real time. You could easily adapt this to MySQL, but I use Access since it is more portable and sufficient for this application. Hopefully, the formatting is preserved.
You'll need an Access database with a table named "APs" and columns named:
BSSID
SSID
MaxSNR
Latitude
Longitude
Altitude
FixType
CapFlags
DateTime
Or you can use the attached one.
All of the columns are text fields, with BSSID being the primary key though it doesn't HAVE to have a primary key, it prevents the possibility of two identical BSSIDs being stored in the database. Obviously you can customize this as you see fit. I recommend Aedix for VBScript editing.
EDIT: You'll need to change the path of your access database in line 6.
EDIT part Deux: Attached a database you can use (for those of you without access to Access).
You'll need an Access database with a table named "APs" and columns named:
BSSID
SSID
MaxSNR
Latitude
Longitude
Altitude
FixType
CapFlags
DateTime
Or you can use the attached one.
All of the columns are text fields, with BSSID being the primary key though it doesn't HAVE to have a primary key, it prevents the possibility of two identical BSSIDs being stored in the database. Obviously you can customize this as you see fit. I recommend Aedix for VBScript editing.
- Code: Select all
Sub OnPositionChange(SSID, BSSID, CapFlags, MaxSNR, Lat, Lon, Alt, FixType)
'this function runs when NetStumbler updates the GPS location of an AP - ie strongest signal point
'we can use this to update our database in real time
If BSSID <> "000000000000" Then 'ignore bogus APs
Set MyConn = CreateObject("ADODB.Connection")
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Network Stumbler\aps.mdb"
Set RS = MyConn.Execute("SELECT * FROM APs WHERE BSSID = '" & BSSID & "'")
SSID = Replace(SSID, "'", "") 'remove characters from SQL query that will crash it
While Not RS.EOF
'BSSID exists in database already, update it with latest info
MyConn.Execute "UPDATE APs SET SSID = '" & SSID & "', MaxSNR = '" & MaxSNR & "', Latitude = '" & Lat & "', Longitude = '" & Lon & "', Altitude = '" & Alt & "', FixType = '" & FixType & "', CapFlags = '" & CapFlags & "', DateTime = '" & Now & "' WHERE BSSID = '" & BSSID & "'"
MyConn.Close
Exit Sub
Wend
'this BSSID does not exist, must be added
MyConn.Execute "INSERT INTO APs (BSSID, SSID, MaxSNR, Latitude, Longitude, Altitude, FixType, CapFlags) VALUES ('" & BSSID & "', '" & SSID & "', '" & MaxSNR & "', '" & Lat & "', '" & Lon & "', '" & Alt & "', '" & FixType & "', '" & CapFlags & "', " & Now & ")"
MyConn.Close
End If
End Sub
EDIT: You'll need to change the path of your access database in line 6.
EDIT part Deux: Attached a database you can use (for those of you without access to Access).