?
Solved

few questions on front ends with access

Posted on 2009-05-01
26
Medium Priority
?
834 Views
Last Modified: 2013-11-29
only way to solve problems im having is to multiply my front end by number of users.  working on that note...  
will having only one backend be a problem?

also, can a front end detect its being used and instead open the next front end?

lastly, i had problems with using the same front end with 2 users on the same form.  i think it was because both forms were on the same record.  will that be the case with 2 front ends having problems adding records to the same table?
0
Comment
Question by:sumhungl0
  • 10
  • 6
  • 5
  • +2
26 Comments
 

Author Comment

by:sumhungl0
ID: 24282891
quittin time, be back in the morning.  have a good day!
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24282915
Q: will having only one backend be a problem?
A: Not if you have a limited number of users (1-20). I have a backend with 25-50 simultaneous users and it works fine with an occasional corruption problem.

Q. also, can a front end detect its being used and instead open the next front end?
A. Each user should have her/his own front end on their computer.

Q. lastly, i had problems with using the same front end with 2 users on the same form.  i think it was because both forms were on the same record.  will that be the case with 2 front ends having problems adding records to the same table?
A. Each user should have her/his own front end on their computer. Two front ends will only have a problem if both are trying to change the same record at the same time.

Please let me know if you have any other questions.
0
 
LVL 13

Expert Comment

by:adraughn
ID: 24283532
I agree with cwood answers for Q2 & 3.

<Q: will having only one backend be a problem?>
No, it is not a problem - but to elaborate, you should not have "two" backends, regardless of the number of users. Your data should all be stored in one database so that everyone is viewing and updating the same information.

The only reason that number of users should play in, is if you have 50+ users. Then you may want to look into a SQL backend purely for stability. There are multiple reasons that developers move their backends to SQL:

1. Stability - SQL has a scheduled backup and is not affected by any access corruption issues that may occur.
2. Performance - Note that performance would not improve unless you made some significant changes in your front end. This includes but is not limited to moving your queries to SQL as views and stored procedures.
3. Security - Data can be easily secured on the server based on user roles and groups.
4. Growth - Qhile access can handle a lot of data, when you start working with millions of records, it is much more stable in SQL. (Will also eventually cause performance issues when trying to query millions of records with JET).
5. Improvements - If your data is stored in SQL and you would like to upgrade your application, you can write the front end in VB (or other language) and change your reports to SQL Server Reporting Services (or Crystal - yuck).

Note that the move to SQL is a major step that would be a direction you may go in if you are having issues with one or more of the topics I have listed above. The important thing to remember is that a small database (sounds like yours is) works just fine in Access with ONE shared backend on a shared drive and local front ends (one on each users desktop).

-a
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24283890
Hello sumhungl0,

I generally agree with the Experts above, just 2 additional points...

1) Every user should have his/her own copy of the FE, as above.  In a fast LAN environment it is usually OK
to have the FE placed on each user's machine.  Otherwise, you may be better off placing the various FEs
on a server, and having the users "remote" into the server to use the application

2) It is sometimes advantageous to have >1 back end, BUT A GIVEN TABLE ONLY RESIDES IN 1 BACK END.
For example, if the back end data store is expected to get too big for Access to handle, you can have some
tables in backend 1, and the other tables in backend 2.  A single FE file can link to any number of BE mdb files

Regards,

Patrick
0
 
LVL 85
ID: 24284162
<also, can a front end detect its being used and instead open the next front end?>

I'm not sure what you mean by this, but it seems that you're still trying to circumvent the process of deploying a separate FE to each user. If that's the case, just say NO <g> and concentrate your efforts on making the FE deployment happen. You'll never get reliable, stable, satisfactory results using other methods. Access has been around for a long while, and the legions of developers who have built multi-user apps will all tell you - give EACH user their own copy of the FE, and be done with it. There are utilities out there to help with deployments and such, but overall the premise is pretty straight forward.

IOW - deploy a copy of the FE to each workstation that needs it, and be done with it.
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24284454
LSMConsulting,

Amen, brother :)

Regards,

Patrick
0
 
LVL 85
ID: 24285725
I speakth the TRUTH!
0
 

Author Comment

by:sumhungl0
ID: 24285942
all good information thanks.  i get the point, but im not allowed to place the front end on users machines.  i have a folder on the server that i can use.  i am sectioning this thing down as much as possable to get to the point of almost all have a different fe.  but its really not possable in this case.  
i will make a folder for each shop i have with the company, bigger shops will get an extra copy for supervisors.  
being able to make it idiot proof is the reason for having a fe detect it is being used.  i can tell these people all day that its one user at a time per fe, but past experience shows people forget, dont listen, dont care, and so on.  
i already have the script in place to mark the usersInfo table, loggedIn field.  so i can tell we have a person logged in.  does anybody think it is possable to open a different FE if current one is in use?  other then that, i dont know of another way to section this off, and split it enough to have a copy for each person.  maybe i could just have the FE warn the second user that somebody else is on it.  because people in the same shop will probably be in the same office area anyway and could tell the other to hurry up er somethin.  
thank you all for the input.
0
 
LVL 13

Expert Comment

by:adraughn
ID: 24305230
I agree with LSM and MP.

<im not allowed to place the front end on users machines>
not sure I understand the reasoning behind that. FE on local machine is standard for any access application. If you create a seperate front end for everyone on the server and try to keep them from using the same one, it is going to be a nightmare.

I suggest you plead your case with whomever told you not to put a local copy on the machines. It will save you a lot of time in the long run.

good luck.

-a
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24305422
All good points. If you really, really, really must not place the FE on the user's machine, then you can put the FE on the server and give them a shortcut to it. If you are not going to have more than 10-30 users (maybe up to 50 depending on structure), multiple users can access the database with little or no problems. Of course two (or more) of them cannot edit the same record at the same time but that should rarely happen.

With multiple users you should have your database set to record level locking (Access 2007 => Office button => Access Options => Advanced (left margin) => Advanced section (bottom) => Default record locking => select Edited record).
0
 
LVL 85
ID: 24305474
FE on a local machine is standard for ANY non-web application. Can you run Work or Excel from a network shortcut? No, you cannot, and with good reason (the network traffic alone would make this untenable).

There is no way for a FE to know if it's in use, since in order for code to run the FE MUST be in use (by the current user). You might be able to work with your "login" table, but that's a bandaid at best. There is really no way for one FE to know if another is in use, except by iterating open Windows via API calls (and that's difficult to do at best, and not very efficient or reliable at worst). You can check the Lockfile of the Backend to see if THAT is in use, but this really wouldn't be much help.

So:

Your IT department is officially 100% brain-dead, and has apparently "drank the Koolaid" of web-based applications. I have no problem with web-based apps, but they are not the end-all, be-all that IT depts seem to believe they are, and as much as they hate it, desktop apps rule in office environments, and it'll be that way for quite some time. IT guys LOVE web-based deployments, since it means they can sit back in their cubicle and play Halo and update their Facebook page all day long <g>, but in the real world that's just not a viable, long term solution at this stage of the game. In your specific case, you simply MUST deploy a FE to each workstation - there is no getting around this, and if your IT dept fails to recognize this, then it's time to go above their heads, as adraughn suggests.

And, if you place a network link to the FE, I can GUARANTEE you they'll shut this down pronto - they'll claim "too much network traffic" and you'll be back at square one. Believe me. Been there, done that, and I ain't never going back <g>. Using FEs in this manner means ALL objects - forms, reports, etc - must travel across the wire, and you can bet that, given you IT Dept's obvious lack of love for Access, they'll be monitoring the app heavily.

And please believe us when we tell you this: you can try all the tricks you can find to try and make this work, but you'll have no end of troubles with this sort of setup. Unfortunately, this was something that probably should have been sorted out before you ever even began working on this app, since you could have moved to a web-based langauge if need be ...
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24305585
Brave, LSM. Well said.
0
 
LVL 13

Assisted Solution

by:adraughn
adraughn earned 500 total points
ID: 24305676
agreed.
0
 

Author Comment

by:sumhungl0
ID: 24305950
the issue with FE deployment on local end user machines will not go through, i asked and they will not let it happen, nor will they update the FE if it was on the machines.  i would not be able to do it myself without their permission.  

current configuration....  on server..
20 shop folders, 1 FE in each
1 BE folder
thats the best they will let me do.  but i did come up with a 1 user login code for the FE.  i put a local table in the FE with a field for the user name.  

heres how that works.  
1. the FE starts
2. checks 1 record in the new FE table for a name Or the word "None"
3. If FE table has "None" it goes on with the login code and puts a check mark in the BE userInfo table(this tells me all users on the BE), then writes that users name to the new FE table replacing the "None" entry
4. If FE table has users name it does a msgbox telling them the user that is on, they click ok and the FE quits.
what do you all think?  i just got done testing it on 2 machines at the same time and its working great.  i went through every senario to make sure i didnt miss anything.  but it does keep the FE limited to one user like i wanted.

i dont know much about the record level locking.  can i get an explaination of what happens with that?
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24306003
If you were using more than one FE/user at a time, it locks the record the user is editing and locks only that record.
0
 

Author Comment

by:sumhungl0
ID: 24306032
so that would be in a situation where FE1 being used by user1 and user2.  right?
0
 

Author Comment

by:sumhungl0
ID: 24306037
so that would be in a situation where FE1 being used by user1 and user2.  right?
0
 

Author Comment

by:sumhungl0
ID: 24306046
sry, browser timed out
0
 
LVL 85
ID: 24306072
It would seem to make more sense to put the Login table in the BE, but if you're confident it will work then more power to ya ...

I'd assume that, at some point, the "in use" FE Would have to rewrite the login table with "None" when that user exits the FE. All works okay until (a) somebody leaves the FE open on their machine and walks away or (b) the network drops the connection (which will most likely happen regularly with the FE being run across the network), at which point the FE will NOT rewrite your login table to "None" ... so every other user that tries to log in will be denied access.

And again, this is another of the splints that we often see on this sort of application. It's probably the best you can do in your case, but be prepared, and make SURE that you keep all emails and such regarding your IT Dept's refusal to correctly deploy the application, along with any emails you may have from higher-ups regarding your suggestions as to a proper implementation of the app and the IT Depts refusal to do so. You may need those at some point.
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 24306109
<so that would be in a situation where FE1 being used by user1 and user2.  right?>

No, this would be where User1 and User2 are connected to the SAME BE ... your data resides in the BE, so recordlocks would be generated by the BE. Generally speaking, so long as users aren't editing the same record, you generally don't have any trouble. The more users you have, the more prevalent/possible recordlock issues become, but at 20 users you'll probably be okay with this.

Of course, recordlocks depend quite a bit on how well architected your db is ... if you've made intelligent use of AutoNumbers (if used), correctly related your tables, and made sure to implement proper multiuser techniques, generally recordlocking issues are few and far between.
0
 

Author Comment

by:sumhungl0
ID: 24306189
you know what i didnt think about the senerio where the network goes down, might be a problem.  i think i could just replace the FE when the network is back up and should be ok.  

and yes the FE replaces the user name when the main form is closed.
0
 

Author Comment

by:sumhungl0
ID: 24306443
do i set the record level locking on the BE or FE?

right now both are set to:
default record locking = no locks
open databases using record level locking = checked
default open mode = shared  <--- if that has anything to do with it
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24307079
You set the record level locking on the BE but it won't hurt to do it on both. Another way to meet your requirements might be to set the default open mode to Exclusive. If a database is opened Exclusive, no one else can open the database.
0
 

Author Comment

by:sumhungl0
ID: 24308540
does Exclusive lock out other users from the FE?  if i did that it would not run the code to see if another user is on, i think...
0
 
LVL 16

Assisted Solution

by:Chuck Wood
Chuck Wood earned 500 total points
ID: 24308884
Yes it does. Right, it would not allow them to open the FE.
0
 

Author Closing Comment

by:sumhungl0
ID: 31577059
thanks alot!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question