November 10, 2011 at 2:08 am
Hi Guys,
I am in the middle of developing a desktop app on sql server 2008 with access 2003 FE at the moment and would appreciate you advise on a few issue.
In every normalised database you have supporting tables that help keep the large tables ref integrity(there's about 10 of them, you know the type tblGender, tblTitle, tblArea, etc et..).
Firstly
What is the best way to keep the small tables updated? I was thinking rather than copying them all from the sql server database into Access everytime the FE starts up I keep a copy in an access table in the FE? does that make sense? So instead of downloading them all every time and converting into access I have local copies and if they change I just send out a new FE ( as I using Windows Authenticity and a DSNless connection so no usernames/passwords requried in the FE)
Secondly I have six user groups and I want to restrict the view of client data, so they see only their own clients, I don't know whether to store a variable in each FE that identifies the usergroup and pass it to sql server to restrict their client view or write 6 different views for each user group ! Its a real dilemma.
Thirdly when passing data back a variable to sql server to get a record set should I use a query of a table, a query of a view or pass as a parameter to a store procedure?
Fourthly and finally when retrieiveing data for editing ie a clients record I return 1 record at a time to minimise network load, but what is the best record set mode to allow me to edit and save back the changes easily?
Sorry for the long question they could easily be 4 different topics, but I would appreciate everyone opinion on these thorny subjects !
November 10, 2011 at 5:35 am
Lookup access data project.
The data always sits on sql server. Access serves solely as front end. No need to import or sync data all the time.
#2 if you use views as datasource you can do that filtering there.
#3 You can use views here. Access wizards can build reports and forms based on them. So coding would be minimal.
#4 Just use the defaults. Works really well to let access handle all that in the forms.
November 10, 2011 at 5:47 am
Firstly
What is the best way to keep the small tables updated? I was thinking rather than copying them all from the sql server database into Access everytime the FE starts up I keep a copy in an access table in the FE? does that make sense? So instead of downloading them all every time and converting into access I have local copies and if they change I just send out a new FE ( as I using Windows Authenticity and a DSNless connection so no usernames/passwords requried in the FE)
It's OK but duplication of data in two DBs is not recommended. SYNCing these DBs could be a big issue in future. Also, your connection to DB is unsecure.
Secondly I have six user groups and I want to restrict the view of client data, so they see only their own clients, I don't know whether to store a variable in each FE that identifies the usergroup and pass it to sql server to restrict their client view or write 6 different views for each user group ! Its a real dilemma.
Views, Schema & Role based permissions should solve it.
Thirdly when passing data back a variable to sql server to get a record set should I use a query of a table, a query of a view or pass as a parameter to a store procedure?
SPs would be good option.
Fourthly and finally when retrieiveing data for editing ie a clients record I return 1 record at a time to minimise network load, but what is the best record set mode to allow me to edit and save back the changes easily?
If supported by Access FE TOP 20 or TOP 50.
Now I have few questions for you.
• What’s the justification for selecting SQL Server when you have Access DB with you at the first place?
• Why Access FE? I don’t think it has rich FE features. Also it follows classic VB based programming (not sure about latest versions).
• (Just to make sure I understood the Client – Server Architecture correctly) If I say “you are going to create N Access DBs (with FE forms) to connect 1 SQL Server as data store”, will it be correct?
November 10, 2011 at 5:51 am
Access is a decent front end epecially for quick developement. That beind said if you plan to become the next google, then maybe you need to rethink your strategy.
November 10, 2011 at 6:35 am
That beind said if you plan to become the next google, then maybe you need to rethink your strategy.
???
November 10, 2011 at 6:41 am
Dev @ +91 973 913 6683 (11/10/2011)
That beind said if you plan to become the next google, then maybe you need to rethink your strategy.
???
It works well for smaller shops with maybe 50-200 employees. But I wouldn't re-do a full ERP in there. Not that it's impossible.
Depends of the needs of the cie(s) and why you build that app!
November 11, 2011 at 8:25 am
Dev (11/10/2011)
That beind said if you plan to become the next google, then maybe you need to rethink your strategy.
???
I can see this. If Google forced everyone to have Office installed and open an Access app to search the web, I can't imagine their shares would be much over $400 right now.
Now, if Google Maps was built using Word and VBA macros -- that might be a winner.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply