De-Crowd my Back End Database

[redbull]

So there are 100 people on the front end. When ever they click an a button on the program, It fires an append query to the back end database, this way I can provide metrics..



I am convinced I am loosing clicks due to crowding on the back end, Call me a conspiracy nut thats fine.

So I am trying to have the program randomly divide the users to either 2 or 3 back end databases, There is no unique way of identifying each machine.. they all run the same image... So I am trying to find a way to have the program (on form load) randomly pick one of three back end databases.

I don't know Just looking for ideas. Any input is appreciated.

[orange]

So there are 100 people on the front end

What does this really mean?

For more comments on FE and split database see post #10 here

[redbull]

100 people have the front end file stored locally on their machine. Each front end is currently reporting to the same backend database

[redbull]

So there are 3 helpdesks... one of the desks has a different logonserver.. so I am able to isolate that one without issue..

Sub test()
Dim MyServer As String
MyServer = Environ("logonserver")
strstring = MyServer
strstring = Left(strstring, 3)


If strstring = "\\W" Then
' I need another statement here to seperate two desks... trying to find something unique... GAH!!!!
 End If

If strstring = "\\L" Then
DoCmd.TransferDatabase acLink, "Microsoft Access","\\Myserver\BackendDatabaseEAST.mcb", acTable, "tblfunclog", "tblfunclog"
 End If


End Sub

So, the code above will separate \\L from the other two desks... Now I just need another (if there is one) unique way of identifying the other two desks.

[orange]

Why do you think you are losing "data"?

It's been a while, but can't you look a the properties of the linked tables and see where they are located?

Have you really proven to yourself that there is an issue? Having duplicate backends (especially if they are operational and data is being updated) could be more of a problem?


This old code should identify the location of a linked table --.

Sub LinkedTbls()
'
'JED
'Routine to tell in this database which file a linked table is linked to
'
Dim mDB As Database
Dim tdf As TableDefs
Dim i As Integer
Dim MyBEPath As String
Dim strMsg As String
Dim sResponse As Variant
Set mDB = CurrentDb
Set tdf = mDB.TableDefs
On Error Resume Next

For i = 0 To tdf.Count - 1
 MyBEPath = Mid(mDB(i).Connect, 10)
 'Original code block follows:::
   If MyBEPath <> "" Then
      sResponse = "The table " & mDB(i).name & " is from  " & MyBEPath & vbCrLf
      strMsg = strMsg & sResponse
   End If
  If sResponse = vbCancel Then GoTo Get_out
Next i
MsgBox strMsg
Get_out:
End Sub

[redbull]

I know where they are located. I just want to have the front end to be able to determine the region they are located in, and then depending on that location select the appropriate backend.

I figured having three different backends would reduce the opportunities for error.

[orange]

But aren't the back ends being updated, added to, edited, deleted...?
It sounds a little like you haven't quite defined the problem. What is missing?
Do you have any sort of audit log? Would that be a better way to go?
Creating (copying a back end to 2 other locations) seems to be adding to effort of reconciliation, and increasing the chances for screw ups.

Do you have 1 version of the FE, then copy it to the local PC when users start?

[redbull]

The backend logs bascially only receives append queries...

INSERT INTO tblFuncLog ( chrUser, chrFunction, dtmTimeStamp )
SELECT GetUser() AS Expr1, [screen].[activecontrol].[name] AS Expr3, Now() AS Expr2;

So all we want to record is the user, button clicked, and time they clicked it.
......

The problem, what is missing? I think that we are losing clicked because the back end database is being overworked. Currently we get around 2000 clicks on the front end application a day, each clicks run this append query to the back end log.

Audit Log? That is kind of what the back end is doing?

Yes, I have one version of the FE, the users (all 100) of them have the file stored locally on their machines.

[orange]

Do you have a primary key on the tblFuncLog?

What else does your back end do? I never got an answer for
But aren't the back ends being updated, added to, edited, deleted...?

What actually invokes your Insert Into tblFuncLog? If it's clicking a button, are the users actually clicking the button? Are you sure?

Have you tried to run a loop from various FEs to do the Insert -- force it to either Insert a 100 records or fail on something.

My concern is that you seem to be focused on some Inserts aren't being done, but if your BE is doing Financial processing or Sales or Inventory, then those items should be "missing" also, and to me that's a bigger issue.

[redbull]

Do you have a primary key on the tblFuncLog? No, not necessary for the data that is being gathered.

What else does your back end do? (Nothing)
But aren't the back ends being updated, added to, edited, deleted...? Just inserting a row, with time/user/function information. Each time a user clicks a button on the front end, it fires an append query to the back end, just inserts a row with the info above.

What actually invokes your Insert Into tblFuncLog? If it's clicking a button, are the users actually clicking the button? Are you sure? This is what brings me to my tin foil hat... No way to prove one way or another... except...

Have you tried to run a loop from various FEs to do the Insert -- force it to either Insert a 100 records or fail on something.
This is genius, I will be testing this. I think with this method I can finally figure out if I am missing clicks or not.

[Rawb]

Ah HAH! OK, so let me see if I understand this correctly: You have a database that is used by multiple users. As an auditing/security function, you log each time ANY user clicks one of the buttons on the front-end of this database. You're concerned that, due to level of usage of this database, that not all of the button clicks are being correctly logged.

Assuming the above is a correct interpretation of your issue, I have a few questions:

1. Are the users all in the same physical location or on the same local (high-speed) network? Or are the users separated physically and simply linked by a WAN?
2. Assuming that the users are separated physically, does each physical location contain something that could be used as a de-facto File Server?
3. Does the "audit" data - the logging of user clicks - get added to a separate database? Or is it simply added to a Table in the same database as the rest of the back-end?
4. And finally, how are you linking the Tables between the frond-end and backend databases? Are you using a UNC path or are you using mapped drives?

[orange]

Seems Rawb has had the true AH HAH moment!!!

I'd put a pk on that table (but then again I believe every table should have a pk -- I think Codd suggested that).
I would also set up some loops to insert some records in your backend log table
I'd suggest 3 or 4 users

'---------------------------------------------------------------------------------------
' Procedure : t
' Author    : Jack
' Date      : 18-04-2013
' Purpose   : Small proc to add 100 dummy records to tblFuncLog
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Sub t()
Dim i As Integer
Dim msql As String

   On Error GoTo t_Error

For i = 1 To 100
    msql = "INSERT INTO tblFuncLog ( chrUser, chrFunction, dtmTimeStamp ) " _
    & " VALUES  ('User1Name','This is message (" & i & ")',#" & Now() & "# );"
    Debug.Print msql
    CurrentDb.Execute msql, dbFailOnError
Next i

   On Error GoTo 0
   Exit Sub

t_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure t of Module Module1"
End Sub

I'd get 3 or 4 users to use this -- adjust the username as needed to identify the 3 or 4 people.
You get them coordinated and get them to press run at your command.

[redbull]

Yes, interpretation correct.

Assuming the above is a correct interpretation of your issue, I have a few questions:

1. Are the users all in the same physical location or on the same local (high-speed) network? Or are the users separated physically and simply linked by a WAN?

They are all connected to the employer's vpn. Through out 3 locations.

2.Assuming that the users are separated physically, does each physical location contain something that could be used as a de-facto File Server?

Yes, I have the UNC path mapped to all users.

1. Does the "audit" data - the logging of user clicks - get added to a separate database? Or is it simply added to a Table in the same database as the rest of the back-end?

Added to a table in the same database . This is the only purpose of the back end.

1. And finally, how are you linking the Tables between the frond-end and backend databases? Are you using a UNC path or are you using mapped drives?

UNC path