VRS and MS Access

Want to post something that doesn't quite fit into the other forums? This is the place for that.
Post Reply
oakview
Posts: 4
Joined: Sat Apr 28, 2018 1:42 pm

VRS and MS Access

Post by oakview » Tue Feb 26, 2019 11:03 am

Hi,

I have 2 RPi receivers merging to my VRS. From this I create a daily "flights" report that I convert into a text file that I import to my Access database.

After a year I now have a collection of over 15,500 aircraft and over 750,000 flights, importing the daily flights isn't a problem plus it gives me chance to see what oddities/interesting aircraft have been active during he day.

The problem I have is the hours finding details of new ICAO's that crop up everyday. After filtering the "Russian" and known rubbish out I can still end up with quite a list of new contacts to add to my Access.

I know the VRS software downloads and updates these details automatically, so I'm wondering if there's a way for me to update my Access aircraft list from VRS, or even a way to "read" the aircraft details on the VRS server?

Many thanks in advance.
Dave.

agw
Posts: 2241
Joined: Fri Feb 17, 2012 3:20 am

Re: VRS and MS Access

Post by agw » Tue Mar 05, 2019 12:32 am

VRS will be logging the flights and aircraft details to your BaseStation.sqb if you have the database writer plugin installed. If you don't have the plugin installed then it'll still cache details of online aircraft lookups to AircraftOnlineLookupCache.sqb in the VRS configuration folder (Help | About, there's a link to the folder along the bottom of the window).

Both files are SQLite database files. SQLite is in very wide use. You can find documentation and download a command-line utility to interrogate SQLite databases here:

https://sqlite.org/index.html

There are APIs aplenty for reading SQLite files, ditto GUI applications for interrogating SQLite database files.

Regarding picking out details of new aircraft - the Aircraft table in BaseStation.sqb has created and last-modified timestamps, they will be updated when new aircraft are written or existing aircraft brought up-to-date. I think there's a similar pair of timestamps in AircraftOnlineLookupCache.sqb.

oakview
Posts: 4
Joined: Sat Apr 28, 2018 1:42 pm

Re: VRS and MS Access

Post by oakview » Tue Mar 05, 2019 3:30 pm

Hi,

Firstly thank you for the help. I have downloaded/installed DB Browser for SQlite, made a copy of the main RPi's BaseStation.sqb file to mess with. As you say everything is there I need.

I'm looking into ways of grabbing the time filtered aircraft data into a txt file that can be imported into Access, I'd rather not use csv files as Excel screws up the ICAO numbers and adds random characters to place names etc.

Many thanks for putting me on the right road with this, when I get sorted it will save hours and turn the hobby back into a hobby and not the chore it was becoming.

Dave

Post Reply