Attempt to query linked tables failing

[gnrmjd]

Hi, I've been looking around trying to find a specific answer to this question but nothing shows up in searching.



I recently have experimented with linking our company's SQL Server database to Access, to see if it can work as a useful front end. I used the wizard for making linked tables. But when I attempted to test queries I was unable. I tried to do a query to get fields from multiple different tables but I was told I could not because there was no relationship between the tables.

Does this mean that I have to re-create every single relationship between the tables? There are many many tables (over 100) and so to do this would be a very arduous and annoying task. Is there really no other way to get the relationships/joins to transfer?

[JoeM]

I have never had a problem doing this between SQL and Access, as long as I have a JOIN in my query. How have you written your query? Does it contain JOINs between the tables used in the query?

Note that you can also do Pass-Through Queries. This actually passes the query on to the SQL server from Access, and just turns the results. This is more efficient, as you are only pulling into Access the results. This is very helpful when doing with large amounts of data. Here is a link on that: http://support.microsoft.com/kb/303968

[gnrmjd]

Well, i've been using the query wizard, so I didn't actually write the query in SQL or anything. the wizard had me select fields, which I did. It's supposed to then group them based on relationships but it didn't. And when I go to the Relationships screen and right click and hit "Show all" or click "All Relationships" nothing shows up. It seems that none of the relationships are showing up in Access.

It wouldn't be such a big deal to me, as I know SQL, but I'm hoping to make a front end for users who don't, and therefore they wouldn't really know about "joins". I need it to work like it's supposed to, where it joins them automatically in the wizard based on their relationships

[JoeM]

So, are you expecting the users to create their own queries, or use queries that you have already set up for them?
From my experience, if the users do not have a good understanding of Access and/or relational databases, having them try to write their own queries may not be a reasonable expectation.

[gnrmjd]

I figured they could use the query wizard, as all you need to do is click the fields you want and then it does the rest pretty easily correct?

I was expecting that with the query wizard they could make their own though I also wanted to set up forms.

Either way I have to find out why the relationships aren't showing up

[JoeM]

It appears that the relationships do not transfer over to Access, that you might need to set them up manually.
See: http://office.microsoft.com/en-us/ac...010341762.aspx

[gnrmjd]

I see that it says that on "Import" but I didn't use Import, I used "Link" and I was under the impression it was supposed to transfer in that case.

[JoeM]

I was under the impression it was supposed to transfer in that case.

Where did you see that? I was looking on the internet for any information on it, and could not find any reference to that.
In many instances, the behaviors of Linked vs. Imported Tables are the same.
From what you are experiencing, it certainly appears that those relationships are NOT transferring over.

[gnrmjd]

Because when trying to find MY error I've read a lot of people who say that they can't see the relationships on the relationship screen but that it does show up on "Show all". This isn't the case with me however.

Anyways, interestingly enough, I've discovered that making a query from scratch, rather than using the wizard...when I drag tables into the query the relationships do indeed show up. I really don't understand why it works there but doesn't in the wizard or the relationships screen. I can do the joins just fine when making a query from scratch.

I guess now it's workable but I still would like to find out how to make it work in the wizard. it seems strange for there to be no way to do that.

[gnrmjd]

And now I find for some reason that while most of the relationships show up in the query designer, there are some that are just missing for (apparently) no reason....like I have 6 tables in this query and while 5 of them are joining properly, one foreign key connection is just missing even though it definitely exists.

[JoeM]

That is interesting... Maybe the Wizard does not recognize them, for some reason.

One option you may want to consider is if you have a general idea of what people may need to query, create a handful of pre-defined queries between certain tables, and return all the fields. Then, when using the Query Wizard, your users could start from your pre-defined query instead of the underlying tables, and then they would not need to worry about the relationships.

Don't know if that works for you, but it is another option, anyway.

[gnrmjd]

^Hm, I like that idea.

That's definitely a possibility.

I have two questions about things I can do when making a front end, and I was wondering if you could help.

1. What's the best way to go about formatting? I tested by making a query I had just done in SQL. Of course on access it was much faster which was great. But the problem was I had some formatting in SQL and I didn't know where to do it. for example if one filed was 0 i gave it a certain string, if it as 1 I gave it a certain string. Is there any way to implement this into the process of using the query designer? This is really for my own use since I might use it instead of SQL queries sometimes but maybe it's not worth it if I lose control. (Also how do I know the joining order? That's kind of important to know what results I'll get. Does it start fromthe left field and join as needed as it moves right?

2. I know pass-through queries are powerful and fast. But if I'm having users do this and I give them navigation and forms how can I work pass-through queries in in a way to increase performance? Would it be like you said where I let them choose from queries that I already pre-defined and they can tweak?

[JoeM]

for example if one filed was 0 i gave it a certain string, if it as 1 I gave it a certain string.

I am not sure I understand. That sounds more like "calculated fields/expressions" and not formatting.

By the way, if you know SQL, and it is just the Access terminology tripping you a bit, the nice thing about pass-through queries is that you use SQL syntax for that.

I know pass-through queries are powerful and fast. But if I'm having users do this and I give them navigation and forms how can I work pass-through queries in in a way to increase performance? Would it be like you said where I let them choose from queries that I already pre-defined and they can tweak?

I guess it really depends on your needs.

If you create only pass-through queries to your data (and no linked tables), then if they use the Query Builder, they will only see those pass-through query objects and not any table objects (so that really allows you to control things).

If you prefer to use Forms instead of the Query Wizard, you can do that too. I often create Forms where users make Criteria selections on the Form, and then I use VBA to build the SQL code for the query "on-the-fly" based on their selections.

[gnrmjd]

^Ok. Hm. For the first part I just meant that in SQL I often turn Boolean columns into values. like a 0 or 1, might turn into 'Yes' or 'No' which I do in a case statement in the select statement. I was just wondering how you accomplish this in Access. Unless the only way is with using pass-through queries. (I've never used them before so maybe I should look up some tutorials.)

And as for the second thing that's a good idea. The only question I have with THAT, is will you be losing efficiency? I'm not sure if Access has any kind of query optimization in terms of how they join things, or how they filter....but doing it programatically would most likely lose any efficiency they have right? Although I'm not sure if Access actually does do any of this themselves.

[JoeM]

^Ok. Hm. For the first part I just meant that in SQL I often turn Boolean columns into values. like a 0 or 1, might turn into 'Yes' or 'No' which I do in a case statement in the select statement. I was just wondering how you accomplish this in Access. Unless the only way is with using pass-through queries. (I've never used them before so maybe I should look up some tutorials.)

If you use a pass-through query, you should be able to keep your syntax the same (using Case statements).
If you want to do it in an Access query, you can use the Immediate If (IIF) function. See: http://www.techonthenet.com/access/f...vanced/iif.php

And as for the second thing that's a good idea. The only question I have with THAT, is will you be losing efficiency? I'm not sure if Access has any kind of query optimization in terms of how they join things, or how they filter....but doing it programatically would most likely lose any efficiency they have right? Although I'm not sure if Access actually does do any of this themselves.

I don't think you should lose any efficiency. Remember, it is either using pre-defined queries, or setting up the queries the way that you instruct it.

And in general when using SQL data in Access, using pass-through queries is much more efficient than doing the queries in Access, as pass-though queries pass the query code back to the SQL server, perform there, and only return the results to Access. Alternatively, doing the query in Access means sending all of the data from all tables used in the query to Access first, and then performing the query in Access. SQL is much more powerful than Access, so the more work you can do directly on the SQL server, the better.