Multiple User Access Warnings - Unsure if problem is fundamental

[Steveoh421]

Hello fell MS Access users,



I've had a scout about for an answer to my query and I can't seem to find a solution, but please accept my apologies if you happen to have seen a solution to this already.

I work in a small office with 4 employees. We've set up a relatively straightforward client database which includes 1 table for all client info, a form which is used as a user-friendly quick-access info sheet and a couple of reports and queries to sort data as we need it. I split the database to allow everyone access at different/simultaneous times which for the most part we're doing fine, I've just noticed warning or error prompts which I can't say are entirely consistent depending on the situation.

I have split the database and we all have shortcut access to the files. I've found that the majority of the time we see warnings and error messages (screengrabs attached). We can save our data updates and I've only made one design change that didn't give us any problems. In the worst case, we'll shut down the programme and let one user update the database which isn't a huge problem, but it may turn into one if the company continues to grow.

I'm just wondering if there is a fundamental/major issue with the sharing settings that means we keep getting these warnings and access problems?

[ArviLaanemets]

Having split the database and giving an individual FE for every user doesn't set the app to multi-user one. You have to set back-end to be shared on every user's Access (File > Options > Client Settings, and in Advanced section check Shared). When some user has Exclusive checked, then others can't open the app when this user is using it.

(By default usually this setting is Shared.)

[Steveoh421]

Hey there, thanks so much for posting up a reply so quickly. I'll get a look at the files when I'm in and check those settings.

This is greatly appreciated, thanks again!

[ssanfu]

Just wanted to stress what Arvil said.

EACH used should have a COPY of the FE on their computer. If everyone uses the SAME FE by using a shortcut, the probability of dB corruption is extremely high.
It might not happen tomorrow or next month or next your..... but it will happen.

[Steveoh421]

Just wanted to stress what Arvil said.

EACH used should have a COPY of the FE on their computer. If everyone uses the SAME FE by using a shortcut, the probability of dB corruption is extremely high.
It might not happen tomorrow or next month or next your..... but it will happen.

Spot on, thanks for stressing the point as the extra knowledge is definitely handy.

[Micron]

Two other things that may be noteworthy:
- check your record locking setting (suggest Edited Record as opposed to No Locks or All Records)
- check that users are using the properly configured shortcut; i.e. it doesn't open the db exclusively because the command line was written that way.
FWIW, the 2nd and 3rd messages suggest you are trying to make design changes to the db that everyone else is using. Don't!!
You have your backup and your design copies. Each time you release an updated FE, you replace the backup with the new version (or archive a certain number of backups via date as I did).

Methinks you need to research how to distribute, alter, and ensure users are using the most up to date version. There are many ways you can have the FE check the version number on startup.

[Steveoh421]

Hello again,

Firstly, happy new year to all and my thanks again for the help with my previous query.

I've made some of the basic adjustments to our database i.e. made sure the Advanced settings allow Shared open mode and that the default record locking is set to Edited record however I've noticed a more specific prompt for the attached error messages. In our database we have a field for free text entries to allow the technicians to keep notes as our projects progress. I've noticed that the warnings we get come up when we access the field that I've titled 'Customer History Notes', in particular we have a record saved which doesn't allow us to enter notes at all. I'm not sure if this is somehow a corrupt entry, but in any case it means we're seeing these warning messages come up routinely.

I should add that the database is working as expected, I'm just not sure how to fix the issue of these recurring error messages which often has us guessing on whether the database is working consistently.

Many thanks again for all input previously, this is greatly appreciated.

[ArviLaanemets]

Am I right, that employee(s) working with Client info, and technicians inserting notes about client are different people - and may work at same time on same record?

Create a separate table for notes:
tblNotes: NoteID, ClientID, NoteDate, Note

Be sure, that working with client data doesn't lock notes, and vice versa!

Client info and notes are entered from separate forms. When editing client info, the notes are displayed read-only. When entering notes, the rest of client info is read-only.

Btw., this setup allows you to enter as many separate notes about client, as you ever need!

[Steveoh421]

Am I right, that employee(s) working with Client info, and technicians inserting notes about client are different people - and may work at same time on same record?

Create a separate table for notes:
tblNotes: NoteID, ClientID, NoteDate, Note

Be sure, that working with client data doesn't lock notes, and vice versa!

Client info and notes are entered from separate forms. When editing client info, the notes are displayed read-only. When entering notes, the rest of client info is read-only.

Btw., this setup allows you to enter as many separate notes about client, as you ever need!

Hey! And thanks for the quick reply!

I really just wanted to setup a system where we could at least have the front end file open for us to enter info or collect info at any given time, because there are only 4 of us it's easy enough to shout over when one of us are entering data into the Notes section. You're on the right lines though, it's sometimes the case that more than one person is accessing the file at one time.

I'll give that fix a go. At this point we've been using the database for a couple of months so would I be right in thinking that if I create a separate table for notes that I'd lose any input in the current column where we enter notes? This is a small setback but certainly no huge problem if we end up with a database that doesn't keep prompting the guys with warning messages.

[Steveoh421]

Sorry, I should also reply to your first query more directly; yes, the employees and technicians are the same people i.e. the 4 of us in the office have different job titles so I've probably confused the query a bit with some my my terminology. Apologies for that!

[ArviLaanemets]

You create a table for notes. Then you import (using an INSERT INTO query) all previous notes into new table. After that you delete the Notes column from Clients table (make an archive copy from clients table before).

About having the form open all the time. I myself use mostly a design, where on unbound main form is a tab control with several pages. On every page is some basic form, (which can have his own tab control) with subform(s). So you can work p.e. on Clients form, and another employee can enter at same time some note about same client on Notes form (a possible solution - on Notes page is unbound combo to select client, and an continuous form to enter notes for selected client - so the client is not locked in Clients table).

[Steveoh421]

Hi Arvil,

I'm looking to follow on from the response you kindly added a few months ago.

I may have an issue in that I don't have a great deal of knowledge with the coding aspect. When creating the new table for notes, do I use the crosstab query? If so, it doesn't seem to display the one column that I need in the 'Available Fields' (this is the 'long text' column that we have titled "Customer History Notes"). All other columns are displayed, but not the client history notes. I've added a screengrab to show what I mean.

[isladogs]

Long text (memo) fields cannot be used in crosstab queries

[ArviLaanemets]

Use simple INSERT query. Something like

INSERT INTO tblNotes (ClientID, NoteDate, Note)
SELECT ClientID, Iif(IsDate(Left([Customer History Notes],8)),CDate(Left([Customer History Notes],8)),SomeOtherDateField), [Customer History Notes]
FROM [Main Client Database]
WHERE [Customer History Notes] Is Not Null

You end with some crap in tblNotes, like Note = '#Error', which you have to remove manually, and those date strings, '**'s, etc. at start of notes (you can remove dates using SUBSTITUTE() in 3rd query field, but as between dates and notes an unidentified number of various strings is used like ' - ', or ' ', or whatever users did come up with, there is no way to extract only note part on full automation).

There is also the question about your Customer History Notes table structure. It looks more like some Projects table. I would have a separate table for customer information (CustomerID, CustomerName, bank details, etc.), and in your current table only CustomerID.

[Steveoh421]

Thanks again, Arvil. I'll follow this route and make an effort to understand more about the coding aspects.

We basically enter every single piece of info on the client in the table titled "Main Client Database". The main form we use is the "Client File" which takes the main info we need from the aforementioned table. We use the reports to print of filtered info which relates to projects that are managed by the employees at our company, to be honest we haven't had any use for the queries at this point.

I refreshed this query as one of our employees told me that all her customer history notes disappeared which I understand will be the issue of that entry becoming corrupt (this was highlighted to me earlier on in the thread).

With reference to your observation, the "Customer History Notes" is just one of the columns in the main database table. I'll hopefully get some success with the query you've very kindly offered further advice on