×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

MSAccess 2010 join query help please...

MSAccess 2010 join query help please...

MSAccess 2010 join query help please...

(OP)
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

RE: MSAccess 2010 join query help please...

(OP)
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;

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close