Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


VB6, SQL-Server 7 and the Data Environment

Posted on 2000-04-11
Medium Priority
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
Question by:scj1
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 12

Expert Comment

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.


Author Comment

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.
LVL 12

Accepted Solution

mark2150 earned 600 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.


Author Comment

ID: 2704268

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


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

Maybe in VB2000?
LVL 12

Expert Comment

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


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

715 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