Solved

Developing and Deploying Client/Server App

Posted on 1998-08-21
7
150 Views
Last Modified: 2010-04-30
I have been contracted to redevelop 10 VB 3.0 applications and one Access 2.0 database for a bank.  The applications will be redeveloped in VB 6.0 and the Access database will be converted to Access 7.0.  The bank's configuration is:  3 branches where one may be considered headquarters;  at the headquarters, there is one NT Server where the database and applications reside;  there are two T1 lines connecting each of the other branches;  each workstation connecting to the server runs Windows 95;  there are approximately 6-10 users who may simultaneously access the database at one time.

Issue:  the VB 3.0 apps were coded by an amateur (not that I am an expert) who might have 40 text boxes on one form with names Text1, Text2,...Text40, who would place an END statement prior to code that he have liked executed, etc..  Occasionally, the bank experiences database corruption.  When I recode the apps in VB 6.0, I need to be aware of certain practices, such as, Record Locking, Closing Recordsets, etc..

Without referencing a book, might someone suggest a model that includes practices to avoid corrupting the database and to ensure data integrity.  I assume I should use the Jet Engine to connect to the database.

Sorry for the lengthy question.

David
0
Comment
Question by:dfhaines
7 Comments
 
LVL 14

Expert Comment

by:waty
ID: 1430194
Using Access 97, I don't see much data coruption as in Access 2.0

By the way, you should make backups or even implementing an automatic replication in another database (in Access 97 it is but not in Access 2.0)
0
 
LVL 2

Expert Comment

by:lmorris
ID: 1430195
I seem to remember reading something to the effect of (this is not a quote):

If you connect everyone directly to the database, you might experience corruption if one of the workstations is doing something and get improperly shutdown or locks up.

Something like that anyway. In-fact, I think it was is the VBPJ magazine about developing multi-user applications a year or so ago. If I remember right, this persons fix to improve performance and reliability was to create a server side object that talked directly to the database. All the clients talked to the server size object.

Do note that I develop with SQL Server and not Jet, so I don't know if this solution actually works. But there you have it.
0
 

Author Comment

by:dfhaines
ID: 1430196
Thank you for your comments.

I had considered developing a server component to handle all client requests on the DB.  Any thoughts?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 14

Expert Comment

by:waty
ID: 1430197
We have done this in an application with SQL Server (4.2). Instead of writing directly to the DB, we was making requests on this DLL to write to the DB. This DLL had the responsability to write the same data to 4 databases and sending it also through a pipe to an AS/400 computer.

When a database was down for some reason, automatically, the user application switched to another database.
0
 
LVL 9

Accepted Solution

by:
cymbolic earned 270 total points
ID: 1430198
Your database will be more secure and reliable with SQL Server, and multiuser/locking capability will be better,  Also, ver 7.0 is due out in November, and promises (finally!) to support row level instead of page level locking, which makes multiuser locking problems less significant.  In addition, you get the SQL Executive, which aids in unnattended task scheduling of backups, etc.  I would choose SQL server over MS Access anytime in a WAN implementation.

As far as practices go, and despite your reluctance to get book references, I recommend you get and read (or just go down to Barnes & Noble and browse) "Hitchiker's Guide to Visual Basic & SQL Server", by W. R. Vaughn.  It contains a comprehensive comparison of Access vs SQL Server, with explanations and examples of "Best Practices"  and why you might want to use one over the other.

I have used both extensively in client/server enterprise development systems.  Major systems are always developed in SQL Server.  Access becomes a convenient doorway for local database operations, and to support migration and data distribution.  Many systems are developed to use both products, with the positioning for SQL Server as shared database, and Access datbases on local machines for distributed processing, and datamarting.

Best programming practices involve using unbound data controls, enterprise version of VB, RDO (and in your case ADO) access methods, with careful consideration for multiusr locking conflicts.  A primary approach to reducing locking problems is to design your data structures so that you do not have one common table always accessed (for instance a central control number dispenser table), and to do most of your reading with filtered, where clause restricted SQL, read only, and execute your changes with update SQL, rather than the worst choice, which is large updateable cursor result sets that sit for eons on the client, with many locked pages on the server, wating for some user to make a change while you do the updates using the .edit methods.  This is a formula for failure in multiuser situations.

In a large system, with many modules, it is appropriate to design your program architecture to use a central .dll for all data i/o, giving you less maintenance problems downstream, and better common control capability for business logic.  You can either provide a common code .dll to run on each client, or for the more adventurous, use DCOM and a remote connection to a common database front end program, running on a separate machine.  Eahc interation makes for a better final system, but more difficulty in learning, understanding, and development of the app.  It's easy to crank out cloned code and a gazillion programs, but in the long run, this practice will limit your ability to respond, maintain, and control your process.
0
 

Author Comment

by:dfhaines
ID: 1430199
Cymbolic,

Thank you for your insightful response.  I will award you the points.  Before I closed the question, however, I wanted to point out a few things.

First, I will eventually port-over to SQL Server.  In the meantime, however, I am upgrading to Access 7.0/VB 6.0.  I do realize that Access is not a true back-end database engine.  Second, since I am confined to Access over the next 2 or 3 months, I want to ensure that engineer the data i/o correctly.  Do you recommend that I make an initial connection to the MDB at the start of the application, and then use Filtered SQL to retrieve the data for presentation and then immediately close the recordset to free memory.  It is a transaction-driven application, so I do not see the need retain the recordset, and hence, lock that page(s).

Thank you for time and attention to this matter.

Once you respond, regardless of your comment, I will award you the points.

Thanks again,
David
0
 

Author Comment

by:dfhaines
ID: 1430200
Please reply to my comment when the chance permits.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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 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…
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…

837 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