Shared Front End

[Nymandus]

First, let me say I am relatively new to Access 6 months self-taught and 2 active databases and I am a quality analyst not a computer person.

Second, I just found the thread saying shared front ends are not a good idea. but for now I have users following a link to 2 separate FE files (one for each location, as we are in different cities on opposite sides of the country)

Third, I had my first instance of an error and this is where my questions begin...

My database is tracking call evaluations for a call center. I am using the Primary key to number the call evaluations with it set to incremental and indexed (no duplicates)

The database worked well for a month and a half. Friday we had a problem where one user’s evaluations overwrote everyone else's that were processed the same day. (After reading the shared FE thread is see this as the possible reason)

1) Do you know is the shared FE the reason?

2) I wanted the shared FE for ease of updating the FE as I am not the computer tech and don’t have access to each person's computer. My next idea...instead of emailing updates on a regular basis to each person, would it work for me to have a separate FE folder for each person (15 max) on the servers (one for each location) and have separate links sent to each person?
My thought would be their link will never change and I can update on the fly when I see that person is not in the FE.

I ran the compact and repair on both FE files as well as the BE and it seems to be working again but....fool me once....

Any help with both questions would be greatly appreciated.

 

[Pat Hartman]

The error you experienced is not typical of a shared FE. It is more likely user error or perhaps a programming error.

There are tools on the market that you can use to distribute FE's to users. There are also methods that don't require any coding. For example, you can create a .bat file that the users click on (rather than a shortcut to the FE). The .bat file copies a new FE down from the server.

Until you buy or develop a distribution mechanism, using separate copies of the FE on the server is a better solution than having all users share a single version. Keep in mind that having the FE local on the user's hard drive is more efficient because Access doesn't have to keep downloading it from the server each time the user opens it.

 

[Nymandus]

Not being a programmer I freely admit it is possible I made an error... is there any way I can find that error and fix it?
what seems to be, right now, is that one user is at the root of the problems but she should not have any greater access than the rest of us, including me, (4 of my evaluations were over-written by her). She is using the same front end as the rest of us. The form we are using does not have alot of options to make mistakes with as a user.

what is the maximum size a table can get to?
is there a maximum BE or FE file size?

 

[Pat Hartman]

You'll have to do some testing. Make sure you do your testing on a test BE, not on the production BE. Log on as the user who overlaid the data and see if you can do it while logged in as her.

Access has a help entry that gives you all the "specifications" for sizes. Databases are limited to 2G. See this article - http://office.microsoft.com/en-us/access-help/access-2010-specifications-HA010341462.aspx

 

[Nymandus]

I will do some testing... I hope... I do not have seperate security access set up for each person. we all use the same Password to access the FE. shoudl that make a difference?

So my overwritting issue should not have anything to do with the first person or the last person to open the FE file?

thank you for the link I will go there now.

 

[Nymandus]

ok so size does not appear to be an issue as the total size of the BE file right now is 1.59 MB....

 

[Pat Hartman]

I don't believe this is an Access issue. Whenever I have experienced it, it was always my error or a user error. But user errors are ultimately my error also since I didn't protect against it.

The most common reason for this is having forms opened to unfiltered recordsets. The user mindlessly just starts typing thinking he is adding a new record but in fact, he is typing over an existing record. If the users are primarily doing data entry, then have the form open in add mode so it opens to a blank record. Then have a separate menu option to open the form in edit mode. Even then, most of my forms open blank. The user must enter selection criteria and press the search button. This makes them conscious of what they are doing so they don't blindly start typing. It is also more efficient since users really can't make use of thousands of records so there is no point in giving them forms bound directly to tables anyway. Once you change your forms to be more client/server compliant, then the move to SQL Server is easy if you ever have to make it.

 

[Nymandus]

We just had the issue again... I think I may have had a problem in my save button it had extra syntax in there... but that beign said I also looked at the Data field on the form
filter on load is set at no---should this be set to yes (is there anything else that would need to change with that?)
record locks is set to no locks-- should this be changed to "edited records" or "all records" ?

 

[way2bord]

It's also possible the front end has tables on it. Shared temp tables due to shared FE. Bad idea.

I recommend dividing the database into 3.

Your data backend. (located on network) - store data here.
Your development frontend. (doesn't matter where it's located) - make all changes here
Your local copy frontend * no. of users. (This is located on every PC) - local users copy a version of the development frontend to their local harddrive.

 

[Nymandus]

If I do decide to push a copy out to each person's desktop how do I ensure they do not have a "personal copy" of the database? In the past we have had issues with Access databases being copied to the desktop and from that point on it was a stand alone copy with their own set of evaluation numbers?


anyone know about the form portion of the questions and setting the data a filtered and record locking in the data properties? will that help or cause problems?

 

[Pat Hartman]

All the FE's should link to the same BE on the server so the FE's don't become isolated. Always store the version prominently on the menu so the users have an easy way to see if they are working with the current version.

To solve the problem, you need to go with one of the distribution tools. They work by having a table in the BE with version data and a table in the FE with version data. On open, the app compares the local version table with the linked (BE) version data. If they are different, it automatically downloads a new version. That's how they stay in sync. Any other method does leave possibilities for old FE's floating around.

 

[way2bord]

If I do decide to push a copy out to each person's desktop how do I ensure they do not have a "personal copy" of the database? In the past we have had issues with Access databases being copied to the desktop and from that point on it was a stand alone copy with their own set of evaluation numbers?


anyone know about the form portion of the questions and setting the data a filtered and record locking in the data properties? will that help or cause problems?

As mentioned - all front end databases (forms + queries + linked tables only - no tables) should point to a single back end database (tables only) housing all data.


A poor man's fix for version control:

Change the name of the Backend every time you want to force users to download a new frontend.

Because all data in a frontend is linked tables (pulling data from backend), changing the name of your backend will break all links and will crash the front-end if the new version of the database is not downloaded.