October 20, 2011 at 4:56 am
Hi Guys,
This question si about Front End design when using against a SQL Server Database.
I am looking for advice on the way forward with a MS Access Front End I have. There is really only 3 tables, Clients, Staff and WorkedHours on a sql server 2008 database Back End
In terms of table sizes there would be approx 600 records in the staff table and 2000 in the Client table. Worked Hours will grow to maybe 50,000 over time.
We want to record how many hours each member of staff works with each Client.
What I need to decide is the user navigation trough the database and this will dictate what queries to run.
My thoughts are as follows;
1) When database opens - launch ADO code to open connection to SQL Server.
2) Give user choice to View/Add/Edit Clients Details, View/Add/Edit Staff Details, View/Add/Edit WorkedHours (ie three buttons on the welcome page and a fourth to exit Application).
3) On Choosing any one of the above do I then retrieve all the records for Clients or staff and let the user add/edit at will or fetch a read only subset of records FName Surname DOB and let the user chooses which record to view/amend?
4) Update amended/added record
5) Exit application close ADO link
This would mean I could keep the number of forms to a minimum otherwise would I have to create a view form, an amend form and an add form or just change the record set and make it dynamic?
This would mean you would one form with three possible states?
In the past when I was using MS Access as both FE and BE with linked tables I just let the users see all the records at once, but from reading books and forums on SQL Server this seems to be frowned upon !
I am interested in everyone's opinion on this subject as there are many way to skin a cat
You advice is greatly appreciated
October 20, 2011 at 5:46 am
For SQL server and access 600 records is insignificant.
That being said you have staff that needs to quickly find the correct one.
Same for employees and worked hours.
I think you need at least to make a search form for the clients.
I would make the 1 form for each object type, not try to merge all inserts into 1 form. That's just asking for trouble.
The final interface should be fairly simple. I have something similar to this for myself and I only have 1 screen & 1 report (after the employees & clients have been put in the DB). I have 2 more forms for those objects but I never need to use them.
October 20, 2011 at 5:10 pm
Hi SSCoach
Can you explain a little more, excuse my.igborance but what do you by objrct do you mean recordset type? or table?
Kind regards
😀
October 21, 2011 at 4:44 am
Looks like you are really green at this.
The best thing you can do right now is buy a book or find online tutorials and start playing with it. There's no shortcut for you unless you hire this out.
P.S. In Access 2007 there are template DBs and there's one for time tracking so you might start with that. I already comes with everything you need afaik.
October 26, 2011 at 5:56 pm
Yes green and wet behind the ears!
But maybe i didn't explain it right i am familiar with the object orientated programming paradigm, i understand the principles behind encapsulation too.
I understand SQL and can design tables upto and beyond boyce codd normal form.
But I am trying to desgn a front end using ado and vba to access stored procedures and update a recordset on a sqlserver backend.
So please cut me some slack.
Its really the best front end interface design i am looking to create and bypass the learning curve via this forum.
regards to all.
October 27, 2011 at 5:58 am
I am cutting you all the slack there is.
50 000 foot review.
1 - Play with it to learn
2 - Pay someone to learn or do it for you.
To start the easiest way possible I'd build the table sql server side then use the wizards to build the GUI. There should be nothing you can't do with those basics tools in the needs you listed.
Come back when you have a more specific problem/task that you can't solve.
October 27, 2011 at 12:13 pm
Tallboy (10/20/2011)
In the past when I was using MS Access as both FE and BE with linked tables I just let the users see all the records at once, but from reading books and forums on SQL Server this seems to be frowned upon !
Don't worry too much about that. The form can be based on a recordset with a filter so instead of grabbing all the records you can grab a subset. That method is still using a bound form which is easier to develop. You can also go the disconnected recordset route, but it will take more of a learning curve. But hey, try both and see which one you like better. 🙂
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
October 27, 2011 at 5:06 pm
Yeah, I wouldn't over think it. Use linked tables and bound forms for data entry.
For reporting you could look at executing stored procedures.
I would also consider DSN less connections.
>>but from reading books and forums on SQL Server this seems to be frowned upon
Well I gues, if you need enterprise level quality for a mission critical app then sure. You'll have to decide what the expectation of your users is.
With the amount of data you mention I would just rip through it with bound forms, optimize when possible and then evaluate. The learning curve is a lot higher with other methods. If you are not going to use the ease of development feature of Access then I would skip it and build a .net app.
October 31, 2011 at 7:59 am
Hi,
Good advice was reappraising the size of tables and I think one table could have 500,000 records after 5 years.
But still think the free version of Sql server will do the job now.
Getting on good with ADO but need to do a lot more testing but looking good.
I dont want to link tables as donw won the network guy's accusing me of dragging large tables over the WAN.
Thanks again for all the help and direction.
October 31, 2011 at 8:38 am
The current free version allows you to hold 10 GB of data. So for 500 000 records you'll be more than fine.
In 5 years? Might be even more since there should be another 2 releases in that period.
November 1, 2011 at 8:42 am
Oh a WAN, then linked tables and bound forms is not the way to go. Shouldn't be a problem on a solid LAN but a WAN changes the whole conversation.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply