Loads of errors when querying MS SQL Server Express 2005 from Access 2007

Hi,

I wonder if you could help - I have an SQL server database with four
tables...

I have a 'tblContact':
ContactID,FirstName,LastName
101,Peter,Smith
102,John,Crow
103,Ed,Bye
104,Stuart,Jones

I have a ‘tblClient’:
ClientID,CompanyName
201,XYZCom
202,ABCCom

I have a ‘bltClientContactLinkTable’:
ContactID,ClientID, PositionID
101,201,301
102,201, 301
103,202, 302
104,202,302

And fiinally a tblPosition:
PositionID,Description
301,Manager
302,Director

The query below (when run through a Query Analyser connected diretly
to the SQL server) displays the first and last name of all contacts
along with the company they work for and the position the hold. If
they don't have a position it returns 'no position'.

SELECT
tblClient.CompanyName,
tblContact.FirstName,
tblContact.LastName,
coalesce(tblPosition.Description, 'no position') as Description
FROM
tblContactClientLinkTable
INNER JOIN tblContact ON (tblContact.ContactID =
tblContactClientLinkTable.ContactID)
INNER JOIN tblClient ON (tblClient.ClientID =
tblContactClientLinkTable.ClientID)
LEFT JOIN tblPosition ON (tblContactClientLinkTable.PositionID =
tblPosition.PositionID)
GROUP BY
tblClient.CompanyName,
tblContact.FirstName,
tblContact.LastName,
tblPosition.Description
ORDER BY
tblClient.CompanyName

However, If I connect to the server from Access so I can actually edit
the data easily I get problems

Once I'd added all the tables I need (through ODBC linked table),
prefixed each table name with dbo_ (seeings as that's what they're
called when I connect to them and when I look at automatically
generated SQL when I use the
designer instead) and created the SQL query, an error comes up:

Syntax error (missing operator) in query expression
'(dbo_tblContact.ContactID = dbo_tblContactClientLinkTable.ContactID)
INNER JOIN dbo_tblClient ON (dbo_tblClient.ClientID =
dbo_tblContactClientLinkTable.ClientID)
INNER JOIN dbo_tblPosition ON
(dbo_tblContactClientLinkTable.PositionID = dbo_tblPosit'.

Any suggestions?

Many thanks,

.

Relevant Pages

  • Re: The Many Flavors of SQL - Can a SQL Server query work in MS Ac
    ... John and Baz, ... Now that I understand the underlying differences in query syntax, ... inner join cmsopen.dbo.apm_vendor v ... in the Query Analyzer on a Windows 2000 Server box running SQL Server ...
    (microsoft.public.access.queries)
  • SQL Server updates slower than MS Access?
    ... We're having trouble with a new SQL Server installation upgraded from MS ... A very large update query affecting 750,000 records with an inner join takes ... few seconds, but as soon as it hits about 400k records, SQL Server hits the ...
    (microsoft.public.sqlserver.programming)
  • SQL Server slower than MS Access
    ... We're having trouble with a new SQL Server installation upgraded from MS ... A very large update query affecting 750,000 records with an inner join takes ... few seconds, but as soon as it hits about 400k records, SQL Server hits the ...
    (microsoft.public.sqlserver)
  • Re: Batch Queries in a Pass-Through
    ... Is this really a pass-through query or are you just using a link to the ... the SQL server side, but your posted syntax doesn't appear to do so. ... Joins if you are trying to detect new and deleted records. ... Select a.id from table a inner join table b on a.id = b.id where a.timestamp ...
    (microsoft.public.access.queries)
  • Re: The Many Flavors of SQL - Can a SQL Server query work in MS Access?
    ... I prefer to specify by name the sort columns, ... query as the source. ... in the Query Analyzer on a Windows 2000 Server box running SQL Server ... but it always erros out with: "Syntax Error ...
    (microsoft.public.access.queries)