Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make table query works but Select does not (2000)

    I have an Access application that provides on screen enquiries for a SQL Server backend database. The SQL Server tables are all linked via a read only ODBC DSN. Mostly the application works fine. However, occasionally it incorrectly reports on the contents of the SQL tables. Looking deeper into one instance of this I found that I was getting the same incorrect results before the data even got to my queries, at the table level. I looked directly at one of the linked SQL Tables from Access and applied a filter to the view. Nothing complex, just order_id = nnnnn. The number of records displayed was correct, but the contents were wrong. I then knocked up a quick query, to return all fields from the same table, with the same selection criteria. Same result. Right number of records, wrong contents.

    However, when I converted the query to a make table query (no other changes), the resultant table was 100% correct, right number of records and correct contents. (as verified by running the query directly on the SQL Server).

    Can anyone suggest what is going on? Are there known circumstances where this occurs, or will I have to make all my Access queries that look at SQL Server data into make table queries?

    Ian

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Make table query works but Select does not (2000)

    We work in this mode with Access 2000 most of the time, and don't see any problems, so I suspect you may have an issue with ODBC drivers or with your version of Access. I believe there were some problems with ODBC linked tables in the initial release of Access 2000 - do you have SR-1 installed? You may also want to check your MDAC components for incompatible versions - there is a version checker that can be downloaded from the MS web site. Hope this helps debug the problem.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make table query works but Select does not (2000)

    I made a mistake in my original post, the database in question was running 97 not 2000. However, it is now on 2000 and my workstation is running SR-1. Same problem. I'm not sure what to do about MDAC - is it safe to just download and install the latest version(s) rather than sift through dozens of DLL version numbers, which I think is how the Microsoft version checker works?

    Wierd new information on the problem. The setup for this particular part of the application is very simple, a pop-up form based on a query based on a single SQL Server table (no messy joins to confuse things). The query selects records that match a Product code, a company ID and warehouse location (three criteria linked by ANDs) and sorts in reverse order by transaction date. When the form first displays it has the correct number of records and most, but not all, of the fields have the correct data in. Critically, the qty field, which shows how many units have moved, is not correct for all the records. On a whim I decided to reapply the sort using the option available when you right click a form field. Hey presto, the correct data appears. This aslo happens when I apply a filter from the form's interface.

    Clearly the correct data is there all the time, but it is not being displayed by queries or when the data is viewed in table view. It is only unveiled when a query becomes a make table query, or when a form based on the query is resorted (resorting the query does nothing) or filtered.

    I am confused (and worried - we have lots of these links to SQL Server here).

    Ian

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Make table query works but Select does not (2000)

    The version checker does run through all of the DLLs that are part of the MDAC and check that they are consistent - it will tell you if one or more are of the wrong vintage and you can actually update just those files. You didn't indicate which SQL Server version you were running, but we have seen cases where the SQL Server ODBC updates were applied in an inconsistent manner. One question - does this problem show up on all workstations, or only on one? If it's only on one then the MDAC is a likely candidate. From your post I think you are suggesting that more than one workstation is affected. If that is the case, are all workstations running a front-end on a server, or is the front-end downloaded to each workstation?
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •