stevbe
asked on
Microsoft Access 2003 Runtime Unbound forms architecture
Microsoft Access 2003 Runtime
Unbound forms architecture
Hello all,
I have a new project coming up which is basically a weekly timesheet entry for about 150 people and administration functions for about 5 people. The business process, as has been explained to me, is that on Friday mornings they want EVERYONE to enter their time for the week. While I am very familiar with Access, I typically work with bound forms which clearly will not work due to the number of concurrent connections that would be necessary. Looks like it is time for me to delve into the world of unbound forms. Access is the only data store available at this time, please do not post suggestions to move to another RDBMS.
I can think of a couple of ways to handle this:
1. Binding forms to disconnected ADO recordsets
My concerns here are that, while it will still work in 2007, ADO is being depricated as a data access technology for Access.
2. Connecting, populating local lookup tables (projects, tasks, etc), diconnecting, capturing data entry in local temp tables, connecting and committing raw SQL or manipulating recordsets (DAO or ADO)
I am leaning more towards something along the second option but am open to someone telling me that the first will be much easier and that because ADO will work in 2003/ 2007 it will be a very long time before I need to worry about this.
As for using the second method, well, I simply don't have much experience and would appreciate the basic methodology people working with unbound form structure thier apps.
TIA,
Steve
Unbound forms architecture
Hello all,
I have a new project coming up which is basically a weekly timesheet entry for about 150 people and administration functions for about 5 people. The business process, as has been explained to me, is that on Friday mornings they want EVERYONE to enter their time for the week. While I am very familiar with Access, I typically work with bound forms which clearly will not work due to the number of concurrent connections that would be necessary. Looks like it is time for me to delve into the world of unbound forms. Access is the only data store available at this time, please do not post suggestions to move to another RDBMS.
I can think of a couple of ways to handle this:
1. Binding forms to disconnected ADO recordsets
My concerns here are that, while it will still work in 2007, ADO is being depricated as a data access technology for Access.
2. Connecting, populating local lookup tables (projects, tasks, etc), diconnecting, capturing data entry in local temp tables, connecting and committing raw SQL or manipulating recordsets (DAO or ADO)
I am leaning more towards something along the second option but am open to someone telling me that the first will be much easier and that because ADO will work in 2003/ 2007 it will be a very long time before I need to worry about this.
As for using the second method, well, I simply don't have much experience and would appreciate the basic methodology people working with unbound form structure thier apps.
TIA,
Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I wouldn't personally aim for 2 versions. 'Cos I know in my heart that sooner or later I would need functionality from both at the same time. And wouldn't the admins also be be doing timesheets?
My thought would be controlled access to the administration menu.
Of course there are so many questions and this is only scratching the surface. For both stability and security reasons I might not create permanently linked tables for the timesheet entry segement. Append queries can post into a different file, after all. I guess the admins would need links so create them and destroy them in code for that segment.
(Every developer is going to have a bound/unbound preference so the next response might be recommending the unbound route. I tend to feel that if you are an 'unbound' person then wouldn't you be better off with VB where there is much more scope for a totally customised approach - but then I regard programming as a necessary evil involved in producing Access apps, so I'm just bias, bias, bias(:-) )
My thought would be controlled access to the administration menu.
Of course there are so many questions and this is only scratching the surface. For both stability and security reasons I might not create permanently linked tables for the timesheet entry segement. Append queries can post into a different file, after all. I guess the admins would need links so create them and destroy them in code for that segment.
(Every developer is going to have a bound/unbound preference so the next response might be recommending the unbound route. I tend to feel that if you are an 'unbound' person then wouldn't you be better off with VB where there is much more scope for a totally customised approach - but then I regard programming as a necessary evil involved in producing Access apps, so I'm just bias, bias, bias(:-) )
>>"I regard programming as a necessary evil involved in producing Access apps"
<faints>
<faints>
<shakes head and wakes up after fainting - thanks for that Pete :-p>
Stupid new site that doesn't list names :-(
Sorry Steve - I hadn't even realized this was a question of yours.
Ahhhh <rubs hands together>
Waffle time!! :-)
I believe I can speak as an open mind.
I like unbound forms - and am willing to use one as very soon as I determine it could be beneficial to - but I too use bound forms predominently. (And am happy to do so).
With this being Jet data I believe that there's nothing wrong with having linked tables here.
They do no harm just being there (the users naturally have no access to the db window to wildly click - so you'll be controlling when they are accessed).
I do agree that, with this many users, you will benefit from local initial processing (OK all processing is local with Jet, but you know what I mean :-)
You don't want writes to the server each time the user begins a new row.
And then changes their mind - pops back up one row - changes the hours the did yesterday - pops backk down.
Meanwhile the data file winces.
So yes - a local copy of your entry table.
I'd perhaps go with that scenario even in client server mode (above an ADO recordset) - but just perform the update a little differently. Largely because if they are interupted from entering their sheets - the data they've entered so far will still be there (held locally) for them to come back to and finish off. They're not at the mercy of a code drop-out or crash.
I wouldn't shy away from ADO because of (potential) trends.
MS expected DAO to dissappear - it didn't and I fully expect ADO to do the same. Both are too well established now and I think MS knows that.
The lookup tables, well it depends on size somewhat (how many records). I remember in a previous question, Steve, we discussed the possibility of you having a separate mdb with lookup values in it - and you could always copy that locally when you required. As mentioned already the alternative is to just fill local copies of the lookup tables.
Even though, for the most part, reading data isn't what's going to hurt your application, we do want to minimize the total of what that one MDB file is having to provide.
Then I'd probably just use those linked tables to execute the Append query to the live table (which is crunch time).
Hopefully that could be pretty clean though.
It's a straight forward append. About as good as it gets in database terms.
(If edits are required then that's a different matter - but would likely only affect the way you initially fill your local data).
I too wouldn't worry about separate apps.
Not unless this has a very limited lifespan.
Stupid new site that doesn't list names :-(
Sorry Steve - I hadn't even realized this was a question of yours.
Ahhhh <rubs hands together>
Waffle time!! :-)
I believe I can speak as an open mind.
I like unbound forms - and am willing to use one as very soon as I determine it could be beneficial to - but I too use bound forms predominently. (And am happy to do so).
With this being Jet data I believe that there's nothing wrong with having linked tables here.
They do no harm just being there (the users naturally have no access to the db window to wildly click - so you'll be controlling when they are accessed).
I do agree that, with this many users, you will benefit from local initial processing (OK all processing is local with Jet, but you know what I mean :-)
You don't want writes to the server each time the user begins a new row.
And then changes their mind - pops back up one row - changes the hours the did yesterday - pops backk down.
Meanwhile the data file winces.
So yes - a local copy of your entry table.
I'd perhaps go with that scenario even in client server mode (above an ADO recordset) - but just perform the update a little differently. Largely because if they are interupted from entering their sheets - the data they've entered so far will still be there (held locally) for them to come back to and finish off. They're not at the mercy of a code drop-out or crash.
I wouldn't shy away from ADO because of (potential) trends.
MS expected DAO to dissappear - it didn't and I fully expect ADO to do the same. Both are too well established now and I think MS knows that.
The lookup tables, well it depends on size somewhat (how many records). I remember in a previous question, Steve, we discussed the possibility of you having a separate mdb with lookup values in it - and you could always copy that locally when you required. As mentioned already the alternative is to just fill local copies of the lookup tables.
Even though, for the most part, reading data isn't what's going to hurt your application, we do want to minimize the total of what that one MDB file is having to provide.
Then I'd probably just use those linked tables to execute the Append query to the live table (which is crunch time).
Hopefully that could be pretty clean though.
It's a straight forward append. About as good as it gets in database terms.
(If edits are required then that's a different matter - but would likely only affect the way you initially fill your local data).
I too wouldn't worry about separate apps.
Not unless this has a very limited lifespan.
ASKER
Looks like I *accepted* too quickly ... but I will impose on the good nature of my fellow experts anyway :-)
1. Pull data from a couple of tables local for lookup IDs (copy from linked tables)
3. Local temp table that basically is a timeline for them to select what Week they want to add/ edit. (copy from linked tables)
3. If editing existing data then pull the data to be edited (copy from linked tables) into a local table and pump that back up to the server when they are done.
4. If adding then let them whack away at the local table only to be pushed to the server when they are done.
5. The push back to the server ... could either be in-line SQL, or paramaterized queries. I think the key here is to keep track if I need an INSERT or an UPDATE. Any value in doing a DELTE and then an INSERT instead of just an UPDATE?
Sounds pretty simple :-)
<Not unless this has a very limited lifespan.>
you are so funny ... the first version of this app was to last 90 days ... it has been 180. After paying a consultant good money, including buying lots of licenses for M$ Project, they had no idea of what was really going on cause it didn't do what they wanted it to (don't ask me about the requirements or statement of work .. please!) so they called me in to take a look. After I worked out what *could* be done and wrote custom views for them, they had the meeting with M$ and the consultant wh o then fessed up that the *known* issues in 2003 definately stop us from what our end goal was and that 2007 will not completely fit the bill either. So this is where we are today ... "hey Steve" :-)
Steve
1. Pull data from a couple of tables local for lookup IDs (copy from linked tables)
3. Local temp table that basically is a timeline for them to select what Week they want to add/ edit. (copy from linked tables)
3. If editing existing data then pull the data to be edited (copy from linked tables) into a local table and pump that back up to the server when they are done.
4. If adding then let them whack away at the local table only to be pushed to the server when they are done.
5. The push back to the server ... could either be in-line SQL, or paramaterized queries. I think the key here is to keep track if I need an INSERT or an UPDATE. Any value in doing a DELTE and then an INSERT instead of just an UPDATE?
Sounds pretty simple :-)
<Not unless this has a very limited lifespan.>
you are so funny ... the first version of this app was to last 90 days ... it has been 180. After paying a consultant good money, including buying lots of licenses for M$ Project, they had no idea of what was really going on cause it didn't do what they wanted it to (don't ask me about the requirements or statement of work .. please!) so they called me in to take a look. After I worked out what *could* be done and wrote custom views for them, they had the meeting with M$ and the consultant wh o then fessed up that the *known* issues in 2003 definately stop us from what our end goal was and that 2007 will not completely fit the bill either. So this is where we are today ... "hey Steve" :-)
Steve
>>"but I will impose on the good nature of my fellow experts anyway "
Impose away - I'm invested now!! And by the end of your post too!
(And I only noticed this question was yours because you accepted it.
When I got the notification I thought
"Huh? A Question from Steve - when have I commented in a question from Steve?" lol)
Anyway.
1. OK
2. Multiple records though yes? How many roughly?
3. So there could be editing. How do you select which records? Date based - previous week?
4. Indeed
5. I'd go for parameterized queries. You want the engine to be as happy and efficient as possible. So that saved execution plan is worth having.
As for a Delete then Insert I'd wondered about that. But as Jet performs updates with a delete and insert behind the scenes, I'm not sure what we'd actually gain unless of course they were separated by several minutes in a "checking out" and "checking in" type fashion where the records are deleted as they're read into the local table and then appended later on. If they were done immediately consecutively then we'd need a transaction wrapped around them which would only slow things down and harm concurrency even further.
I'm for either a single Update or the *delayed* Delete/Insert.
Now - on to office politics. :-)
First of all - this consultant was an expert in...? Access? Databases? MS Project? Software integration?
But the Project project failed - and you were only called in then? Why weren't you made use of before?
(They rated this consultant above you?? Tisk!)
Or was he a MS Project only consultant and that's what they thought they wanted it in?
Impose away - I'm invested now!! And by the end of your post too!
(And I only noticed this question was yours because you accepted it.
When I got the notification I thought
"Huh? A Question from Steve - when have I commented in a question from Steve?" lol)
Anyway.
1. OK
2. Multiple records though yes? How many roughly?
3. So there could be editing. How do you select which records? Date based - previous week?
4. Indeed
5. I'd go for parameterized queries. You want the engine to be as happy and efficient as possible. So that saved execution plan is worth having.
As for a Delete then Insert I'd wondered about that. But as Jet performs updates with a delete and insert behind the scenes, I'm not sure what we'd actually gain unless of course they were separated by several minutes in a "checking out" and "checking in" type fashion where the records are deleted as they're read into the local table and then appended later on. If they were done immediately consecutively then we'd need a transaction wrapped around them which would only slow things down and harm concurrency even further.
I'm for either a single Update or the *delayed* Delete/Insert.
Now - on to office politics. :-)
First of all - this consultant was an expert in...? Access? Databases? MS Project? Software integration?
But the Project project failed - and you were only called in then? Why weren't you made use of before?
(They rated this consultant above you?? Tisk!)
Or was he a MS Project only consultant and that's what they thought they wanted it in?
ASKER
2. Very few records, I will created records for the *unclosed* weeks (typically only 1 month) and the current week, so it looks like about 5 - 6 records as they only want data captured for the week as a whole instead of daily.
3. I would let them pick a date period from the records in item 2 (thanks for correcting my double 3s).
5. I like parameterized queries myself (only use inline SQL when I absolutely have to) and will probably go this route.
I agree that the delete / append would have to be wrapped in a transaction and was kind of kicking that around, thinking that all *saved* changes would call the same code ... with only a check to see if the ID field was populated (not for new records but would be for exisiting records). I guess I can use the same test to see if I should call the UPDATE or the APPEND.
office politics
He was an MS Project consultant only, they wanted someone with experience in Project, Project Server, Project Web Access (PWA) and of course the SQL Cube that can be generated :-) I tell you, I was unimpressed, not only by the dismal results but his inability to answer questions during our *training* sessions, and when it can time to building views (using the built-in user interface) he was useless. If you ever have to deal with views/ cube in Project, let me know, did lots of *observational* research, views are barely covered in the few books, articles and websites available for Project.
Yup ... I was never involved in this project until it just before it crashed (I may have helped it out the door) so as far as office politics go, I look good ... again (arrogant SOB:-)... I am better than the consultant in a topic I new nothing about before today. My boss's boss is ticked off at this point (VPs emailing VPs this past weekend). This *other* group has consistantly tried to use my skills as a developer to get them out of hot water and for power user kind of stuff they should be handing. They are always trying to push their development projects up the schedule (they are good at this part of the politics but are really bad at requirements). Fortunately I am fairly well insulated, my boss and VP have a good understanding of the situation, in fact whenever this *other* group now asks me for anything I have to tell them to email my boss :-)
Steve
3. I would let them pick a date period from the records in item 2 (thanks for correcting my double 3s).
5. I like parameterized queries myself (only use inline SQL when I absolutely have to) and will probably go this route.
I agree that the delete / append would have to be wrapped in a transaction and was kind of kicking that around, thinking that all *saved* changes would call the same code ... with only a check to see if the ID field was populated (not for new records but would be for exisiting records). I guess I can use the same test to see if I should call the UPDATE or the APPEND.
office politics
He was an MS Project consultant only, they wanted someone with experience in Project, Project Server, Project Web Access (PWA) and of course the SQL Cube that can be generated :-) I tell you, I was unimpressed, not only by the dismal results but his inability to answer questions during our *training* sessions, and when it can time to building views (using the built-in user interface) he was useless. If you ever have to deal with views/ cube in Project, let me know, did lots of *observational* research, views are barely covered in the few books, articles and websites available for Project.
Yup ... I was never involved in this project until it just before it crashed (I may have helped it out the door) so as far as office politics go, I look good ... again (arrogant SOB:-)... I am better than the consultant in a topic I new nothing about before today. My boss's boss is ticked off at this point (VPs emailing VPs this past weekend). This *other* group has consistantly tried to use my skills as a developer to get them out of hot water and for power user kind of stuff they should be handing. They are always trying to push their development projects up the schedule (they are good at this part of the politics but are really bad at requirements). Fortunately I am fairly well insulated, my boss and VP have a good understanding of the situation, in fact whenever this *other* group now asks me for anything I have to tell them to email my boss :-)
Steve
ASKER
Now the second cup of coffee is kicking in ... while this may be percieved as question scope grope ... so would you create 2 versions ... one for time entry and one for administrators. I was thinking the number of admins is so small and the whole experience needs to be much richer that I would maske a "bound" version.
Steve