Access crashes on query parsing NMEA (GPS) data

Access crashes on query parsing NMEA (GPS) data

Post by J.T. van d » Thu, 19 Jun 2008 21:33:21

I have had a problem parsing a time-fields contained in NMEA-data
(record-type: $GPRMC) in an Access-query.
My original data source neatly had output every second, nicely matching the
maximum accuracy of time-keeping in Access (date/time-fields). The problem
started occurring when a different GPS was used and the time-information
included 1/100th of a second.
I found myself forced to split the original string, separate hours, minute,
and seconds (including decimal values for that seconds). After that I
rounded the seconds, only to find myself in the next troublespot.
This time the capabilities of Access in interpreting a time-string where the
limiting factor. Rounding the seconds to the nearest integer, causes a
problem for that final half a second of a minute: 59.51 and above round to
60. And 60 is not a valid input value (59 and below are).
My work around this was to calculate a real (double) as follows: hours/24 +
minutes/(24*60) + seconds/(24*60*60). This yields a fractional value for the
time past during the day, just as Access uses to store in a date/time field.
Using Hour(); Minute() and Second(), gave me the proper parts to rebuild the
final proper time using TimeSerial( Hours; Minutes; Seconds).

Yet even than the final step combining the correct time with the date in one
date/time-field would still crash Access. The error-messages shown on-screen
are not helpfull in diagnosing the problem. Nor is the data contained in the
Event Viewer-logs.

A company-internal guru from the IT-service desk, suggested that I may have
been overdoing the stacking off queries on top of each other. As work-around
the following works, store the results not as a query but as table and
proceed from the new table. Much more cumbersome, but it works.

Please note that this looks a lot like the problems that were supposedly
fixed by Service Pack 6 for the MS-Jet4.0-engine (which had the following
version number: 4.0.6218.0 ). Observe that this problem happened with
4.0.9511.0, a lot higher and the newest available for WinXP at the time of
MS-Jet 4 SP6 in a.o. mentioned in kb 309163.
Getting the newest MS-Jet-engine is a topic covered in kb 239114.

Clearly and old problem has reared it's head again.

Please fix.

1. Reading, parsing, logging and displaying GPS path and altitude (NMEA 0183 ) data

2. GPS NMEA (Loox "GPS instant fix" update problem)

I have an application that uses GPS NMEA information straight from
serial port (no GPS intermediate driver involved). It works on many
devices including Loox N560. However it appears that installation of
GPS instant fix update on this device breaks something as I have a
report from user that he can no longer access GPS data (nor can he
uninstall the update). Some other applications do work (maybe using
intermediate driver - I do not know). Does anyone have any relevant

Henryk Birecki

3. Changing the Read NMEA GPS to work on Mac OS X using usb?

4. NMEA (GPS protocol) parser

5. [ntp:questions] beginner needs help with gps nmea setup and pps


7. NMEA-Gps Receiver suggestions?

8. Has anyone written C# code to get NMEA from a Delorme Bluetooth GPS?

9. GPS NMEA time reader

10. beginner needs help with gps nmea setup and pps

11. NMEA GPS ignored

12. what wrong with my nmea gps receiver?

13. USB NMEA GPS: refclock_open: /dev/gps0: Permission denied

14. Reading GPS NMEA via serial port

15. GPS intermediate driver getting NMEA strings.