Multiple Users

[Ravi Kumar]

Can multiple users use the same database at a time??(different forms for each user , but same table for storing)
Is there any steps to follow to make it possible ?

 

[plog]

Yes. There's really nothing you need to do to allow it--just put the database somewhere where they both can access it.

Best practice, though, is to create a back end / front ends system (https://support.office.com/en-us/ar...database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc). One database holds the tables and then everyone gets a copy of a database that links to those tables and holds the forms/queries/reports needed for a user.

 

[Ravi Kumar]

thank you for your reply,

How can the database be converted to exe file (sorry if I am asking too much )??

 

[isladogs]

How can the database be converted to exe file (sorry if I am asking too much )??

You can't. What many people wrongly call an Access 'executable' is an ACCDE file where the code is compiled so it is no longer accessible to end users.
First split the database so the tables are in the backend and everything else in the front end. Relink to the backend tables.
Make a backup copy before the next step so you have a copy you can continue to use for development.
Run Debug...Compile and fix any compile errors in the FE.
This is essential or the final step will fail.
Finally run Save And Publish to ACCDE or Save As ACCDE depending on your version

 

[nonakag]

Does converting the Frontend to ACCDE make multi-users experience faster response times? Thank you.

 

[Isaac]

Not in my experience ... usually slowness, if it even is slowness that can be solved by better design, has to do with how your design is - related to forms & data binding, at a high level. Or, query optimization, to whatever extent that can be done in Access.

 

[nonakag]

Not in my experience ... usually slowness, if it even is slowness that can be solved by better design, has to do with how your design is - related to forms & data binding, at a high level. Or, query optimization, to whatever extent that can be done in Access.

Thank you for that information. I have a Switchboard that is a Dashboard and Main Menu. The Dashboard uses Dlookup for data calls on, Mileage, Inspections Overdue, Inspections Due Soon, Out of Commission Status, and Dispatched Status. Therefore five items x seven = 35 data calls for tracking seven vehicles. The Dashboard is left open for multiple users on their desktop to monitor status changes. If one user changes any of these Seven indicators, will everyone's Dashboard be updated with the one refresh to each Frontend's Dashboard? Thank you.

 

[isladogs]

Agree with everything @Isaac wrote above
There are many things you can do to improve user experience and one part of that is to make your database run as fast as possible
For example, see my article on Optimising queries
Good design is crucial to ensure users understand how to use your forms effectively
If the design is poor, user experience will be poor also

The purpose of using ACCDE files is to make your code secure

 

[nonakag]

Isla, Thanks. Do you know my question above? Someone, doing an update to the Dashboard underlying tables, will the refresh from their Frontend, update all the Dashboards open on Multiuser Frontends? Thank you.

 

[isladogs]

Is this is a split database with all users having their own copy of the FE on their own workstation...and with a shared BE on a local network?
If yes, then you may need to refresh the switchboard form using a timer event at specified intervals e.g. Set timer interval to 5000 for a refresh every 5 seconds. Perhaps using Me.Requery in the Form_Timer event will do the job.
If the database isn't split as described above then you have major issues,

 

[The_Doc_Man]

To prevent file-locking type of interference, you would have multiple copies of the front-end file, one per user (and private to that user as opposed to shared on the central server). In that case, the stuff you describe involving DLookups would probably not refresh without some other driving event to trigger that refresh.

 

[Pat Hartman]

You've hijacked an old thread with a new question and everyone seems to be going along for the ride so I won't split the thread but in the future, please start a new thread for a new question. It just keeps the forum cleaner and more useful to others.

There are better ways than dlookup() to build your switchboard. You should look into creating subforms with totals queries. That will also almost certainly reduce ongoing maintenance. Domain functions run a query so if you are using 10 dlookups() for 10 values, you are running 10 queries. Instead, you could create a totals query that returns all 10 records at once.

 

[Isaac]

The Dashboard uses Dlookup for data calls on, Mileage, Inspections Overdue, Inspections Due Soon, Out of Commission Status, and Dispatched Status. Therefore five items x seven = 35 data calls for tracking seven vehicles. The Dashboard is left open for multiple users on their desktop to monitor status changes. If one user changes any of these Seven indicators, will everyone's Dashboard be updated with the one refresh to each Frontend's Dashboard? Thank you

1. If any user changes data in the table(s) that the Dlookups are reading from, then any form, in any FE file, which any user is using, (and possibly dependent on that Form's settings related to refresh, requery, etc), will be updated. If I understood the situation correctly?
2. Any Dlookups that can be combined to a single query, probably should be. If you take 3 Dlookups and collapse them to a single query returning 3 fields, 90% sure that will be faster. You can then use a bit of code to read the recordset from that query and place it in the desired controls (textboxes) etc
3. For a single scenario--i.e., a single Dlookup vs. a single recordset-opening, I have no grand generalizations on when one might be faster than the other (I think Isladogs has done a lot of research on that), but it's certainly worth experimenting and finding out your load time! You can always set up some Date variables with a debug.print or Messagebox that tells you how many seconds each one took, then use the faster.

 

[nonakag]

Pat, yes, I will remember to post a new thread for different topics. Thank you.

Yes, one BE and many FEs. The Dlookups are searching the Mileage Table, Inspection table, Maintenance Table, and Reservation Table. That was the dilemma I was facing trying to Join 4 Tables in one query to be the one data source for a single form's controls. The DLookup returns a boolean addition of two check boxes being a -1 or 0 adding up to -1+(-1) = -2 for Available and -1+0 = -1 for Dispatched. I can experiment with calculated query fields to do the math if I can get the complex join of all four tables. Would this be the faster way to go? Thank you.

 

[Isaac]

If you can't find a natural way to join the tables in a query, then that may not be the way to go. I mean, doing "anything" to get them into a 'query' (further lookups in the query, subqueries, etc) is not what I meant. If you can, you can - if you can't, maybe try the recordset method to see if it's faster or not.

 

[nonakag]

If you can't find a natural way to join the tables in a query, then that may not be the way to go. I mean, doing "anything" to get them into a 'query' (further lookups in the query, subqueries, etc) is not what I meant. If you can, you can - if you can't, maybe try the recordset method to see if it's faster or not.

Thanks, Issac, I'll experiment. All the FEs are identical. Any updates to the Switchboard/Dashboard form has me.dirty=false statement followed by a [Forms]![Switchboard].Refresh. I hope this refreshes all the FE Switchboard/Dashboards that are open on the Multiuser's Desktop?

 

[Pat Hartman]

Access refreshes forms but you might need a timer event in the switchboard so you can control how frequently the switchboard refreshes.

 

[Isaac]

Thanks, Issac, I'll experiment. All the FEs are identical. Any updates to the Switchboard/Dashboard form has me.dirty=false statement followed by a [Forms]![Switchboard].Refresh. I hope this refreshes all the FE Switchboard/Dashboards that are open on the Multiuser's Desktop?

If you write code to open a recordset and place results in a control, you'll definitely need a way to explicitly re-run that.
If you have an expression as a control's ControlSource, like =Dlookup or =CustomFunctionThatIncludesRecordsets then I believe you'll need to use the control's .Requery method

Note: (MS)

The Requery method updates the data underlying a form or control to reflect records that are new to or deleted from the record source since it was last queried. The Refresh method shows only changes that have been made to the current set of records; it doesn't reflect new or deleted records in the record source.

 

[nonakag]

Pat & Isaac, Thanks to both your suggestions. I used the following code:

Sub Form_Load()
    Me.TimerInterval = 20000
End Sub

Sub Form_Timer()
    Me.Requery
End Sub

Works really well. Prior to this, I thought any of the desktop FEs would send a requery to all the other FEs in the network upon making record changes. However, they only affect their own instance for the Dashboard. Too bad there is not a VBA solution that can stay with the BE that could do this automatically each time the BE data is changed.

 

[The_Doc_Man]

Every FE automatically refreshes a form when you take any action on that form that changes records - like navigating to another record as one example. But you are right - there is no passive auto-requery on a form that isn't doing anything at the time. This is why Access supports Timer settings on a form. Even so, setting the timer to a small number (like, <15 seconds) is generally not a good idea because of overhead issues.