Access join not supported "sometimes". 1

[Cicero01]

I have an Access database in which I have a series of SQL server tables linked into Access with an ODBC connection. This is the SQL code in question:

SELECT [dbo_Account].[acct_id], [dbo_Agent].[AgentName]
FROM dbo_Account LEFT JOIN dbo_Agent ON [dbo_Account].[sales_rep]=left([dbo_Agent].[uid_Agent],8)
ORDER BY [dbo_Account].[acct_id];

This query sometimes runs as written under Office 2000 and it gives me correct results. If I open the query in Access SQL view mode, select all the text and cut it, then paste it back into the same SQL view window and try to go to Datasheet view (run the query), it dies with a message, “Join expression not supported”. Clearly the error is bogus because the query does run under the right conditions.

We have some clients running Office 2000 and some with Office 2003. In Office 2000, I can build the database from scratch, link the tables using ODBC, build the query in design view and then modify the SQL and the query will run. The same sequence in Office 2003 will not run. However, I have an Office 2000 database created some months ago where the query will work if opened under 2003. If I open the database under 2000, close it leaving Access up, create a new database and import only the tables from the old database, the query runs. Until I try pasting in the code. Then it fails. Arghhh!

 

[lameid]

Have you tried using an SQL Pass Through query? Sometimes Jet has a fit over trying to figure out a unique index on the SQL side. Also sometimes Jet gets corrupt text in objects. Try copying the sql to notepad, saving as an MS-DOS text file, closing it, reopening and then copying and pasting the text. Rekeying would be the next thing I'd try.

 

[cmmrfrds]

Check which library contains the "left" function, I think the vba library. Perhaps you need to set a reference to the library on the PC where the query does not work.

I have Access 2000 and the function is contained in this library.
c:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL

Maybe in Access 2003, Microsoft changed the library name or location. Look for a missing reference.

 

[PHV]

Definitively check the references:
when in VBE menu Tools -> References ...

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

 

[Cicero01]

First, thanks to lameid for the pass-through query suggestion. Changing the query to a PT allowed it to work with the failing join. I did attempt fixing it earlier with vba references but that didn't work, but thanks also for that suggestion.

Now I have another problem created by the PT query. The original issue affected 2 queries, one of which is a parameter query. The old format of the parameter field of market.desc=[] is rejected when it's in the PT query. I get "Invalid column name. (#207)". Any suggestions as to how this should be formatted if it's possible? I have temporarily built a 3rd query for the 2 values we use but I'd rather limit the # of queries if possible?

 

[lameid]

I am slowing moving into using ADO (ACC 2000+) but in DAO I have just set the SQL property of an existing querydef object using VBA before executing it. You should create a view for your Select statement without criteria.


Dim qry as dao.querydef
Set qry = CurrentDb.QueryDefs("<QueryName>")
qry.SQL = "Select * From <View Name> Where market.desc= '" & strValue & "'"
qry.close
Set Qry = nothing

HTH