I've got a test Access DB in Access 2000 format which works fine as a data source for a linked server. On a new laptop I have Access XP. When I put the test Access database on the laptop I get a collation error from SQL Server (also on the laptop). I haven't converted the Access DB to XP format, so I assume the problem is around the Jet OLE provider presumably installed when I installed Access XP.
The error only occurs when I join data from the Access DB to data in other databases. When the join is between two tables in the Access DB there is no problem (I assume SQL Server passes the query down to Access and just proceses the result set).
If I code the collate clause in my join the problem goes away. I don't really want to have to do this. is this a known problem/feature? Is there any way around the problem?
Thanks
Martyn Hodgson
martyn.hodgson@cdesolutions.com
The error only occurs when I join data from the Access DB to data in other databases. When the join is between two tables in the Access DB there is no problem (I assume SQL Server passes the query down to Access and just proceses the result set).
If I code the collate clause in my join the problem goes away. I don't really want to have to do this. is this a known problem/feature? Is there any way around the problem?
Thanks
Martyn Hodgson
martyn.hodgson@cdesolutions.com