July 24, 2008 at 3:43 pm
Well, here's one I would never expect. I was tasked with writing a query against an old Visual FoxPro Database (.dbc file) via ODBC, so that a web-based form could help pull the old data for conversion into a new system. Of course, I was not provided a local copy of IIS nor any means to reproduce the webform. I downloaded a free copy of WinSQL to do the work after finding that working through MS Access for the particularly large number of tables was becoming a bit of a nightmare.
Wrote the query, and it appears to be working, but in the investigation of some odd results where some data, should, in theory, not be coming up, I've been asked to look into it, and found that if I run that same query in MS Access, it produces zero records unless I eliminate a reference to one specific "logical" field, or at least make it's criteria "Is Not Null" instead of merely placing a False value in the column of the query for that field.
Net result of that is that I then get just 1 record, which disagreed with the results that the web-form's query was getting through ADODB. Thus I decided to implement that query in ADODB methods using MS Access's VBA. Sure enough, I get the same two record result. Looking at the field values as displayed, another logical field is -1 for both records in the web query, but 0 for the "missing" record when Access's table based query runs in such a way as to produce both records by not limiting the value of this 2nd logical field.
How can this be? Does MS Access have some kind of serious data integrity problems using ODBC unless you also use ADODB? Or is it the other way around ? Either way I'm looking at a potential data integrity issue, and I'm baffled.
Anyone?
P.S. I have to hold off on too much detail due to propietary information, but if needed, I can at least provide some basic data type information for the fields in question.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 25, 2008 at 12:39 pm
Holy Connection String, Batman...
The problem was resolved while working with another contractor, and we started investigating the connection string, and found that simply changing DELETED=NO to DELETED=YES fixes the problem such that we only get the 1 record, which is what should be happening. I was more than a bit flummoxed on this, and had no idea we had that incorrect setting hanging around. Live and learn...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply