Shared database, how to save filters for each user

[cowboy]

I have a shared database and there are a few people who use the database at the same time. Is there a way to make so it can save the filter settings for a split form after they navigate away from the form and come back to it? For Example


User1 on the Project_Form filters by city
User2 also has the Project_Form open so User1 couldnt save any sorts or filters that I know of and User2 sorts by project name
User1 navigates to details page, which closes the Project_Form
User1 returns to Project_Form [can I have User1 keep the same filter they previously set? (in this example by city)]

Users do not login to this database, so I am not sure if there is a way to identify which user is which and then save the filter with that identifier to a table.

Any ideas are appreciated, thank you.

-Dan

[ranman256]

The filters, and all other changes are saved in the Frontend.
which is then delivered to all users.

tho, a single user could save an old copy of their FE, then export their personal items (filters) to it.
this way, when a new FE is delivered,they could import it back.

[cowboy]

There is only 1 database file, it is not a front end with linked tables to a back end database. That being said if they are opening one shared file are you saying they still have a front end type of situation where when they open the file it creates a front end? If so their filter would automatically be saved as they navigate around the forms?

[CJ_London]

There is only 1 database file, it is not a front end with linked tables to a back end database.

you need to read up on how access should be setup. 1. Access should always be split into the database part (the back end) containing only tables and 2. each user should have their own copy of the front end (everything else - forms/reports/queries/modules). Failure to do so will result in corruption at some point. After 268 posts, I'm surprised you are not aware of this.

[cowboy]

you need to read up on how access should be setup.

This is the first time I have setup a database this way, but it is one of the methods shown by Microsoft itself:
https://support.office.com/en-gb/art...__toc310279041

So I did read up on it a little, and we have been testing for about 2 months now with about 8-10 users probably only 3 or 4 at the same time with only a few crashes.

The main reasons I didnt split the database was because access said it was acceptable in the link above and with 10 users I would need to coordinate and make sure everyone was using the latest front end.

I will discuss with the higher ups about splitting the database.

[CJ_London]

Must admit I've not seen that advice before - but it has plenty of warnings around reliability, availability, performance and security.

Updating users with the latest copy of the front end is a regular topic, there are a number of ways this can be accomplished. Plenty of threads on this and other forums about how to do it

[Micron]

If you decide to risk not splitting, you need to get the Windows user login name and find that in a table of users. Research ms access Windows user login or something similar. There are several options, one is the built in Environ username property. Also need a table of forms vs users and the filters for that form/user combo. Recommend not using literal sql text as I've seen cases that suggest it can't be resolved in vba. You can concatenate the filter values from this table into a sql string in code and apply that on the necessary form event. AFAIK, each user is opening a copy of this form in such a shared environment and their application of a filter won't affect anyone else. However, whatever the form is bound to will be affected by concurrent users, which means that conflicts can occur in terms of record locking depending on that setting.

[theviking]

As others said, there is really no good reason not to split the database. It's an hours worth of work and will eliminate a lot of potential issues.

Regarding the windows user name call, this is the function we use to retrieve the active login.

Option Compare Database
Option Explicit


Declare PtrSafe Function IGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal sBuffer As String, lSize As Long) As Long


Function GetUserName() As String


On Error Resume Next


Dim sBuffer As String
Dim lSize As Long
Dim x As Long


sBuffer = Space$(32)
lSize = Len(sBuffer)
x = IGetUserName(sBuffer, lSize)
GetUserName = Left$(sBuffer, lSize - 1)


End Function

[isladogs]

Just for completeness, there are 3 methods of getting user name
I prefer: CreateObject("WScript.Network").UserName - its more reliable than Environ and needs no API declarations

This article compares all methods: http://www.mendipdatasystems.co.uk/g...ame/4594424315