Access vs. SQL query Designer

Why is it that I can use existing queries in the Access query designer but not when I am in an .ADP and using MS SQL2000 as the back end?

Why is the functionality of the query designer in an .ADP so much less powerful then the one in an .MDB? It is driving me crazy!!

I realize that there is a difference between Jet and SQL 2000, and in the syntax of the specific SQL being used, but I would think the GUI, where the work is being done, would do the work behind the scene!

Did the Microsoft Access programmers just "give up" on us, or is it really impossible to have a GUI for MS SQL2000?

__________________
Mitch

Hi Mitch,

"Queries" don't exist in .adp files or on SQL Server. They are a native Access object. Did you upsize a .mdb file to an .adp file? If so, all of your queries were either a) not upsizeable, or b) upsized to stored procedures, views, or user-defined function. The .adp designer is actually a very good tool for working with any of the last three object types on SQL Server.

HTH,

Bob

.mdb and.adp
A .mdb file is a file that contains MS Access tables, reports, forms, etc. It has a Jet engine that runs the database. The data resides in MS Access. Every time you create a new .mdb file you get no tables.
A .adp file serves as a front end for SQL Server. It shows all tables, views, stored procedures that reside in SQL Server. It can also have forms, reports, etc.. When you create a new .adp file it will automatically sho all object on the database that you point it to. (unlike .mdb)

Query designer - There are two flavors. For a .mdb file it writes JetSQL code for you, for a .adp file it will write TSQL code for you. When you open a .adp file you will not see the queries that reside in your .mdb file.

There are differences in the two, but once you get used to the change, it is actually nicer to use the .adp.

And yes, the GUI does the work behind the Scene, but it will save it as a view in SQL Server. After you create the view, you can link it from access just like a SQL Server table.

What are you needing to do in the designer?

Sal

I understand (to a point) the differences between the MDB and the ADP.

I did use the term Query to broadly refere to views, SP and InLineFunctions, I should have clarified that.

The functionality of the MDB version of the query designer allows you to use existing queies whereas the ADP version does not, except for views. The problem with views, is that they are not as powerfull as SPs. In an MDB I can do almost anything in its queries, but very little can be done in views (othere then just filter out data) so I have to do what I need to do in a SP (like calculations, IIF--which have to be done using Case, etc), but if I then need to use that SP in a SP (query/sub querey) I can't do it in the query designer. So what I have to do now is write out everything in SQL by hand, and that is not very intuitive compaired to the GUI way of doing it in an MDB. Kind of like going from a calculator to pencil and paper.

Take for example an update querey, In an MDB I can bring in 2 tables, link them and update one table based on the data in the second table. In the ADP I can't.

I fully understand that I am new to the .ADP and SQL world but this sure seems like a giant step backwords.

Am I missing something on how to get the same functionality/ease of use or what? I have built a lot of "complicated" apps in an .MDB, but this is looking very daunting to me.

Again, I know that I am more venting right now then anything, but I would like to better understand how to use the tools Access gives me. Any suggestions on how to get a better understanding of how to go about using the various desgin tools in an ADP (and going from Access SQL to MS SQL) are welcome.

Thanks again, Michal

Hold that thought.
You can create a case statement in a view, you just can not view the designer the same way. You must remove everything except the code (yes, a pain).
As far as sub-queries, you can still use them, but the same applies at times. If you must, you can still filter your data in SQL Srver and do the rest on the front end. Sometime having access as a front end can give you the bets of both worlds. Once again, it takes some getting used to, but you should do allright.
check out this book

Microsoft® Access Projects with Microsoft SQL Server™
By Ralf Albrecht, Natascha Nicol
ISBN 0-7356-1002-9

It is a great tool to show you some diferences and some tricks that I am sure you will like. It also covers ADO programming, which you can use in your .mdb front end or .adp. allowing you to use stored procedures with parameters.
It helped me.


Sal

Thanks, I will try and find the book and give it a look at.

Try bookpool.com or half.com, Their prices are about half than amazon and they are reliable and clean for business.



Sal

Thanks for the web site, it is good to get recomendations from people who actually had positive results from the company!