A simple VBScript to update MS Access Database

Chris H · 12-30-2005

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:

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

streaker69 · 12-30-2005

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?

Chris H · 12-30-2005

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.

streaker69 · 12-30-2005

Quote:

Originally Posted 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.

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.

Chris H · 12-30-2005

Quote:

Originally Posted by streaker69

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.

streaker69 · 12-30-2005

Quote:

Originally Posted by Chris H

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.

wrzwaldo · 12-30-2005

Quote:

Originally Posted by Chris H

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

tuvelc · 03-03-2006

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

streaker69 · 03-03-2006

Quote:

Originally Posted 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

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?

goldfndr · 03-04-2006

The functionality of this script has been merged into the newest release of the Master script for NetStumbler (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.

tuvelc · 03-04-2006

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

streaker69 · 03-04-2006

Quote:

Originally Posted 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

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

Airstreamer · 03-04-2006

Quote:

Originally Posted by streaker69

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!!

12-30-2005	#2 (permalink)
streaker69 Psychic Amish Stumbler Join Date: Jul 2004 Location: Virginville, BlueBall, Bird In Hand, Intercourse, Paradise, PA Posts: 11,708	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? __________________ "One of these days, I'm going to cut you to pieces." If you're offended by this post, please feel free to report it to one of the many helpful moderators of this forum. Thank you.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

12-30-2005	#3 (permalink)
Chris H Registered Member Join Date: Dec 2005 Posts: 11	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.

03-03-2006	#8 (permalink)
tuvelc Registered Member Join Date: Jan 2006 Posts: 2	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

03-04-2006	#10 (permalink)
goldfndr Knight Tooth Puller Join Date: Aug 2004 Location: Bellevue, WA, USA Posts: 182	The functionality of this script has been merged into the newest release of the Master script for NetStumbler (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.

03-04-2006	#11 (permalink)
tuvelc Registered Member Join Date: Jan 2006 Posts: 2	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