ODBC issue with outerjoin

[MNJoe]

What I have is SQLBase 9.0.1 and Access 2003 windows XP Pro. A simple outerjoin of 2 tables. The Work_Order table and the Parts table. Some times the work order does not have a part number. If I split the query up and do just the work orders with parts it works just fine and if I do the work orders without parts it works just fine. If I use the old Merant 3.6 ODBC connection the outerjoin works fine. If I use unify's ReportBuilder 2005 it works just fine. If i try to connect with access 2003 with an outerjoin I get the runtime error 3146 ODBC call failed. I have tried these versions of SQLBaseoledb.dll and get the same error. 901-13951, 901-13768, 900-13663, 811-13312. The only files that I have for SQLwntm.dll and SQLWS32.dll are version 902-13767.

[Clifford Bass]

I think I may have reported that issue somewhere in the past. If you can get the 11.0 driver, that should work for the most part. I am not sure it was never completely solved. But perhaps it will be in 11.6. I think part of the problem was the driver returning zero-length strings instead of null values. Memory is somewhat vague on this. I doubt they will do any further patching of the version 9 driver; but I could be wrong.

Alternately, one thing I have done to get around some of the outer join issues is to create an additional ODBC DSN and connect to the second table through that. That forces Access to do the query in a different way instead of sending it directly to the server. Downside is that you have to log into the database twice.

Another alternative to that is to convert the SQL into SQLBase native SQL and put it into a pass-through query. Do not include the semi-colon at the end or it will complain. To configure it so that you do not have to choose the ODBC DSN each time you run it do this: In your tables list open up one of the linked tables in design view. Press <Alt>-<Enter> to display the properties window if it is not already showing. Copy the text of the description (it starts with "ODBC"). Close the table and click on your pass-through query. Paste the information into the ODBC Connect Str item, replacing anything already there. Then remove the part that refers to the specific table. Save and test.

How about you post a test case that demonstrates that and Unify (Mike) can test it with the upcoming 11.6 driver?