[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2138
  • Last Modified:

Collation Conflict with linked SQL tables

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?

1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now