Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

multiuser data access

Posted on 2001-06-14
5
Medium Priority
?
150 Views
Last Modified: 2010-05-02
hi..i have created a vb app which is going to be used by multiple users. the vb app is using odbc dsn to access the sql server and ado to retrieve and update data on sql. Around 80 people will be using the vb app. i was wondering if there are any issues/limitations when using odbc and ado in this scenario. Also, iam sometimes getting an error like this when the app loads :

'This action cannot be completed because the other application is busy. Choose 'Switch To' to activate the busy application and correct the problem.'

is this related to ado or problem with focus of the app??


Thanks in advance
0
Comment
Question by:anishs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:andyclap
ID: 6194584
While there's no issue with ADO->ODBC, it all depends on how you implement the data access. With an 80 user system, you'll really need to design the VB app to make sure that you're accessing the data efficiently, network traffic is minimised, and that there's no major locking going on. The best advice I can give without knowing how your VB app is coded is:
1) No data bound controls. They really bring multiuser systems to their knees.
2) Optimistic locking using timestamps for integrity
3) Use explicit SQL execute statements for deta modifications rather than updatable recordsets
4) Use stored procedures to do processing on the server where possible
5) Think carefully about data modifications within transactions to take accout of locking
6) Make sure your database is well designed with a good index strategy to prevent bottlenecks on the server.

80 users is close to my personal preference for SQL Server on a standard 1 CPU DB Server, at this volume you really have to be careful with locking etc.
0
 

Author Comment

by:anishs
ID: 6198993
Thanks ..below is the form load code which i believe is the most crucial bit as everytime the app loads (which happens quite a lot). i believe i have done most of what was mentioned above but if theres any other ways to improve it, i would be keen to know. One option iam looking at is use MTS to make it more scalable.

Private Sub Form_Load()

Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

'Create the objects for control population
Set con = New ADODB.Connection
Set rst = New ADODB.Recordset

'Open the connection to database
con.Open "DSN=xxx;" & "Uid=xxx;" & "Pwd=xxx;"

' Create a keyset recordset based on the
' table with optimistic locking

Dim strTemp As String

rst.Open "SELECT xxx,xxxx FROM xxx WHERE (xxxx LIKE '[x]')", con, adOpenKeyset, adLockOptimistic
If rst.RecordCount <= 0 Then
rst.Close
con.Close
Set rst = Nothing
Set con = Nothing
strTemp = MsgBox("Sorry No Records were located. Please contact your Systems Administrator for help", 4112, "Error")
lstT.Enabled = False
cboT.Enabled = False
btnExit.Visible = True
btnExit.Enabled = True

Exit Sub
End If

' clear columns
lstT.ColumnHeaders.Clear
lstT.View = lvwReport
' make columns
lstT.ColumnHeaders.Add , , "xxx"
lstT.ColumnHeaders.Add , , "xxxx"

' populate fields
Do Until rst.EOF
strTemp = Format(rst("xxx"), "###,###,##0")
Set mitem = lstT.ListItems.Add(, , String(11 - Len(strTemp), " ") & strTemp)
mitem.SubItems(1) = rst.Fields("xxx")
If Not IsNull(rst("xxx") & ("xxx")) Then
cboT.AddItem Trim$(rst("xxx") & " - " & rst("xxx"))
rst.MoveNext
End If
Loop
cboT.AddItem ("")
'autosize column headers
Dim col2adjust As Long
For col2adjust = 0 To lstT.ColumnHeaders.Count - 1
Call SendMessage(lstT.hWnd, LVM_SETCOLUMNWIDTH, col2adjust, ByVal LVSCW_AUTOSIZE)
Next

'enable full row selection
LVFullRowSelect lstT
'set columns flat
flatheaders
rst.Close
con.Close
Set rst = Nothing
Set con = Nothing
End Sub
0
 
LVL 6

Accepted Solution

by:
andyclap earned 400 total points
ID: 6199131
I'd recommend trying a forward only cursor here, as you're just looking once through the records and not updating.
Also,
If rst.RecordCount <= 0 Then
could be replaced by
if rst.eof then
but it's really 6 of one, half a dozen of another.
0
 
LVL 6

Expert Comment

by:andyclap
ID: 6199132
Also, I get the "can't switch to app" message sometimes myself. It's just one of those "live with it and reboot" things.
0
 

Author Comment

by:anishs
ID: 6199387
thanks..i have asked the 'cant switch to app' error to couple people and their responses have been different. some responses were:

"bug with ado"
"another app is trying to take focus at same time"
"a problem with sendmessage code, i should be using callback"

anyways, i will probably use a 3 tier architecture with mts in the next revision of the program which i hope will fix couple of issues especially scalability issues.

Cheers
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Suggested Courses

670 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