Help with Inner Join in Access

[xc405]

I am very new to Access and SQL so apologies.
The database is in SQL Server Management and I am able to use this query just fine to join all the tables:

SELECT *

FROM FracFocusRegistry.dbo.RegistryUpload

INNER JOIN FracFocusRegistry.dbo.RegistryUploadPurpose on RegistryUpload.pKey = RegistryUploadPurpose.pKeyRegistryUpload

INNER JOIN FracFocusRegistry.dbo.RegistryUploadIngredients on RegistryUploadPurpose.pKey = RegistryUploadIngredients.pKeyPurpose

I created an Access file which links to the SQL Server database using the ODBC option and I am able to view the 3 tables in separate tabs but the query no longer works. I get an error "Cannot find file ...FracFocusRegistry.dbo" when I run this query:

SELECT *

FROM (FracFocusRegistry.dbo.RegistryUpload

INNER JOIN FracFocusRegistry.dbo.RegistryUploadPurpose on RegistryUpload.pKey = RegistryUploadPurpose.pKeyRegistryUpload)

INNER JOIN FracFocusRegistry.dbo.RegistryUploadIngredients on RegistryUploadPurpose.pKey = RegistryUploadIngredients.pKeyPurpose

Hopefuly someone can help me with my problem, thanks in advance.

 

[Micron]

In case you don't get an answer soon, here's my guess (I confess to being mostly ignorant of MySql or SQL server).
I'm assuming you copied a sql server sql statement and dumped it into an Access query. If so, it will never work because of the multiple dots (.) in your table reference. The dot is the delimiter between table and field (tblName.FieldName) in Access. If you can open the table in datasheet view from Access, go to query design and attempt to recreate the query using the UI. You may have to give the table and/or field names aliases for Access to work with it.
Sorry if I've missed the mark.

 

[xc405]

Yes Sir lol copy and paste! I'm trying to recreate it using the query wizard but so far no success.

 

[Micron]

I'm not sure that will help you if you need alias names.

 

[james_lankford]

you probably need to make a passthrough query

even though this tutorial is for an old version of access the steps are pretty much the same
MS Access for the Business Environment: Extend Access with Pass-Through Queries — DatabaseJournal.com

and this video does an OK job of explining it with the newer version of access (but its very blurry)
Creating and Executing a SQL Pass-thru Query | Online Training Video