sumhungl0
asked on
few questions on front ends with access
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?
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?
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.
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.
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
<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
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
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
<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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I speakth the TRUTH!
ASKER
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.
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.
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
<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
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).
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).
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 ...
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 ...
Brave, LSM. Well said.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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.
ASKER
so that would be in a situation where FE1 being used by user1 and user2. right?
ASKER
so that would be in a situation where FE1 being used by user1 and user2. right?
ASKER
sry, browser timed out
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
and yes the FE replaces the user name when the main form is closed.
ASKER
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
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
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.
ASKER
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...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks alot!
ASKER