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


Since it works in Query Analyzer, why not just use a Pass-Thru query
to run it?

Or, if this is going to be a regular thing, use the query that works
in Query Analyzer to create a View on SQL Server, and link to that
view in Access. (Just tell Access it's a Table.) Although you'll
have to lose the Order By clause. They won't work in SQL Views. You
can get around that however, by basing a Access Query on the View and
doing your Order By in the Access Query.


On Fri, 30 May 2008 05:27:07 -0700 (PDT), uberyes@xxxxxxxxx wrote:

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,

--
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!
.

Relevant Pages

  • Re: "EXEC" in SQL Server 2000 Views
    ... Kalen Delaney ... > INNER JOIN b ... > Could not execute query against OLE DB provider 'MSDASQL'. ... >> SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • 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)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)