[AccessD] Sql 7 - Corrupt Views ... continued

Tom,

First of all, I still have no idea what is causing your problem.  But,
here's a few comments.

I have moved to creating all views in Query Analyzer.  I've found that
EM doesn't let me do as fancy of syntax as I want.  It often balks at
even simple case statements.  The same thing in QA creats and executes
just fine.

Also, you can sort in a view.  You just need to add "Select Top 100
Percent" before your field list.

--Susan


-----Original Message-----
From: Tom Adams [mailto:tomadatn at bellsouth.net] 
Sent: Friday, March 28, 2003 7:57 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Sql 7 - Corrupt Views ... continued


I've posted a couple of items asking if anyone has had any problems with
Sql 7 and corrupt views.

So far no one else seems to have found this bug/feature.  Plowing ahead
with this first in the universe coverage (you heard it here first
folks!), I thought I'd bring you up to date.

At first I thought it might be caused by Access 2000 ADP.  I used both
that and the Enterprise manager to make changes in views.  I then
decided to eliminate the Access question by never changing a view with
Access, just using it to review data (Being able to sort is absolutely
essential in my opinion.  Although the view can not be sorted, you can
move columns around and then wipe as many as you wish and hit the A-Z
button.  Wonderful really.).

I can now report that I am still hitting corrupt views.  However they're
a little different now.  Now I open them (it's mainly one complicated
view now) in Enterprise Manager and the Sql view looks corrupt.  It cuts
off before the From and following parts. However it runs ok (Previously
the view looked ok but showed bad data.  Any change at that point would
correct it - eg. take out a field and put it back in.)

Note that I can still generate Sql scripts from the database window and
the scripts are just fine.

Now when I delete the Sql statements that show in the EM, and paste the
old complete code back in, it looks ok in design, and will run ok with
the !, but when I save it and reopen in Design it looks corrupt again.

I will test deleting the View, closing the EM, the reopening and
recreating with Sql Server Query Analyzer.  One poster recommended that.

Sql Server is running on an NT 4 server with the latest NT service pack
installed.  It jammed up about a week ago and the Administrator brought
the server all the way down and back up then did some Admin clean up
stuff.  I'm working on a database maintained by Epicor (formerly
platinum) with about 1,000 tables.

Note:  I usually combine all the fields and apply universal criteria in
a "Base" query, then use it in other specilized queries. When the Base
query corrupted (Sql looked good, data looked like poo-poo, I found I
had to revise the other queries using it to fix the problem all down the
line.  Now that the corruption has changed (data looks good, Sql looks
like poo-poo) I find I don't have to fix the downline views.

Note2:  A few tables have too many indexes to link to from an Access
Mdb.  I found that I can make a view, showing all fields with the table
name & "_vw" and get by the index problem.

Note3:  I've really gotten to like the ADP.  It's much more flexible,
faster and easier to use for a variety of development tasks. However I
also use an Mdb from time to time.  Eg.  I created a view and wanted to
show the records where field 1 didn't equal field 2.  I couldn't seem to
do it in a view directly so I just exported the view to excel, imported
the excel file into an Mdb, and added the criteria and had the answer in
about a minute.  I might learn how to do that later in an Adp but got
the job done quickly by using an Mdb.


If anyone has any comments or experience with this I'd appreciate
hearing from them.

Tom (Viva la Access!!! Long live Jet!!!!) Adams



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com