Solved

VB6, SQL-Server 7 and the Data Environment

Posted on 2000-04-11
5
300 Views
Last Modified: 2013-12-25
I am writing a client/server application using VB6 and SQLServer7. I've made good use of the DataEnvironment object to manage and create connections to tables etc.  M question: What are the advantages and disadvantages of DataEnvironment SQL Commands in comparison to using View commands.

Is there a speed enhancment by having the view on the server? Are there any limiting factors in having the view on the server, i.e. how does security work? Does the view inherit its security from its source tables or do you need to assign permissions to the view seperately?

If I used views could I write code to create the view in the first place? The reason for this is so I can maintain the application with "patch updates"

Thank you in advance.

Stuart Jones
0
Comment
Question by:scj1
  • 3
  • 2
5 Comments
 
LVL 12

Expert Comment

by:mark2150
ID: 2703663
Myself, I prefer keeping all of the programming on the VB side. I shy away from Views from a maintainability/visibilty standpoint.

If yoiu code with views, then the proper operation of your program is dependent on the views. This is double edged. If the view is changed, your program could malfunction. Then again, you can change your programs function without having to recompile.

For me, I prefer to have all of the programming in one spot instead of having to co-ordinate the code in two places. If I use views I now have to make sure that the VB and the View constructions match properly. If I keep all of the SQL in the VB and treat the database as a dumb collection I don't ever have to worry about someone killing my program by tweaking the view definition. Less to go wrong, I can't be killed by outside forces.

The down side of this is that your VB has to do all the work. You lose whatever speed advantages that having the view on the server presents (not much in most cases).

If you're just keeping track of, say, your home CD collection or somesuch, then the point is really moot. But if you're coding for a commercial app that someone else might be expected to maintain in the future, then visibility of the code is a key issue. From a long term maint standpoint having all the code in one spot is a *LOT* easier to manage and control.

M
0
 
LVL 1

Author Comment

by:scj1
ID: 2703827
I agree with your points on the whole. Unfortunately/fortunately it is a pretty big database and I've normalised the data as much as is possible, which means some data requires joins of up to five or six tables to get meaningful information. This takes a long time to initialise numerous recordsets etc. if I am showing lots of associated information on the current page.

I was hoping to use views to gain a speed improvement but I am not sure whether this improvement will outway the disadvantages you've already pointed out. I guess the deciding factor comes with if there is any functional difference between a view and a SQL-Command. A SELECT statement in SQL is only ever executed on the server rather than client side, isn't it? If not, the network traffic would have an effect or the server load....swings and roundabouts.
0
 
LVL 12

Accepted Solution

by:
mark2150 earned 200 total points
ID: 2704212
Yeah, the View is essentially nothing more than an SQL that is saved on the server and called by name. Other than the transmission time of the string itself, little is saved. The server still has to chew thru the SQL and return the same amount of data. I don't really see much performance advantage in a C/S model. If you were running SQL only without the Client, then it would make sense, but when you've got VB I'd just let the VB do the work.

One other point about Views, if the VB does the work and a view is accidentially screwed up the existing VB stuff will all continue to fly, only the stuff associated with the view will die. If the VB relies on a view and the view is accidentially deleted or corrupted then *everything* dies and existing functionality may be lost. This, to me, makes the system more "fragile" than if it's all coded in VB direct.

M
0
 
LVL 1

Author Comment

by:scj1
ID: 2704268
Thanks,

It makes more sense with respect to maintenance (i.e. binary patches are all thats needed without worrying about server interaction etc).

Stuart.

If only the data environment would allow some form of filing system, i.e. containers or at least sorting.

Maybe in VB2000?
0
 
LVL 12

Expert Comment

by:mark2150
ID: 2704323
You can sort you SQL results with the ORDER BY clause...

M
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now