Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MSAccess 2010 join query help please...

Status
Not open for further replies.

PLCBeetle

Programmer
Sep 30, 2008
19
US
I have this query which I have written, tested and works great in MSSQL:

SELECT partno12nc, apodo, partno, suffix, suffixtext
FROM partspec12nc.dbo.parameters A
INNER JOIN partspec12nc.dbo.suffix AS B ON A.suffix = B.id
WHERE partno12nc <> 0 and suffix <> 0
ORDER BY partno12nc

I am trying to copy/paste it into MSAccess 2010 using an ODBC connection:

SELECT partno12nc, apodo, partno, suffix, suffixtext
FROM [ODBC;DRIVER=SQL Server;SERVER=ms001;DATABASE=partspec12nc;Trusted_Connection=Yes].parameters AS A INNER JOIN suffix AS B ON A.suffix = B.id
WHERE partno12nc <> 0 and suffix <> 0
ORDER BY partno12nc;

I have more and other queries that work fine in MSAccess 2010 using this exact ODBC connection string so I am sure that this is not the issue.

I get the following error message:

The Microsoft Access database engine cannot find the input table or query 'suffix'.

Ideas or suggestions please? Thank you
 
GOT IT, YIPEE :)

I needed to add the ODBC connection string again to the second table, like so:

SELECT partno12nc, apodo, partno, suffix, suffixtext
FROM [ODBC;DRIVER=SQL Server;SERVER=ms001;DATABASE=partspec12nc;Trusted_Connection=Yes].[parameters] AS A
INNER JOIN [ODBC;DRIVER=SQL Server;SERVER=ms001;DATABASE=partspec12nc;Trusted_Connection=Yes].[suffix] AS B ON A.suffix = B.id
WHERE partno12nc <> 0 and suffix <> 0
ORDER BY partno12nc;

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top