Multi User, Multi Select Subforms, large data set MS Access

[jmp66]

I have a fairly simple database that has a large dataset that is refreshed daily.

194k rows by 70 fields. Size is about 123mb in excel.

I'm developing in 2007 however some have Access 2003. The network chosen to put it on is overfull with terabytes of data.

I was recently told it was to have 30+ users while we talked about 8. Love when that happens.

It uses three subforms with a select button that is used as criteria. These subforms are tables with related criteria and a check box. example department, warehouse etc.

I trimmed down the fields of the primary data set into a Minimal table used for counts and created index numbers for text based selectors. Went from 2 minutes to return count to about 5 seconds. So far so good.

Concerns: With multiple users

Users with 2003 just seem to have slowness when loading forms although there is not a bound record set. Their initial counts often did not finish. I did modify the pagefile.sys on my computer.

Currently a single database (Not Split) will users compete when selecting criteria?

If users used the same front end would there be any difference. Would there be any performance gains?

If there would be conflicts, could I create user specific variables (as in lists)to resolve.

I do not have the options for users to have their own version of database or their own front end. SQL Server backend is not feasible.

Any suggestions? I have searched the web over and feel a bit restricted.

Thanks.

JMP

 

[MajP]

If you truly have a single database not split into front end and backend, then all your questions are a complete waste of time until you split the database. Running a multi user database not split is just a horrible idea. It is eventually going to crash, corrupt your data, and get slow. Explain why users could not have their own front end. That makes no sense.

 

[jmp66]

Just to clarify. Generally they click a check box, or press a button to select all, or clear all related criteria. Because of the 65k record limitation on exports (for simplicity, I'm sure I could overcome with code) they need to know if their selection is exportable. Here is an example, the rest is just addressing potential errors and exports.

I'd provide code but it is fairly standard:

Sub WHS_Items_tabulation()


Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim strSQL As String
Dim strSQL2 As String
Dim WHS_ITMS As Single
Dim l_UPD As Date
Dim strExportable As String

Set db = CurrentDb()

'Check for Missing Criteria
Dim cntRSC As Integer
Dim cntDept As Integer
Dim cntItmClass As Integer
Dim msgText As String


cntRSC = DCount("RSC", "tblRSCSelect", "RSC_Select = True")
cntItmClass = DCount("itm_cls", "tblITM_CLS_Select", "CLS_SELECT = True")
cntDept = DCount("department", "tbl_department_Select", "dpt_select = True")

msgText = IIf(cntRSC = 0 Or cntItmClass = 0 Or cntDept = 0, "You need to select at least one item from RSC, Item Class, Department", "")

If msgText <> "" Then

MsgBox msgText

Exit Sub

End If

'end of criteria check

Dim rsCount As Integer
Dim queryNameOrSQL As String

queryNameOrSQL = "qry_Export_by_Criteria_lite_version"

Set rs1 = db.OpenRecordset(queryNameOrSQL)
If rs1.RecordCount = 0 Then
MsgBox ("Your criteria returned 0 items")
GoTo UpdateWHSEattrib:

End If

rs1.MoveLast

WHS_ITMS = rs1.RecordCount



rs1.Close



'WHS_ITMS = DCount("*", "qry_export_by_criteria")



strExportable = IIf([WHS_ITMS] > 65000, "Your current criteria will return too many items to export", "Ok to Export")

'strSQL2 = "UPDATE tbl_warehouse_Items_tabulations SET Warehouse_Items =" & WHS_ITMS & ", Exportable = & strExportable & " ';"
'DoCmd.RunSQL (strSQL2)

UpdateWHSEattrib:
Set rs2 = db.OpenRecordset("tbl_warehouse_Items_tabulations")
With rs2
.MoveFirst
rs2.Edit
rs2!warehouse_items = WHS_ITMS
rs2!exportable = strExportable
rs2!last_update = Now()
rs2.Update

End With

rs2.Close
db.Close


End Sub

 

[jmp66]

They cannot have their own front end because of limited understanding on the part of the requestor and that it will be passed on/be adminstratedto someone who is just going to press buttons.

 

[MajP]

I do not understand your reply, and I do not think you understand about splitting a database. You can google this to get lots of information.

http://www.access-experts.com/default.aspx?selection=TutorialSplitDB&sm=18

http://www.databasedev.co.uk/split_a_database.html

If the user justs needs to click buttons then it makes even more sense to provide a lock down mde/accde or runtime front end.

However, with that said those dcounts are inefficient especially with where clauses. Try someting like ecount or roll your own.

http://allenbrowne.com/ser-66.html

 

[jmp66]

Majp appreciate your response. I will split. I use/administer a split database daily. I have built a number. The issue in my mind is a split database does not make it effective in isolating individual user inputs. I have to clear out what the previous user used as criteria often and also experience write conflicts. I did not build it. Definitely I am missing something. Could be that criteria table (seems low tech) is in the back end not the front.

The requestor does not understand that having three users who will recognize when they do not get the right results compared to having multiple users hitting the same form interface pulling large amounts of data. Not getting the right data in this case means missing data or getting the wrong data.

I strongly suggested creating local front ends mostly for performance reasons. However, when passed to the new administrator next week anything behind the scenes will be over their head. So I have tried to keep it as simple as possible. Similarly, they did not want to have to distribute front ends. Yes, I agree it does not make sense.

Since I can't use the fairly simple method I am going to grab user name and store criteria temporarily. I'd like to clear out any prior criteria so something they had to scroll down to select is not missed. Might be unecessary. Not sure what is stored in the front end and what is in local memory. This is one of two projects this week. This is the simpler one

thanks,
jmp

 

[MajP]

I am sorry but 30+ people sharing the same database, pulling it over the network is not even worth discussing. The fact that this would work at all would amaze me. It is just not an option to employ Access this way. Any discussion on fixing that would be like worrying about giving a gunshot victim an aspirin to relieve their headache. In regards to conflicts, in a split database the only conflicts are working in the same field in the same record. It most employment you will never have a conflict.

I am not sure what the original problem is. If 2003 forms are slow to load that is because you have to pull them across the network.