Error Number -2147418113 : Unexpected Failure

[LittleSmudge]

I have an Access 2000 Front end that links to a MySQL back end via ODBC.

A certain subform has a record source that is a UNION query made up of two SELECT queries which each look at Past Through queries as their data source.

This all works fine on most users machines.
However, on 3 specific machines I get a problem.
Each SELECT component works in its own right but the UNION query will not.
( If I paste the SQL string into a query it will not change out of SQL view into datasheet view )

At run time on these three troublesome machines I just get a pop-up error message box saying

"Error Number -2147418113 Unexpected failure "

Too right it is 'Unexpected'


Any one know what's causing the problem ?



If it helps any at all the full SQL is

strSQL = "SELECT ItemId, InvoiceRef, ItemText, " _
       & "Sum(ItemNett) AS LineItemNett, " _
       & "Sum(ItemNett *  VATValue) / 100 AS ItemVATValue " _
       & "FROM tblVAT INNER JOIN (tbl" & gstrInvoiceType & "Item " _
       & "INNER JOIN tbl" & gstrInvoiceType & "ItemBreakdown " _
       & "ON tbl" & gstrInvoiceType & "Item.ItemId " _
       & "= tbl" & gstrInvoiceType & "ItemBreakdown.ItemRef) " _
       & "ON tblVAT.VATId = tbl" & gstrInvoiceType & "ItemBreakdown.VATRef " _
       & "WHERE CodeRef <> '19999999' " _
       & "GROUP BY ItemId, InvoiceRef, ItemText " _
       & "HAVING InvoiceRef = " & lngFKValue & " " _
       & "UNION " _
       & "SELECT ItemId, InvoiceRef, ItemText,0,0 " _
       & "FROM tbl" & gstrInvoiceType & "Item " _
       & "LEFT JOIN tbl" & gstrInvoiceType & "ItemBreakdown " _
       & "ON tbl" & gstrInvoiceType & "Item.ItemId " _
       & "= tbl" & gstrInvoiceType & "ItemBreakdown.ItemRef " _
       & "WHERE (CodeRef = '19999999' OR IsNull(ItemRef)) " _
       & "AND ItemId Not IN (SELECT ItemId " _
       & "FROM qryPTvItem " _
       & "INNER JOIN qryPTvItemBreakdown " _
       & "ON qryPTvItem.ItemId = qryPTvItemBreakdown.ItemRef " _
       & "WHERE CodeRef <> '19999999' GROUP BY ItemId ) " _
       & "AND InvoiceRef = " & lngFKValue & " " _
       & "ORDER BY ItemId "

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[dhookom]

The length of your statement might be an issue. I would first try divide you one statement into at least two like:

strSQL = "SELECT ItemId, InvoiceRef, ItemText, " _
       & "Sum(ItemNett) AS LineItemNett, " _
       & "Sum(ItemNett *  VATValue) / 100 AS ItemVATValue " _
       & "FROM tblVAT INNER JOIN (tbl" & gstrInvoiceType & "Item " _
       & "INNER JOIN tbl" & gstrInvoiceType & "ItemBreakdown " _
       & "ON tbl" & gstrInvoiceType & "Item.ItemId " _
       & "= tbl" & gstrInvoiceType & "ItemBreakdown.ItemRef) " _
       & "ON tblVAT.VATId = tbl" & gstrInvoiceType & "ItemBreakdown.VATRef " _
       & "WHERE CodeRef <> '19999999' " _
       & "GROUP BY ItemId, InvoiceRef, ItemText " _
       & "HAVING InvoiceRef = " & lngFKValue & " "
strSQL=strSQL & "UNION " _
       & "SELECT ItemId, InvoiceRef, ItemText,0,0 " _
       & "FROM tbl" & gstrInvoiceType & "Item " _
       & "LEFT JOIN tbl" & gstrInvoiceType & "ItemBreakdown " _
       & "ON tbl" & gstrInvoiceType & "Item.ItemId " _
       & "= tbl" & gstrInvoiceType & "ItemBreakdown.ItemRef " _
       & "WHERE (CodeRef = '19999999' OR IsNull(ItemRef)) " _
       & "AND ItemId Not IN (SELECT ItemId " _
       & "FROM qryPTvItem " _
       & "INNER JOIN qryPTvItemBreakdown " _
       & "ON qryPTvItem.ItemId = qryPTvItemBreakdown.ItemRef " _
       & "WHERE CodeRef <> '19999999' GROUP BY ItemId ) " _
       & "AND InvoiceRef = " & lngFKValue & " " _
       & "ORDER BY ItemId "

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LittleSmudge]

Done that Duuane - no improvement.

I've go a line
Debug.Print strSQL after the code posted previously and that works fine.
If I then past the result from the immediate window into a query - it is the query that fails not the text string creation.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

You may try to replace this:
& "ORDER BY ItemId "
By this:
& "ORDER BY 1"

You may also test the value of InvoiceRef in the WHERE clause instead of the HAVING.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LittleSmudge]

TWO more pieces of information come to light.

1)
If I remove the

       & "AND ItemId Not IN (SELECT ItemId " _
       & "FROM qryPTvItem " _
       & "INNER JOIN qryPTvItemBreakdown " _
       & "ON qryPTvItem.ItemId = qryPTvItemBreakdown.ItemRef " _
       & "WHERE CodeRef <> '19999999' GROUP BY ItemId ) " _

clause it all works fine on all machines


2)
The problem machines are all running Windoz98 whereas the non-problem machines are Windoz2000 or WindozXP





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

Oh - and all of the machines are fully up to date with MDAC2.8 - so I'm confident it's not an MDAC problem.

( All machines running Office 2000 )

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.