November 23, 2016 at 2:40 am
I need to give a client some options for creating a front end interface for a sql backend. What they currently have is a series of lame old access databases that are well overdue an overhaul. It's a fairly small undertaking. We're looking at maybe 10 tables, 3000 rows of data in each, limited forms, some basic reports, 5 write users, 20 read users - all of whom are not techy & need something simple.
As the 'database' person, I'm all they've got at the mo.
The options I see so far.
1. A new access front end - slightly lame, problems with concurrency? Does access just lock the whole mdb / mde while someone is writing to it?
2. Sharepoint 2013 lists - Great for concurrency, not sure if this can produce reports? not sure if the volume of data here is problematic in terms of performance. Having had a quick look, sharepoint seems to be pretty irritating and slow. Other opinions gratefully received.
Or ...
What are my other options for creating a user friendly front end interface to my sql database? I know bits of html / css / powershell - happy to pick up whatever I need.
Thank you.
November 23, 2016 at 6:58 am
snomadj (11/23/2016)
I need to give a client some options for creating a front end interface for a sql backend. What they currently have is a series of lame old access databases that are well overdue an overhaul. It's a fairly small undertaking. We're looking at maybe 10 tables, 3000 rows of data in each, limited forms, some basic reports, 5 write users, 20 read users - all of whom are not techy & need something simple.As the 'database' person, I'm all they've got at the mo.
The options I see so far.
1. A new access front end - slightly lame, problems with concurrency? Does access just lock the whole mdb / mde while someone is writing to it?
2. Sharepoint 2013 lists - Great for concurrency, not sure if this can produce reports? not sure if the volume of data here is problematic in terms of performance. Having had a quick look, sharepoint seems to be pretty irritating and slow. Other opinions gratefully received.
Or ...
What are my other options for creating a user friendly front end interface to my sql database? I know bits of html / css / powershell - happy to pick up whatever I need.
Thank you.
There are just entirely too many options here. Pick any language you are comfortable with and get going. As you said, the application is not all that complex or large. My first thought would be to do this in asp.net but that is because I am extremely comfortable with that language. As it sits right now this is just entirely too broad for an online forum.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 23, 2016 at 8:51 am
snomadj (11/23/2016)
I need to give a client some options for creating a front end interface for a sql backend. What they currently have is a series of lame old access databases that are well overdue an overhaul. It's a fairly small undertaking. We're looking at maybe 10 tables, 3000 rows of data in each, limited forms, some basic reports, 5 write users, 20 read users - all of whom are not techy & need something simple.As the 'database' person, I'm all they've got at the mo.
The options I see so far.
1. A new access front end - slightly lame, problems with concurrency? Does access just lock the whole mdb / mde while someone is writing to it?
2. Sharepoint 2013 lists - Great for concurrency, not sure if this can produce reports? not sure if the volume of data here is problematic in terms of performance. Having had a quick look, sharepoint seems to be pretty irritating and slow. Other opinions gratefully received.
Or ...
What are my other options for creating a user friendly front end interface to my sql database? I know bits of html / css / powershell - happy to pick up whatever I need.
Thank you.
Have you considered migrating the data up to SQL Server? Microsoft makes a rather excellent tool for doing so, named SSMA (SQL Server Migration Assistant). Last time I looked, there was a unique version for nearly every combination of SQL Server version and MS Access version, although that was a number of years ago. It can migrate the data up to a SQL Server instance, and automatically change the existing tables into Linked tables, and the number of other changes to your Access database are minimal. The benefit is that you no longer have to worry much about corrupted data, as there's no more of your data in the .mdb file. You can give every user their own copy of the Access database, and you can refresh everyone easily enough (email a zip file, for example). Then all you need to worry about is setting up the access rights inside of SQL Server. Not a difficult thing to do.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 23, 2016 at 9:19 am
snomadj (11/23/2016)
I need to give a client some options for creating a front end interface for a sql backend. What they currently have is a series of lame old access databases that are well overdue an overhaul. It's a fairly small undertaking. We're looking at maybe 10 tables, 3000 rows of data in each, limited forms, some basic reports, 5 write users, 20 read users - all of whom are not techy & need something simple.As the 'database' person, I'm all they've got at the mo.
...
Since you said they are a client, I think the biggest concern is what is the client comfortable with maintaining? 20 users, 5 write users, isn't too big for doing an Access front end to SQL Server back end, there are some suggested best practices though, such as using Pass Through queries and other means of trying to do most of the processing on the database side instead of having Access bring all the data down to the client:
https://technet.microsoft.com/en-us/library/bb188204(v=sql.90).aspx
November 24, 2016 at 7:49 pm
"10 tables, 3000 rows" is not the scale which could possibly justify choice of such an expensive (in all meanings) tool as SQL Server.
It's not even enough for Access.
An Excel Workbook with 10 sheets will do just right.
🙂
But seriously - any free DBMS will be OK for a project of such scale.
Just make sure the design of the database is not too horrible.
_____________
Code for TallyGenerator
November 25, 2016 at 8:09 am
Because you already use MS Access as a sort of front-end, I wouldn't change it. Just upgrade to the latest version and fix some compatibiliy issues (if any). The number and the size of the tables imply you can use SQL Express as the database back-end (it's free of charge). Use linked tables in MS Access to connect to the SQL Server back-end. The SQL Express back-end will give you the ability to build and use stored procedures (amongst other benefits) and provide better locking mechanism so the end users won't interfere with each other.
November 25, 2016 at 8:59 pm
The SQL Express back-end will give you the ability to build and use stored procedures (amongst other benefits) and provide better locking mechanism so the end users won't interfere with each other.
Yep, what MR Hanshi said!
November 26, 2016 at 2:42 am
On balance, yes, excel would do it but my users want something more "formy" that can generate (basic) reports and do things in a way they are used to. I'd like to get stuck in and learn app programming but this is me wanting a challenge and not actually what the users need. They need something sustainable after I've gone so I'll go updated access +/- linked tables, depending.
Thanks for your time & views & helping me look at it from all angles.
Much obliged.
November 26, 2016 at 1:00 pm
Your specs right now are pretty small. However, if you expect the data or usage of the application to grow significantly, I'd recommend going with SQL Server (express is fine) and putting in the work to write an ASP.NET application like Sean suggested. I'd also have the application call stored procedures to do the work. It's been my experience that applications tend to expand their user base if they're good. Data also tends to grow over time. Five years down the road, you might even not recognize the original specs. Putting in the work now will enable you to more simply connect the application to SQL Server Standard Edition if it grows to be too large for Express.
Don't get me wrong, you don't want to recommend a $20K for a $100 scenario - just keep future growth in mind.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply