If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access crashes on query parsing NMEA (GPS) data
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 writing. 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. |
Thread Tools | |
Display Modes | |
|
|