Improve company productivity with a Business Account.Sign Up


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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
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…

585 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