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

Tom Adams tomadatn at bellsouth.net
Fri Mar 28 07:57:25 CST 2003


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





More information about the AccessD mailing list