SQL Server + Access = No Queries!?

[isoquin]

So I'm connecting to an external SQL server. The data I need to access is stored across three tables, lets call them:

tblTinkerers
tblWidgets
tblParts

tblTinkerers is related to tblWidgets by the latter's PK.
tblWidgets is related to tblParts by the latter's PK

So, to see what Tinkerers need which parts, you need to traverse the tables. Now, I've not really worked with this SQL server thing, and don't quite understand a lot of the restrictions. Normally, I would just make a query between the first two tables, and then another query with the combination of the first two with the third.

I can't seem to do that now.

So, I'd very much like either of the following:
1) an easier way to do this normally that I'm missing, or
2) a way I can create joins within this SQL realm.

Please and thank you all.

[ADezii]

So I'm connecting to an external SQL server. The data I need to access is stored across three tables, lets call them:

tblTinkerers
tblWidgets
tblParts

tblTinkerers is related to tblWidgets by the latter's PK.
tblWidgets is related to tblParts by the latter's PK

So, to see what Tinkerers need which parts, you need to traverse the tables. Now, I've not really worked with this SQL server thing, and don't quite understand a lot of the restrictions. Normally, I would just make a query between the first two tables, and then another query with the combination of the first two with the third.

I can't seem to do that now.

So, I'd very much like either of the following:
1) an easier way to do this normally that I'm missing, or
2) a way I can create joins within this SQL realm.

Please and thank you all.

The SQL would look something like this assuming the following Primary Keys, Relationships, and Field Names:

SELECT tblTinkerers.TinkererID, tblTinkerers.T_Name, tblParts.Part_Name
FROM (tblTinkerers INNER JOIN tblWidgets ON tblTinkerers.TinkererID = tblWidgets.TinkererID) INNER JOIN tblParts ON tblWidgets.WidgetID = tblParts.WidgetID
ORDER BY tblTinkerers.T_Name;
 

1. tblTinkerers.[TinkererID){PK}[1] ==> tblWidgets.[TinkererID][MANY]
2. tblWidgets.[WidgetID]{PK}[1] ==> tblParts.[WidgetID][MANY]

NOTE: The Tinkerer's ID, Name, and Part Names would be displayed for each Tinkerer by the Tinkerer's Name Ascending (WOW! - getting tongue tied)

[isoquin]

I should clarify:

I'm not having a problem with the SQL statement (although that did make things much more clear), but rather that I can't seem to create queries within an Access project that pulls data from an external SQL server (which I enter through IP address and login).

Ideas?

[ADezii]

I should clarify:

I'm not having a problem with the SQL statement (although that did make things much more clear), but rather that I can't seem to create queries within an Access project that pulls data from an external SQL server (which I enter through IP address and login).

Ideas?

can't seem to create queries within an Access project that pulls data from an external SQL server (which I enter through IP address and login).

I'm not really sure what you mean when you say you cannot create Queries in an Access Project, please explain in more detail.

1. Do you have a legitimate Connection to SQL Server and have you tested that Connection?
2. Does you Login have the necessary Permissions on the Tables comprising the Query?
3. Have you tried Creating/Executing the Queries utilizing an Administrative Login?
4. Can you create simple SELECT Queries on the SQL Server data?
5. Do you have required Permissions on the Directory where the SQL Server Database resides?

[isoquin]

I'm not really sure what you mean when you say you cannot create Queries in an Access Project, please explain in more detail.

1. Do you have a legitimate Connection to SQL Server and have you tested that Connection?
2. Does you Login have the necessary Permissions on the Tables comprising the Query?
3. Have you tried Creating/Executing the Queries utilizing an Administrative Login?
4. Can you create simple SELECT Queries on the SQL Server data?
5. Do you have required Permissions on the Directory where the SQL Server Database resides?

I have a legitimate connection, tested and true. I can pull data, export it to a blank database, and play with it as usual. However, in the SQL connection Access project, the Queries "tab" only lists the following:
Create Function in Designer
Create View in Designer
Create Stored Procedure in Designer

But, if I try to open any of these, it says my version doesn't support them with an external SQL database. As such, I"ve been having to export the data continuously, which is a pain.

idea?