Collation Conflict with linked SQL tables

Posted on 2012-09-21
Last Modified: 2012-09-24
I have an access mdb that has links to two different SQL databases.
The two collation sequences are:
SSP: SQL_Latin1_General_CP1_CI_AS
Brooms: Latin1_General_CI_AS

My access query links tables and view from the two databases. However, if I try to filter on one of the SSP fields, I get a message:
Cannot resolve the conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

How would I solve this issue within Access?

Question by:jdhackett
    LVL 84

    Accepted Solution

    Access doesn't support the COLLATE keyword in SQL Queries, so I'd say the only way to do it would be to use temporary tables to save down the data from the servers to the local machine, and then query from there. For example:

    Currentdb.Execute "SELECT * INTO YourLocalTable FROM YourLinkedSQLTable"
    Currentdb.Execute "SELECT * INTO YourLocalTable2 FROM YourLinkedSQLTable2"

    Now build your query using YourLocalTable and YourLocalTable2.
    LVL 5

    Expert Comment

    What happens when you create two "Select * from tblXXXX" queries for the linked tables and use these instead of the linked tables in your filter query ?

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now