Go Premium for a chance to win a PS4. Enter to Win


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
  • 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

Independent Software Vendors: 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 (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

971 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