Expert: Ravindra - 4/29/2004

Question
I have an application in VB that has started to grow. Initially this app was a stand-alone "single-user" app. It has now prgressed to the point where we need multiple people running the app. The db is an Access 2000 DB.

I have done some work in there but from time to time we will still get runtime errors stating something is locked  or just recently got a run-time error that stated "Connection string: Too many active users". I know there wasn;t too many users accessing it but maybe internally it thought there was for whatever reason...

What is the rule of thumb when designing an app for multi user access with regards to DB connections, recordsets etc...

SHould you be using disconnected recordsets? Always setting recordssets = nothing when you are done etc...

I suspect that what is happening in my case is that am creating a whole pile of recordsets in the main procedure so they are available everywhere. Is this creating too many connections or something?

ANy help in a good guide to multi-user programming as far as db connections (maintaining to a minumum) recordsets etc...  would be quite beneficial as I think I;ve got some bad code...

If only one user is in the app and I look at the open files on the XP machine that it is running on, it will show 40 locks on the DB! That's with only one user in there! What on earth is creating that many locks? It has t be something with the code....

thanks,

Brad  

Answer
for complex and multiple user needs, better switch to SQL Server 7 or 2000.

another suggestion is put the declarations, connect and disconnect functions, adodb as new etc .. in a bas module.
every time you need, connect and disconnect in each procedure. can have all teh multiple recordsets in bas module, but close the recordsets and the connections immediately. though this may slow down, it will be available for others.

typical example of bas module code: which you can alter to suit your needs.

Option Explicit
Public LoginSucceeded As Boolean
Public Const Shuru As Date = #2/15/2004#
Public conn  As ADODB.Connection
Public rec As ADODB.Recordset, rec1 As ADODB.Recordset, rec2 As ADODB.Recordset
Public esql As String, esql1 As String, ravi As String, username As String, startdt As Date, datee As Date, Okay As Boolean
Public formu As String
Public Function connect()
   ravi = Chr(39)
   Set rec = New ADODB.Recordset
   Set rec1 = New ADODB.Recordset
   Set rec2 = New ADODB.Recordset
   Set conn = New ADODB.Connection
   esql = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Dhana1.mdb" & ";Persist Security Info=False"
   conn.Open (esql)
   Okay = True
End Function

Public Function Disconnect()
If Okay = False Then Exit Function
   conn.Close
   Set conn = Nothing
Okay = False
End Function
Public Function dater(date1 As String)
   Dim ag As String, i As Integer, k As Integer
   i = Month(date1)
   k = Year(date1)
   ag = 15 & "/" & i & "/" & k
   dater = Format(CDate(ag), "Medium date")
End Function

Ravindra M.G.