Page 1 of 1

A simple VBScript to update MS Access Database

PostPosted: Fri Dec 30, 2005 8:50 am
by Chris H
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.

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

PostPosted: Fri Dec 30, 2005 8:54 am
by streaker69
Cool script, and there had been some people asking about that.

Question: Does it require any certain version of the MSJet or MDAC for it to run?

PostPosted: Fri Dec 30, 2005 9:01 am
by Chris H
Jet 4.0 should be sufficient. I should have but forgot to mention, the version of Access I used is 2003 but this should work with an Access 2000 database. I don't have Access 2000 to create and test one.

If someone wants to use my database as a template I can email it or upload it somewhere if someone has webspace.

PostPosted: Fri Dec 30, 2005 9:03 am
by streaker69
Chris H wrote:Jet 4.0 should be sufficient. I should have but forgot to mention, the version of Access I used is 2003 but this should work with an Access 2000 database. I don't have Access 2000 to create and test one.

If someone wants to use my database as a template I can email it or upload it somewhere if someone has webspace.


You know there is an SQL command that you could send that will create the db struture for you as long as the file exists?

But if you were to attach a blank database with the structure created to your original post that would probably be a big help for people that don't even have access to begin with.

PostPosted: Fri Dec 30, 2005 9:09 am
by Chris H
streaker69 wrote:You know there is an SQL command that you could send that will create the db struture for you as long as the file exists?

But if you were to attach a blank database with the structure created to your original post that would probably be a big help for people that don't even have access to begin with.


Yeah, that is true. That would take the work out of it for everyone.

I just noticed the attachments button, I'll attach an empty version of my database to my first post.

PostPosted: Fri Dec 30, 2005 9:11 am
by streaker69
Chris H wrote:Yeah, that is true. That would take the work out of it for everyone.

I just noticed the attachments button, I'll attach an empty version of my database to my first post.


Zip it first.

PostPosted: Fri Dec 30, 2005 9:11 am
by wrzwaldo
Chris H wrote:Yeah, that is true. That would take the work out of it for everyone.

I just noticed the attachments button, I'll attach an empty version of my database to my first post.



Nice! I've been wanting to write one but have been too busy [insert lazy] to do it.

Thanks

PostPosted: Fri Mar 03, 2006 7:12 pm
by tuvelc
I was trying the script but I do not understand than it so happens that he does not store nothing in data base to me, I am working access 2003 want knowing that it can be happening that he does not store nothing. I expect ready answer.

Tuvelc, mi email is tuvelc@yahoo.es

PostPosted: Fri Mar 03, 2006 7:15 pm
by streaker69
tuvelc wrote:I was trying the script but I do not understand than it so happens that he does not store nothing in data base to me, I am working access 2003 want knowing that it can be happening that he does not store nothing. I expect ready answer.

Tuvelc, mi email is tuvelc@yahoo.es


I believe you saying that you're trying to make this work, but it's not inserting any data into the Database.

Did you put the APS.MDB file in the correct path, or update the script to indicate where that file is located?

Did you start the script in NS?

PostPosted: Sat Mar 04, 2006 3:35 am
by goldfndr
The functionality of this script has been merged into the newest release of the [thread=12512]Master script for NetStumbler[/thread] (set "UseDatabase" to True). And more - it will automatically create the database and tables for you and can filter. If you continue to have trouble with this thread's script, the "Master script" might be less hassle.

PostPosted: Sat Mar 04, 2006 5:22 pm
by tuvelc
I have a gps and netstumbler but the scritp don't save any data in the database, i want to help me pls with the script

PostPosted: Sat Mar 04, 2006 8:19 pm
by streaker69
tuvelc wrote:I have a gps and netstumbler but the scritp don't save any data in the database, i want to help me pls with the script


Then answer the damn questions that were already asked. Jumping Geebus on pogo stick we're not goddamn mind readers.

PostPosted: Sat Mar 04, 2006 9:20 pm
by Airstreamer
streaker69 wrote:Then answer the damn questions that were already asked. Jumping Geebus on pogo stick we're not goddamn mind readers.

I'll bet I know what YOU'RE thinking!!:D