cannot join on Memo, OLE or hyperlink

[Andy Levy]

Hi

Hope you can help with this one.

Database frontend = MS Access (part of MSOFFICE 2K Professional - SP-3
=> connected via ODBC to =>
Database backend = MySql client API version 3.23.56 ( i cannot change
this )

I am trying to set up an inner join query in Access2k. The field type in
both tables is exactly the same - int(11).
However i keep getting the error

"Cannot Join on Memo, OLE or Hyperlink Object (tableA.fieldA=
tableB.fieldA)"

The SQL is :

SELECT tableA.fieldA, tableB.fieldA
FROM tableA RIGHT JOIN tableB ON tableA.fieldA=tableB.fieldA;


Thanks

 

[Gary Walter]

Hi

Hope you can help with this one.

Database frontend = MS Access (part of MSOFFICE 2K Professional - SP-3
=> connected via ODBC to =>
Database backend = MySql client API version 3.23.56 ( i cannot change
this )

I am trying to set up an inner join query in Access2k. The field type in
both tables is exactly the same - int(11).
However i keep getting the error

"Cannot Join on Memo, OLE or Hyperlink Object (tableA.fieldA=
tableB.fieldA)"

The SQL is :

SELECT tableA.fieldA, tableB.fieldA
FROM tableA RIGHT JOIN tableB ON tableA.fieldA=tableB.fieldA;

Hi Andy,

I apologise that I am responding without
a solution, but...

Did you update Jet to SP8?

What version of MDAC?

I only ask because an ODBC link to an SQL Server
view recently started returning a field
( char, len=13, allow nulls)
as a *Memo* in my make table query.

If you create make table queries for tableA and
tableB, are either fields "A" returned as Memo fields?

Again, apologies for no solution.

Gary Walter

 

[Van T. Dinh]

What's Int(11)?

In Access, you can have Field Type Numeric of Size Integer (2 bytes) but I
have never heard of Int(11).

 

[Andy Levy]

int(11) is the size specified for the field on the MySQL server

 

[Van T. Dinh]

Sorry, don't know about MySQL. Clearly, Access doesn't interpret it as one
of common Field Types that Access can use for joins.

Suggest you use View (or equivalent in MySQL) or at least Pass-Through Query
where the link and the Query execution are actually handled by MySQL.

 

[Andy Levy]

Thanks Gary

I believe i am using MDAC version 2.53.6200.0

And Jet Version - (im not sure where to find this)

Are you suggesting that these updaates might cause the problem or remedy the
problem.

Thanks

Andy

 

[Gary Walter]

Sorry Andy,

I'm fixated on the fact that I believe
something was changed recently in
ODBC.

Please ignore my post, and follow Van's excellent advice.

 

[Andy Levy]

Thanks Van
The pass-through idea worked

I never knew you could do that. Fantastic

Ta

Amdy