A simple VBScript to update MS Access Database

Take a script, leave a script - Like the penny tray only different

A simple VBScript to update MS Access Database

Postby Chris H » 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.

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).
Attachments
aps.zip
(7.29 KiB) Downloaded 388 times
Chris H
Mini Stumbler
 
Posts: 11
Joined: Wed Dec 28, 2005 10:00 am

Postby streaker69 » Fri Dec 30, 2005 8:54 am

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?
Treat your gun like your genitals, only whip it out when it's absolutely necessary.
User avatar
streaker69
 
Posts: 11867
Joined: Thu Jul 08, 2004 10:09 am
Location: Virginville, BlueBall, Bird In Hand, Intercourse, Paradise, PA

Postby Chris H » Fri Dec 30, 2005 9:01 am

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.
Chris H
Mini Stumbler
 
Posts: 11
Joined: Wed Dec 28, 2005 10:00 am

Postby streaker69 » Fri Dec 30, 2005 9:03 am

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.
Treat your gun like your genitals, only whip it out when it's absolutely necessary.
User avatar
streaker69
 
Posts: 11867
Joined: Thu Jul 08, 2004 10:09 am
Location: Virginville, BlueBall, Bird In Hand, Intercourse, Paradise, PA

Postby Chris H » Fri Dec 30, 2005 9:09 am

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.
Chris H
Mini Stumbler
 
Posts: 11
Joined: Wed Dec 28, 2005 10:00 am

Postby streaker69 » Fri Dec 30, 2005 9:11 am

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.
Treat your gun like your genitals, only whip it out when it's absolutely necessary.
User avatar
streaker69
 
Posts: 11867
Joined: Thu Jul 08, 2004 10:09 am
Location: Virginville, BlueBall, Bird In Hand, Intercourse, Paradise, PA

Postby wrzwaldo » Fri Dec 30, 2005 9:11 am

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
wrzwaldo
 
Posts: 8995
Joined: Sun Dec 14, 2003 12:43 pm

Postby tuvelc » Fri Mar 03, 2006 7:12 pm

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
tuvelc
Mini Stumbler
 
Posts: 2
Joined: Sun Jan 22, 2006 7:00 pm

Postby streaker69 » Fri Mar 03, 2006 7:15 pm

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?
Treat your gun like your genitals, only whip it out when it's absolutely necessary.
User avatar
streaker69
 
Posts: 11867
Joined: Thu Jul 08, 2004 10:09 am
Location: Virginville, BlueBall, Bird In Hand, Intercourse, Paradise, PA

Postby goldfndr » Sat Mar 04, 2006 3:35 am

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.
goldfndr
 
Posts: 181
Joined: Sat Aug 28, 2004 3:30 am
Location: Bellevue, WA, USA

Postby tuvelc » Sat Mar 04, 2006 5:22 pm

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
tuvelc
Mini Stumbler
 
Posts: 2
Joined: Sun Jan 22, 2006 7:00 pm

Postby streaker69 » Sat Mar 04, 2006 8:19 pm

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.
Treat your gun like your genitals, only whip it out when it's absolutely necessary.
User avatar
streaker69
 
Posts: 11867
Joined: Thu Jul 08, 2004 10:09 am
Location: Virginville, BlueBall, Bird In Hand, Intercourse, Paradise, PA

Postby Airstreamer » Sat Mar 04, 2006 9:20 pm

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
"But when we disarmed They sold us and delivered us bound to our foe,
And the Gods of the Copybook Headings said: "Stick to the Devil you know.""

- Rudyard Kipling
User avatar
Airstreamer
 
Posts: 2703
Joined: Sun Nov 07, 2004 9:26 pm
Location: A little North of Reason


Return to Scripts

Who is online

Users browsing this forum: No registered users and 1 guest

cron