Developing Client/Server Apps from VB to SQL Server 6
What have been your experiences? We intend to use VB as a front end tool. Our average application will have three to six users. Please email me if you can share any information. I would highly appreciate your help.
From: Terri Soler
Email: tsoler@npr.org
If you are programming to the ODBC API this is a very low level approach. You have to be familiar with how ODBC works and how to call its API before you can really get results. In fact you will need to build more code to do anything taking this approach. You will probably also find that the code is harder to debug because it is more complicated and there is more of it. On the other hand it will be very trim and quite fast. The memory overheads are at a minimum with this approach.
If you were to use DAO/JET to access the SQL Server you would still be using ODBC at the lower levels, but you would also being using JET to communicate to the ODBC layer. This means you have ODBC, the ODBC drivers, JET and DAO loaded into memory. Using ODBC directly you only load ODBC and the drivers into memory. Conversely you do not have to deal with or debug as many potential problems because JET deals with the interface to ODBC and handles all its initialisation very neatly.
For someone who is familiar with using JET from VB, the move to an ODBC back end is almost trivial. Of course, Client/Server should be undertaken with a different approach, but merely getting the code to work and achieving a connection is quite simple. You should also find that the performance is quite reasonable. It's not quite as good as direct ODBC but it's pretty close.
One thing to be aware of is to make sure you link to the SQL server database directly when using JET, not to connect to an Access database that has attached tables from the SQL server database. This means you have to do twice the work to get to the data (actually the engine does twice the work) and it runs very slowly.
If your systems will be used by 5 or 6 people mostly, either method will suit. Essentially SQL Server is built to accept much greater load than that, so it will cope. Your connection strategy will have comparatively little impact on the maximum users that can be connected. The exception to this would be if you were to use an Access database with attached tables. In this case you are limited by the Access database's abilities. I don't like to have more than about 6 users hitting the same Access database at the same time. It deals with concurrent hits well only to a point.
I hope all this rambling helps guide you in some way. If you have any more specific questions feel free to send them to me. Enjoy Client/Server.
