Multi-user database question 1

[jender624]

I have an Access application (frontend and backend) that I'm developing for a target user group of approx. 130 people. I know, I know, access can't handle that kind of load. The compromise that I'm using is limiting the number of people who are logged into the application at any given time. I'm tentatively setting this number to 25 people, which could change pending a test period.

Upon opening the app, it would count the number of current users, and if that number is 25 display an appropriate message and disable the fields on the log in screen. The reason I'm allowing the users to get to the log in screen at all (vs. shutting down the app right away) if there are 25 people using the app is because I want to include a list box of all the currently logged in users on the login screen so they can see who is using it. So potentially 25 users could be adding, updating, and deleting data, while the other 105 could see the list on the login screen, but not progress to the data entry part.

My question: Would it be better to simply shut down the app if the max user count has been reached, or is it okay to have 105 users accessing a read only log in screen that will tell them who is logged in, but not let them log in. OR, should I do something like a .txt log file to list all current users?

Sorry for the verbose explanation...

Thanks,

jender624

 

[willir]

jender624

I guess my first thought which you probably already researched this, is why not upgrade to MS*SQL or Oracle solution...

There are two are two problems with Access in a multiple user network environment - network traffic and record locking.

Using the standard forms, Access is not very kind to the network. When you open up a form keyed on a table, say SalesOrdersTbl, then the entire record set is copied to the desktop. If you have 100,000's of sales, that is a lot traffic. Now multiply this by 10 salers order clerks...

Record locking becomes more and more of an issue as the number of users increases - especially if many are doing the same type of task.

If you use a front end loaded on the desktop, then you can actually test for your concurrent user condition before connecting to the backend.

Your backend tracks the number of concurrent users, say in table ConUserTbl.

The frontend connects to the ConUserTbl. If the current count exceeds your maximum, then it disconnects from the backend. The login screen stays open and a RETRY option. If the user clicks on the RETRY button, the process repeats. You can make this process more user friendly by using the form property "Timer Interval" and "On Timer" event to attempt to reconnect.

If ConUserTbl shows that there are available connections, then the rest of the coding links the backend to the frontend programatically.

An alternative
Data Access Pages - I suspect web based systems will be kinder to the network and record locking, especially since you have more control on the connection type.

Richard

 

[jender624]

Thanks for all your input, Richard! I did originally check out an Oracle solution, but the manager I'm creating this app for didn't want to pay the support costs within the organization if he could get by this way.

I did like your suggestion about only connecting to one table to check the current number of users. Right now each table in my front end is linked all the time to the backend. I haven't done much in terms of controlling table links programmatically; is there any resource you could point me to for some self education?

Thanks!

jender624

 

[willir]

I knew that would be your next question.

I have not done it yet, but I know it can be done, and it is a project I will work in the near future.

One the top Tek-Tips members who has been less active as of late is Bill Powers. He posted a download at the following web site that should get you started.

http://www.wpower.fsbusiness.co.uk/

Also, search this specific forum for other programming examples. I have seen this specific question posted several times.

As I see it, some things to consider...
- Make sure your first / autoexec form is not bound to a linked table; otherwise the user will see a linking error - can not find table.
- You can actually have two backend databases - one to handle only concurrent users, and the other for the main data area. I sometimes split the backend into two databases to accommodate security issues. But this may be another reason. This way, you can see how many users are logged in to the main database, and how many are trying.

 

[jender624]

Awesome! Thanks so much for all your valuable feedback. I'll start getting myself smarter on the linking situation per your suggestions. Have a star!

jender624

 

[scking]

As an older solution using DAO this is CBF which accepts a filename selected by implementing a browse capability and filling a listbox. We've used this for years+. If course there are a couple of parsing functions called that aren't included here but they are relatively simple.


' PARSE THE PATH AND DBNAME FROM THE CBOSELECTOR CONTROL
If Len(cboSelector) > 0 Then
strSelectorFPath = GetFPath(cboSelector)
strSelectorDbName = GetDbName(cboSelector)
Else
GoTo ExitProc
End If

For i = 0 To db.TableDefs.Count - 1
Set tbl = db.TableDefs(i)
If tbl.Connect <> "" Then 'Skip base tables
Select Case strSelectorCase
Case ".mdb"
If Dir(cboSelector) <> "" Then
tbl.Connect = ";DATABASE=" & strSelectorFPath & strSelectorDbName
tbl.RefreshLink
End If
Case "\"
If Dir(cboSelector & strSelectorDbName) <> "" Then
tbl.Connect = ";DATABASE=" & strSelectorFPath & strSelectorDbName
tbl.RefreshLink
End If
Case " "
If Dir(cboSelector & "\" & strSelectorDbName) <> "" Then
tbl.Connect = ";DATABASE=" & strSelectorFPath & "\" & strSelectorDbName
tbl.RefreshLink
End If
Case Else
MsgBox "Invalid database in selector box"
Exit Sub
End Select

TableCount = TableCount + 1

End If ' If tbl.Connect <> "" Then

Next i

If strUnLinkedList <> "" Then
MsgBox "The following tables were not linked. " & vbCrLf & _
"Please investigate and Relink manually" & vbCrLf & vbCrLf & _
strUnLinkedList
Else
MsgBox "Tables have all been successfully linked. "
End If
'SetStatusBarOption (OFFSWITCH)


---------------------
scking@arinc.com
---------------------