• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 973
  • Last Modified:

SQL 2005 Database Front End

Presently, I have several Microsoft Access databases on a network drive that is used by roughly 30 office staff.  The front-end forms have all been carefully designed and use no macros...all visual basic coding.

We have just completed the construction of our Intranet (which is on an SQL 2005 server) and utilizes Active Directory log-in controls and we have people which monitor the server traffic 24 hours a day to ensure there are no security issues...so there is little concern about someone using the intranet to hack our servers.

What we now want to do is move the Access tables to the SQL server AND recreate the Access Front-End forms on the intranet (obviously in web pages).

I have gotten many many different answers on the easiest way to go about creating the web front-end but nothing definitive yet.  At present, the user MUST be able to query information from the tables and be able to do data entry into the tables WITHOUT the user accessing the table data directly (do not want to display the table on the screen preferably).

So my question is this...what is the best way to go about designing a user web front-end for the database?  Should it be in a particular language (as opposed to another)?  Are there already very good programs which have been designed for this purpose?
  • 8
  • 6
  • 3
5 Solutions
I can only give you the benefit of my experience rather than X is better than Y.
I created our Intranet (front end and back end).
I used SQL 2005 and ASP.NET (vb.net)

To isolate the tables, use stored procedures and give the users execute permissions on them.
Then you can use gridviews on the web page to allow the users to view/modify/delete data in the underlying tables. Again deletes, inserts and updates can (and should) be done using stored procedures. No direct table access, cleaner coding as database code is in the database and not in the pages and also helps to cut down on sql injection attacks.

If you don't want to show data in a grid format you use detailsviews and/or datareaders.
Hope this helps.

Oh... program I used was Visual Studio 2005. You can get vb.net or C# express for free from MS.
Very good environment for coding front ends based on back end data.

I don't know of any product that will create the front end based on an existing front end in a different format but then you probably wouldn't want to do this and you would want full control over content and features and not want to spend time later on redoing pages that didn't quite fit/work
BanyanTechAuthor Commented:
Moving the tables to the SQL server is no problem - the problem is the creating of the web pages - I have just had too many people recommend too many things.

I have been told that the web pages can be done in HTML, Javascript, Visual Basic, PHP, ASP and SQL.

I have been told I can Visual Studio or Expressions 2.0 and even use DB Convert to convert the Access forms into web pages.

And this is the problem...which is the proper way to go?

Everyone I have spoken to seems to recommend what they know most but none of the recommendations are based upon the ability to actually having more than just one option....SQL programmers recommend SQL while ASP programmers say ASP and so on.

I don't have the time to find out which way is the best way to go through trial and error...I am just hoping there is someone on here who would be able to say which way is the "most right" based upon actual experience.

My employer wants the web pages to look the same way as they presently look on the Access front-end...nicely designed and laid out forms...as the people doing the data entry are not what we would consider to be technically savy.
well it all depends on what you are experienced in and what tools you have to hand.
SQL is a database langauge (t-sql when dealing with sql server) so that doesn't enter into it.
If you want to hand code html and javascript then you are in for a lot of work.
ASP is pretty ancient now and requires a lot of hand coding.
I don't use PHP so won't comment.

What level of programming skills do you have?
What software do you have?
What operating system will the Intranet be hosted on?

Most people will advise based on what they know.
As a SQL/ASP.NET developer that's what I did.
80% of the code on our Intranet was produced by me dragging grids etc onto a page and tweaking the code to suit. Hours and hours of coding done in minutes.

As I keep referring to... it all comes down to your current skills and experience.
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

I should add that what you are doing is miles different from where you were.
Access forms and tables within a mdb with some vba behind is a completely different platform from where you are going. You will need to focus on and know the differences between the front and back ends. Or the data/business and presentation/application layers as they are known.

Write stored procedures for data access and data manipulation

Web pages:
Create pages to present the data and give the users the platform to edit/add to the data
BanyanTechAuthor Commented:
What level of programming skills do you have?
HTML, Javascript, VB and slightly above basic SQL - but can learn whatever language is needed at have over 7 years programming relational databases (although all in access with either access also being the backend or an SQL server being the backend).

What software do you have?
If you mean software for designing like Visual Studio, none, but purchasing power is not an issue if it saves time.

What operating system will the Intranet be hosted on?
The intranet is actually a packaged program called Adenin IntelliEnterprise which makes use of webpages inside of portlets - I'll find out the exact OS of the SQL server is that's what you need.
"Adenin IntelliEnterprise "

No idea what that is.... so you have a templated solution to build on?
With your background and knowledge you shouldn't have too much trouble adapting to which ever road you choose. You will need a server side language/technology under your belt though. html and javascript (in the main) are client side.

If I were starting from scratch I'd get a copy of visual studio (trial or purchase) and have a play at dragging controls onto a page and using the wizards to point to your back end. Then tweak if necessary.
That said none of this may be applicable with the Adenin IntelliEnterprise you speak of.
BanyanTechAuthor Commented:
The OS on the SQL 2005 Server is Windows 2003 Standard R2 64-bit.

And Adenin IntelliEnterprise is simply a canned intranet package with some built-in functionality....but comes with no real database functionality....it mostly makes controlling user permissions and permission groups a little easier.  The database web pages would be displayed inside of this environment as web pages.
I meant the OS that the web pages reside on... windows, linux, other?
This will help determine what tech you can use.
If you want to have a look at some samples for asp.net and the gridview check this out
BanyanTechAuthor Commented:
Sorry about that - clearly I am not clear on some of these things...so...

We have an SQL 2005 server onto which Windows 2003 OS has been installed.

We also have an intranet frame-work program called IntelliEnterpirse.  The database web pages would reside inside of this program but only so far as the pages would open inside of it.  The program does not have the ability to design these types of pages...only display them through the use of a portlet.

Currently I am waiting to view the link you left above as the site is presently down for maintenance.

I really appreciate your help thus far as at least I am now moving in an actual direction.  Would there be any chances of you having a link to this type of project planning documentation as well?

From a project point of view (non-tech)
At work, we set up a team to develop the intranet. Although I was the tech side of things it was important to get staff buy in. We tackled questions such as how to make it appealing to staff, how to encourage (make!) staff use it.

We ran an in-house fun competition to come up with a name for the intranet to give it a personality. Winner to receive a bottle of wine. We had a fine for any team member using the word "intranet" at meetings. We nutted out the areas/sections for the intranet and what docs would be hosted. Once this was done we thought about what fun areas to include. It's a hard balance between time wasting apps and a  bit of light relief on the intranet. We have a chat room but aimed more toward announcements rather than chat. Competitions. Photo gallery from social club events. And of course all the dull policies and docs that are required.
Anthony PerkinsCommented:
>>My employer wants the web pages to look the same way as they presently look on the Access front-end...<<
Just a word of caution and perhaps lower some expectations: This is not going to happen.
BanyanTechAuthor Commented:
Anthony PerkinsCommented:
There is no way that you can design a web page with the same interface as Access.  Further, even if you could, this would be a lousy idea and defeat the whole point of using a website in the first place.

This was alluded to previously (#23647546).  I was simply trying to highlight something important that you appeared to be overlooking.
Anthony PerkinsCommented:
If you are serious about this plan, I would suggest you hire a contractor to advise you fully.  You can then decide whether his/her approach fits your requirements and if it does contract them to do the job.
BanyanTechAuthor Commented:
I think what would be really helpful is if I could actually see some front-end database web-pages....see what each is able to do and look like...and see if that functionality fits into what we are looking for.

The problem I have with hiring someone again is that each expert often recommends a different design language.
front end databases are just web pages... If I'm reading you correctly?
A web page is processed on the server based on data in the database and the resulting page is delivered to the client as html.

Server side processing is where data is retrieved and formatted to requirements.
eBay, amazon, experts exchange. All examples of "data driven" web sites.
in the code is a very plain example.
This ASP.NET page goes to the database and runs the sql query you can see.
It then puts the results into a gridview. Then this page is rendered to HTML (for example the gridview becomes an html table) and is delivered to the user.

This example took me approx 30 seconds to create. Not 1 word of coding.
I only mention this to demo how easy these pages are to create in the right IDE.
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
    <form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
                    SortExpression="ProductName" />
                <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" 
                    SortExpression="UnitsInStock" />
                <asp:BoundField DataField="CategoryName" HeaderText="CategoryName" 
                    SortExpression="CategoryName" />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
            SelectCommand="SELECT [ProductName], [UnitsInStock], [CategoryName] FROM [Alphabetical list of products] WHERE ([UnitsInStock] &gt; @UnitsInStock)">
                <asp:Parameter DefaultValue="100" Name="UnitsInStock" Type="Int16" />

Open in new window


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now