Convert Access to SQL 2000 Desktop

I have used the Upsizing Wizard in Access 2003 to convert a database to SQL. When I open it using SQL 2000 Desktop edition, I am finding a problem with the views and stored procedures.
When I open a query in design view, the tables are displayed in the upper pane without the details of the individual fields. All I get is a tick box next to *(All Columns)
If I create a new table, the same thing happens. If I create a new database, then make a table within it, any queries behave normally, with all the fields displayed.
I would be grateful for any ideasIf you are using the Access query designer, then checking the boxes next to the field names in the diagram pane will make the fields appear in the query grid.|||My problem is that there are no field names in the diagram pane - just a single line at the top of the table box which says *(All Columns). There is a single check box to the left of it, so I can have all columns or no columns. I can't pick the ones I want.
This "All Columns" would normally be at the top of the list of field names.
(How do I insert an image into this reply?)|||Post the SQL from the SQL pane for the view in question.

Convert Access Query w/IIF to SQL Server View

I am brand new to SQL Server. I had to move my data tables from Access 2003
onto SQL Server 2000. I have just upsized my .mdb using the upsizing wizard
and, after some minor changes, have gotten everything to work.
Now, I'm trying to focus on speeding things up. I have several nested
queries, using many tables, with several IIF statements that are used just
for selecting data to be displayed on forms and reports. I attempted to
convert one into a view, but have discovered that views don't allow IIF
statements. Is there a better way to do this? Can you create a procedure tha
t
is linked to Access? How do you reference this in Access?(c) Access' IIF translates to the CASE expression in SQL Server. See
http://www.aspfaq.com/2214 for this and other resources that should prove
handy.
(b) create your view using Query Analyzer; if you use Enterprise Mangler's
view editor, you won't be able to use CASE (among other problems).
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:DB695ADD-7A5D-44A4-8417-D7E6A3C40277@.microsoft.com...
>I am brand new to SQL Server. I had to move my data tables from Access 2003
> onto SQL Server 2000. I have just upsized my .mdb using the upsizing
> wizard
> and, after some minor changes, have gotten everything to work.
> Now, I'm trying to focus on speeding things up. I have several nested
> queries, using many tables, with several IIF statements that are used just
> for selecting data to be displayed on forms and reports. I attempted to
> convert one into a view, but have discovered that views don't allow IIF
> statements. Is there a better way to do this? Can you create a procedure
> that
> is linked to Access? How do you reference this in Access?|||(c) Access' IIF translates to the CASE expression in SQL Server. See
http://www.aspfaq.com/2214 for this and other resources that should prove
handy.
(b) create your view using Query Analyzer; if you use Enterprise Mangler's
view editor, you won't be able to use CASE (among other problems).
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:DB695ADD-7A5D-44A4-8417-D7E6A3C40277@.microsoft.com...
>I am brand new to SQL Server. I had to move my data tables from Access 2003
> onto SQL Server 2000. I have just upsized my .mdb using the upsizing
> wizard
> and, after some minor changes, have gotten everything to work.
> Now, I'm trying to focus on speeding things up. I have several nested
> queries, using many tables, with several IIF statements that are used just
> for selecting data to be displayed on forms and reports. I attempted to
> convert one into a view, but have discovered that views don't allow IIF
> statements. Is there a better way to do this? Can you create a procedure
> that
> is linked to Access? How do you reference this in Access?|||Thanks for the speedy response. Another question: If I create my view using
query analyzer, how do I save it as a query to link to Access?|||If you have a view in SQL Server, like:
CREATE VIEW dbo.MyView
AS
SELECT 1;
Then from Access you can just treat it like a table, e.g. SELECT * FROM
dbo.MyView instead of SELECT * FROM dbo.MyTable.
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:D6BF7042-48B7-4FC0-9EE2-D66026A461D7@.microsoft.com...
> Thanks for the speedy response. Another question: If I create my view
> using
> query analyzer, how do I save it as a query to link to Access?|||Sorry, I'm not following you.
Ok, using SQL Query Analyzer, I created my Select statement using the case
statements instead of IIF's, and parsing completed successfully. It runs and
selects the right information. Now what?
Do I have to save this somewhere special? Then what.|||You do not save it somewhere else. View is a server object, meaning it has
to be created on the SQL Server database (and saved there after creating
it).
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:C8C7BE9D-70BD-4BB3-BCA5-7D2ABFD4C3D2@.microsoft.com...
> Sorry, I'm not following you.
> Ok, using SQL Query Analyzer, I created my Select statement using the case
> statements instead of IIF's, and parsing completed successfully. It runs
> and
> selects the right information. Now what?
> Do I have to save this somewhere special? Then what.|||Hello
You can use a CASE expression instead of the IIF, using one of these
syntaxes:
CASE
WHEN condition
THEN result_if_true
ELSE result_if_false
END
or:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
..
ELSE other_result
END
For more informations about CASE, see Books Online.
Razvan|||Use Query Analyzer(QA) to transfer ALL the old data from Access to SQL.
Copy it to tables - or better yet use DTS package to copy entire database.
If you are using a view to call the data for reports, use QA to test and
create a stored procedure with the test script. Call the stored procedure
from your reports.
If you want to use the QA script on occasion and not for any so-called
recurring reports, then save the script written in QA to any location (just
liek a text script) and open up the script when you want to run it and run i
t
at will.
"Holly" wrote:

> Thanks for the speedy response. Another question: If I create my view usin
g
> query analyzer, how do I save it as a query to link to Access?|||On Thu, 1 Jun 2006 11:26:01 -0700, Holly wrote:

>Sorry, I'm not following you.
>Ok, using SQL Query Analyzer, I created my Select statement using the case
>statements instead of IIF's, and parsing completed successfully. It runs an
d
>selects the right information. Now what?
>Do I have to save this somewhere special? Then what.
Hi Holly,
If you have a SELECT statement that returns the data you need, e.g.
SELECT au_fname FROM authors
than you can create a view by typing a CREATE VIEW statement before it
and executing the complete code:
CREATE VIEW Author_FirstNames
AS
SELECT au_fname FROM authors
Once this has executed successfullym you can use the Authro_FirstName
view just as you would use any regular table - and that includes
creating a linked table for it in Access.
Hugo Kornelis, SQL Server MVP