Printable View
In your opinion, would you say that accessing a database on a SQL server using VB & ADO is an efficient combination to base a 50+ concurrent user database on.
The VB client would establish a connection with ADO and open and modify recordsets depending on user input (employee database type thing).
So... would YOU say that this is fine or is there a better way to accomplish this?
Your choice of tools are good ( SQL Server,VB and ADO). You will want to avoid keeping recordsets open for a long time. In other words 1)Get the record(s) 2)modify locally and 3) send update back to server. Your update procedure should make sure that no one else has changed the record in the meantime (use timestamps).
That's what I was thinking... rs.Open, populate the form, and close the rs. When they click Save it does a SELECT Stamp FROM bla bla bla and makes sure that it wasnt changed, and does a .Update and closes the rs again.
Or better yet, create a Stored Procedure which will modify the record, then from VB pass parameters (whatever number of parameters you have defined in Stored Procedure) to modify the record. This will increase the speed of your apllication significantly.
How come stored procedures execute so much faster? Just because they are server-side?
Yeap. For a recordset it has to make a trip to the server, where Stored Procedure is being executed on the server and it is precompiled.
May be you also can use the BatchUpdate in ADO connection.