Results 1 to 11 of 11

Thread: ODBC fail

  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I have the following query, as it is it works fine.
    PARAMETERS [forms]![frm EOM]![DateFrom] DateTime, [forms]![frm EOM]![DateTo] DateTime, [forms]![frm EOM]![Company] Text ( 255 );
    SELECT WinifredServers.server AS ServHosp, [WinifredScript].[Server] & " - " & [nname] AS [leavethis in here ServHospXXX], "NHS" AS Description, Sum(WinifredScript.sgovrecamt) AS Amt, "N" AS LookupVal, 6 AS QryNo
    FROM ((WinifredServers INNER JOIN WinifredScript ON WinifredServers.server = WinifredScript.Server) INNER JOIN WinifredWards ON (WinifredScript.swardno = WinifredWards.wardno) AND (WinifredScript.Server = WinifredWards.Server)) INNER JOIN WinifredNursing ON (WinifredWards.wardhosp = WinifredNursing.ncode) AND (WinifredWards.Server = WinifredNursing.Server)
    WHERE (((WinifredServers.entity)=[forms]![frm EOM]![Company]) AND ((WinifredScript.sdispdate) Between [forms]![frm EOM]![DateFrom] And [forms]![frm EOM]![DateTo]))
    GROUP BY WinifredServers.server, [WinifredScript].[Server] & " - " & [nname], "NHS", "N", 6
    ORDER BY [WinifredScript].[Server] & " - " & [nname];
    The query may function but it gives the wrong answer obviously.

    If i take out the 2nd variable i get an odbc fail error. These tables are SQL Server 2005.

    If i build it from scratch it still errors.

    The query used to work but i have no idea now as to why it doesn't.

    If i use this query as the source of another query it works, but this is hardly a nice solution.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What do you mean by "The query may function but it gives the wrong answer obviously"?

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='769306' date='06-Apr-2009 17:08']What do you mean by "The query may function but it gives the wrong answer obviously"?[/quote]
    It runs to completion and gives a result albeit the wrong result.

    A little more info is that the table WinifredScript has replaced a table called WinifredEOMBillingReport.

    I have copied this query into another database and it fails there too.

    Could it have anything to do with changing from an Access linked table to a SQL Server linked table?
    Do the date comparisons stay the same?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The problem with investigating a complex query such as the one you posted is that there are many factors that could cause a problem.

    I'd experiment with a very simple query based on just one table, with criteria on a date/time field to see how this is handled for a SQL Server linked table.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='769310' date='06-Apr-2009 18:45']The problem with investigating a complex query such as the one you posted is that there are many factors that could cause a problem.

    I'd experiment with a very simple query based on just one table, with criteria on a date/time field to see how this is handled for a SQL Server linked table.[/quote]
    Good point although i have tried it with just 2 tables and it doesn't work.

    The annoying thing is that other very similar queries work ok.

    I wil try that tomorrow though. Thanks

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Patt

    I don't know much about SQL Server, but in general when you include dates as parameters in SQL statements you need to convert to mm/dd/yyyy format to get the right results.

    Between #" & format([forms]![frm EOM]![DateFrom],"mm/dd/yyyy") & "# And # & format[forms]![frm EOM]![DateTo],"mm/dd/yyyy") & "#))
    Regards
    John



  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='johnhutchison' post='769350' date='07-Apr-2009 02:13']Patt

    I don't know much about SQL Server, but in general when you include dates as parameters in SQL statements you need to convert to mm/dd/yyyy format to get the right results.

    Between #" & format([forms]![frm EOM]![DateFrom],"mm/dd/yyyy") & "# And # & format[forms]![frm EOM]![DateTo],"mm/dd/yyyy") & "#))[/quote]
    I agree that you need this code when building a query in VBA code, but i have the range of dates embedded in a query then use code to build a query that uses the original query (Q1) and supply the dates thru code like :
    qdf.parameters("[forms]![frm EOM]![DateFrom]") = [forms]![frm EOM]![DateFrom]

    When building the query Q2 that refers to Q1 (which has parameters) do I need to include the Parameters statement in the new query Q2 as below?
    eg.
    ssql = "SELECT Q1.* FROM Q1 INER JOIN RefTable as R ON Q1.linkfld = R.linkfld
    set qdf.currentdb.QueryDefs("Q2")
    qdf.sl = ssql
    qdf.parameters("[forms]![frm EOM]![DateFrom]") = [forms]![frm EOM]![DateFrom]
    qdf.parameters("[forms]![frm EOM]![DateTo]") = [forms]![frm EOM]![DateTo]
    Set rs = qdf.OpenRecordset()
    Do While ......

    Does my ssql = ... statement ned to be:
    sSql = "PARAMETERS [forms]![frm EOM]![DateFrom] DateTime, [forms]![frm EOM]![DateTo] DateTime, [forms]![frm EOM]![Company] Text ( 255 );"
    ssql = ssql & " SELECT Q1.* FROM Q1 INER JOIN RefTable as R ON Q1.linkfld = R.linkfld"

    I will be experimenting this morning with all this, i have about 24 queries that work fine with Access linked tables but somehow i have hit a hurdle when i changed it to SQL Server linked tables.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='769310' date='06-Apr-2009 18:45']The problem with investigating a complex query such as the one you posted is that there are many factors that could cause a problem.

    I'd experiment with a very simple query based on just one table, with criteria on a date/time field to see how this is handled for a SQL Server linked table.[/quote]
    I solved it, i didn't tell you everything about it, I had 4 parameters in each query originally for the Access table. This was reduced to 3 when looking at the SQL Server table but i forgot to delete the 4th parameter from each of the 24 queries.
    When i ran each query from the database window they seemed fine, however when executing them in code they failed.

    I wont get caught by that again (famous last words) .

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Glad you were able to solve it yourself!

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='769470' date='07-Apr-2009 11:51']Glad you were able to solve it yourself![/quote]
    Well not quite, there is still this query that only has the 3 correct parameters but bombs if i take out the table tblClassConv, this table is not required by the query. I am leaving that table in there with a comment.

    The query is:

    PARAMETERS [forms]![frm EOM]![DateFrom] DateTime, [forms]![frm EOM]![DateTo] DateTime, [forms]![frm EOM]![Company] Text ( 255 );
    SELECT WinifredScript.Server, "NHS" AS Description, Sum(WinifredScript.sgovrecamt) AS Amt, "N" AS LookupVal, 6 AS QryNo
    FROM (WinifredServers INNER JOIN WinifredScript ON WinifredServers.server = WinifredScript.Server) INNER JOIN [tbl ClassConv] ON WinifredScript.sclass = [tbl ClassConv].sclass
    WHERE (((WinifredServers.entity)=[forms]![frm EOM]![Company]) AND ((WinifredScript.sdispdate) Between [forms]![frm EOM]![DateFrom] And [forms]![frm EOM]![DateTo]))
    GROUP BY WinifredScript.Server, "NHS", "N", 6
    ORDER BY WinifredScript.Server;

    It bombs from the database window as well as in the VBA code.

    Just one of those mysteries that im bewildered by.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='patt' post='769499' date='07-Apr-2009 18:11']Well not quite, there is still this query that only has the 3 correct parameters but bombs if i take out the table tblClassConv, this table is not required by the query. I am leaving that table in there with a comment.

    The query is:

    PARAMETERS [forms]![frm EOM]![DateFrom] DateTime, [forms]![frm EOM]![DateTo] DateTime, [forms]![frm EOM]![Company] Text ( 255 );
    SELECT WinifredScript.Server, "NHS" AS Description, Sum(WinifredScript.sgovrecamt) AS Amt, "N" AS LookupVal, 6 AS QryNo
    FROM (WinifredServers INNER JOIN WinifredScript ON WinifredServers.server = WinifredScript.Server) INNER JOIN [tbl ClassConv] ON WinifredScript.sclass = [tbl ClassConv].sclass
    WHERE (((WinifredServers.entity)=[forms]![frm EOM]![Company]) AND ((WinifredScript.sdispdate) Between [forms]![frm EOM]![DateFrom] And [forms]![frm EOM]![DateTo]))
    GROUP BY WinifredScript.Server, "NHS", "N", 6
    ORDER BY WinifredScript.Server;

    It bombs from the database window as well as in the VBA code.

    Just one of those mysteries that im bewildered by.[/quote]
    I have found the original problem, the following query is the final version, you will notice that the fixed fields are now no longer grouped (Access doesn't care on it's linked tables):

    PARAMETERS [forms]![frm EOM]![DateFrom] DateTime, [forms]![frm EOM]![DateTo] DateTime, [forms]![frm EOM]![Company] Text ( 255 );
    SELECT WinifredScript.Server, "NHS" AS Description, Sum(WinifredScript.sgovrecamt) AS Amt, "N" AS LookupVal, 6 AS QryNo
    FROM WinifredServers INNER JOIN WinifredScript ON WinifredServers.server = WinifredScript.Server
    WHERE (((WinifredServers.entity)=[forms]![frm EOM]![Company]) AND ((WinifredScript.sdispdate) Between [forms]![frm EOM]![DateFrom] And [forms]![frm EOM]![DateTo]))
    GROUP BY WinifredScript.Server
    ORDER BY WinifredScript.Server;

    The only way i found this was to import the query to another database and the ODBC Driver was kind enough to inform me that:
    ODBC Call failed
    [Microsoft][ODBC Sql Server Driver][SQL Server][Each Group By must contain at least one column that is not an outer reference (#164)

    All it gave me originally was:
    ODBC Call failed

Posting Permissions

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