Hi Guys,
This question is about Front End design when using Access against a SQL Server Database.
I am looking for advice on the way forward with a MS Access Front . 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
This question is about Front End design when using Access against a SQL Server Database.
I am looking for advice on the way forward with a MS Access Front . 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