ODBC 3146 error when querying linked tables. 1

[SunnyByfleet]

Odd one this.

Our company accountancy system is Swan. We have a Crystal Report that looks at three of the tables and joins them.

I am expanding on this with Access. I am using linked tables and querying them.

To speed up development, I have also exported the tables into native access. When I am developing the app, I point to the local tables, and when its live I will point to the live tables.

The three tables in question are:

For local:
L_SOR_TRANSACTIONS
L_SOR_SALES_ORDERS
L_SOR_COMPONENT_ALLOCS

For linked:
S_SOR_TRANSACTIONS
S_SOR_SALES_ORDERS
S_SOR_COMPONENT_ALLOCS

So to switch from local to linked, I just replave L_ with S_.

Pertinent fields in the tables are as follows:

SOR_SALES_ORDERS
---THIS.RECORD
---ORDER_NUMBER

SOR_TRANSACTIONS
---PARENT.RECORD

SOR_COMPONENT_ALLOCS
---ORDER_NUMBER

I haven't set up any explicit relationships. However, in the query I use inner joins:
SOR_SALES_ORDERS!THIS_RECORD = SOR_TRANSACTION!PARENT_RECORD
SOR_SALES_ORDERS!ORDER_NUMBER = SOR_COMPONENT_ALLOCS!ORDER_NUMBER

My problem is as follows, I have the query working fine when I point it to the local copy, but as soon as I point it to the linked tables I get an ODBC error:

Runtime error 3146.

ODBC -- call failed

I suspect it is the join causing the problem, in fact if I modify the query to have the SALES_ORDER table local and the other two link, then it works fine.

Where am I going wrong?

Here's the JOIN part of the query. Below it for completeness I've included the full query. In both cases, I am using the working code. To get the non-working code just change S_ to L_.


I'm going bald on this one, so all help is appreciated.

cQuery = cQuery + "(L_SOR_SALES_ORDERS AS SOR_SALES_ORDERS INNER JOIN "
cQuery = cQuery + "S_SOR_TRANSACTIONS AS SOR_TRANSACTIONS ON "
cQuery = cQuery + "SOR_SALES_ORDERS.THIS_RECORD=SOR_TRANSACTIONS.PARENT_RECORD) "
cQuery = cQuery + "INNER JOIN S_SOR_COMPONENT_ALLOCS AS SOR_COMPONENT_ALLOCS ON "
cQuery = cQuery + "SOR_SALES_ORDERS.ORDER_NUMBER = SOR_COMPONENT_ALLOCS.ORDER_NUMBER "

cQuery = "SELECT "
cQuery = cQuery + "SOR_TRANSACTIONS.ITEM_NUMBER, "
cQuery = cQuery + "SOR_TRANSACTIONS.ITEM_DUE_DATE, "
cQuery = cQuery + "SOR_SALES_ORDERS.ORDER_NUMBER,"
cQuery = cQuery + "SOR_SALES_ORDERS.THEIR_REFERENCE,"
cQuery = cQuery + "SOR_SALES_ORDERS.ACCOUNT_NAME,"
cQuery = cQuery + "SOR_SALES_ORDERS.ORDER_DATE,"
cQuery = cQuery + "SOR_TRANSACTIONS.THIS_RECORD,"
cQuery = cQuery + "SOR_COMPONENT_ALLOCS.COMPONENT_PART,"
cQuery = cQuery + "SOR_COMPONENT_ALLOCS.NUMBER_OFF,"
cQuery = cQuery + "SOR_COMPONENT_ALLOCS.QUANTITY_ORDERED,"
cQuery = cQuery + "SOR_COMPONENT_ALLOCS.QUANTITY_ALLOCATED,"
cQuery = cQuery + "SOR_COMPONENT_ALLOCS.QUANTITY_ISSUED,"
cQuery = cQuery + "SOR_COMPONENT_ALLOCS.PARENT_PART,"
cQuery = cQuery + "SOR_SALES_ORDERS.ORDER_DUE_DATE,"
cQuery = cQuery + "SOR_SALES_ORDERS.ORDER_STATUS"
cQuery = cQuery + " FROM "
cQuery = cQuery + "(L_SOR_SALES_ORDERS AS SOR_SALES_ORDERS INNER JOIN "
cQuery = cQuery + "S_SOR_TRANSACTIONS AS SOR_TRANSACTIONS ON "
cQuery = cQuery + "SOR_SALES_ORDERS.THIS_RECORD=SOR_TRANSACTIONS.PARENT_RECORD) "
cQuery = cQuery + "INNER JOIN S_SOR_COMPONENT_ALLOCS AS SOR_COMPONENT_ALLOCS ON "
cQuery = cQuery + "SOR_SALES_ORDERS.ORDER_NUMBER = SOR_COMPONENT_ALLOCS.ORDER_NUMBER "
cQuery = cQuery + "WHERE ("
cQuery = cQuery + "(SOR_SALES_ORDERS.THIS_RECORD = SOR_TRANSACTIONS.PARENT_RECORD) And "
cQuery = cQuery + "("
cQuery = cQuery + "("
cQuery = cQuery + "(SOR_SALES_ORDERS.ORDER_NUMBER=SOR_COMPONENT_ALLOCS.ORDER_NUMBER) AND "
cQuery = cQuery + "(SOR_TRANSACTIONS.ITEM_NUMBER=SOR_COMPONENT_ALLOCS.PARENT_PART)"
cQuery = cQuery + ")"
cQuery = cQuery + " AND "
cQuery = cQuery + "(SOR_TRANSACTIONS.SEQUENCE_NO=SOR_COMPONENT_ALLOCS.SEQUENCE_NO)"
cQuery = cQuery + ")"
cQuery = cQuery + " AND NOT "
cQuery = cQuery + "(SOR_SALES_ORDERS.ORDER_STATUS=0 OR SOR_SALES_ORDERS.ORDER_STATUS=9 OR SOR_SALES_ORDERS.ORDER_STATUS=10) and "
cQuery = cQuery + "SOR_TRANSACTIONS.ITEM_NUMBER=SOR_COMPONENT_ALLOCS.PARENT_PART And "
cQuery = cQuery + "((SOR_SALES_ORDERS.ORDER_DUE_DATE) <= #" & (AmericanFormat(Date + 30)) & "#)"
cQuery = cQuery + ")"
cQuery = cQuery + " ORDER BY SOR_TRANSACTIONS.ITEM_NUMBER, SOR_TRANSACTIONS.THIS_RECORD, SOR_TRANSACTIONS.ITEM_DUE_DATE;"

 

[bboffin]

Have you tried setting this up as a pair of parameter queries within Access?

Using query builder to construct the queries will eliminate all of the extremely inefficient and error prone SQL string building that your code is doing and will allow you to validate the query before it is called.

As far as I can see there is only one parameter:
AmericanFormat(Date + 30)
which will be a datetime parameter.

Read up in the Help how Parameter queries are set up.

In order to run a parameter query you need to set up the parameters using the Refresh method of the Parameters object within the query and then assign the required value to the appropriate named parameter.

The query is executed by calling the OpenRecordset method.

When you have both queries working your released code will simply call the one that uses the linked tables.

 

[SunnyByfleet]

Sorry but I think you are barking up the wrong tree.

There are no parameters. Americanformat is used to convert the date into American format. It is called when the query string is built, it doesn't exist when it gets passed to ODBC.

What the query will contain is "SOR_SALES_ORDERS.ORDER_DUE_DATE <= #09/06/2004#"

In fact, if I reduce the query to skeleton form, the problem is still there, as long as I refer to all three tables. If I look at the local copy of the SALES_ORDER table, then the problem goes away. However, its obviously looking at the wrong data!

One solution, albeit a sledgehammer one, is to export all three tables first, then run the query on the local copies. There must be an easier way though?

In the mean time, I will have another go with the Query builder, I did try that at the start, but it kept on objecting to relationships....

 

[SonOfEmidec1100]

Error 3146 tells you there is an ODBC error not which one. You need to examine the errors collection to get specific details.

Here is some sample VB6 code this, or something similar, should work for you.

   Dim ODBCError As Error
    Dim ErrString As String
    
    For Each ODBCError In Errors
            ErrString = ErrString & ODBCError.Number & " - " & ODBCError.Description & vbCrLf
    Next
    MsgBox ErrString

This should give you a clue as to the cause of the problem.

With a quick glance at the query I noticed something odd, but I dont think it caused the error. You duplicate your JOIN conditions in the WHERE clause, and also have two WHERE conditions that should be in the join logic. If you use the query builder you would get something like this

.
.
.
FROM (L_SOR_SALES_ORDERS AS SOR_SALES_ORDERS 
INNER JOIN S_SOR_TRANSACTIONS AS SOR_TRANSACTIONS ON SOR_SALES_ORDERS.THIS_RECORD = SOR_TRANSACTIONS.PARENT_RECORD)
INNER JOIN S_SOR_COMPONENT_ALLOCS AS SOR_COMPONENT_ALLOCS ON (SOR_TRANSACTIONS.SEQUENCE_NO = SOR_COMPONENT_ALLOCS.SEQUENCE_NO) AND (SOR_SALES_ORDERS.ORDER_NUMBER = SOR_COMPONENT_ALLOCS.ORDER_NUMBER) AND (SOR_TRANSACTIONS.ITEM_NUMBER = SOR_COMPONENT_ALLOCS.PARENT_PART)
WHERE ( (NOT([SOR_SALES_ORDERS].[ORDER_STATUS]=0 Or [SOR_SALES_ORDERS].[ORDER_STATUS]=9 Or [SOR_SALES_ORDERS].[ORDER_STATUS]=10)=False) AND ((...........

 

[SunnyByfleet]

Sounds interesting. I'll implement an error routine as you suggest and report back.

Regarding the inner join and the where, you are absolutely right. The SQL was lifted from Crystal Reports which does not use inner joins. I then played around with query builder and query builder came up with the inner joins.

I have tried removing the joins but the error still appears.

No matter, I will do the error routine as you suggest and report back. Thanks for the input.

 

[SunnyByfleet]

You utter star. It says "Invalid filter in WHERE clause", so at least I now know where to look.

Thanks a heap!

 

[SonOfEmidec1100]

Your welcome, however I just noticed an error in my sample query >>> WHERE (NOT(......)=False)

This gives the oposite of what you have in the original query, obviously you must use either the [blue]NOT[/blue] or the [blue]= False[/blue] not both.

I believe [blue]= FALSE[/blue] is the one to use this is how the NOT(...) gets interpreted when the query is run.

 

[SunnyByfleet]

Hmm, you are right about the NOT clause causing problems. If I remove that the query works! Unfortunately, that rather screws the data...

So instead I tried using IS FALSE.

....
cQuery = cQuery + " AND "
cQuery = cQuery + "((SOR_SALES_ORDERS.ORDER_STATUS=0 OR SOR_SALES_ORDERS.ORDER_STATUS = 9 OR SOR_SALES_ORDERS.ORDER_STATUS = 10)=FALSE)"
cQuery = cQuery + " AND "
....

Unfortunately that generates a different error:
0 - Syntax Error. Invalid Condition.

I also tried the following:

cQuery = cQuery + " AND "
cQuery = cQuery + "((SOR_SALES_ORDERS.ORDER_STATUS <> 0 AND SOR_SALES_ORDERS.ORDER_STATUS <> 9) AND SOR_SALES_ORDERS.ORDER_STATUS <> 10)"
cQuery = cQuery + " AND "

This brings back the old Syntax Error, invalid filter in WHERE clause.

I'm so close! Do you have any other suggestions?

 

[SunnyByfleet]

Well my only conclusion is that I have a query that insists on being positive. The following works:

cQuery = cQuery + " AND "
cQuery = cQuery + "("
cQuery = cQuery + "SOR_SALES_ORDERS.ORDER_STATUS = 1 or "
cQuery = cQuery + "SOR_SALES_ORDERS.ORDER_STATUS = 2 or "
cQuery = cQuery + "SOR_SALES_ORDERS.ORDER_STATUS = 3 or "
cQuery = cQuery + "SOR_SALES_ORDERS.ORDER_STATUS = 4 or "
cQuery = cQuery + "SOR_SALES_ORDERS.ORDER_STATUS = 6 or "
cQuery = cQuery + "SOR_SALES_ORDERS.ORDER_STATUS = 8 or "
cQuery = cQuery + "SOR_SALES_ORDERS.ORDER_STATUS = 12 "
cQuery = cQuery + ") AND "

These are all other options other than 0, 9 or 10.

This is a far from ideal solution, but it works.

Any ideas why it doesn't like negativity?

 

[SonOfEmidec1100]

Some thoughts

Some providers may not recognize FALSE in this context try [blue]= 0[/blue]

You could also try this

SOR_SALES_ORDERS.ORDER_STATUS NOT IN (0,9,10)

Good luck