Solved

Alternative to Access

Posted on 2014-01-30
13
752 Views
Last Modified: 2014-01-30
Experts,

IT states the will build a database for projects.
They say it will be as good as Access or even better.  
I don't believe this.  
I dont know a whole lot about databases other than what Access can do.

1.

What software is IT likely to use for this? (we use Oracle)
Can IT take my access db and convert it to i think "SQL"
If they were to do this then how is the db used in SQL?  I mean what is the frontend (not sure if that is proper terminology)?  
What other db allows the same functionality as Access?  (making queries on fly, etc etc)

Thank you
0
Comment
Question by:pdvsa
  • 4
  • 3
  • 2
  • +2
13 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
Comment Utility
Access is a RAD (Rapid Application Development) environment.   It does many things for you that in other products/tools, you'd have to do from scratch.  For example, bound forms.  In Access, something that might take minutes could take hours in something else.

But Access does have some serious limitations when compared to something like developing in C#.   Some of those are:

1. Can't build a standalone EXE, so it's very sensitive to its runtime enviroment.

2. Can't do a full n-tier design.

3. Is limited in the types of 3rd party controls and type libs that can be used with it.

 So can they build a "better" application than they can with Access?  Certainly.   But it will also take them a lot longer.

 Also, many don't realize that you can use Access simply as a "front end" with a backend like Oracle and end up with a fairly robust application.

  Access gets a bad reputation from the multitude of people that "develop" and app and really don't have a clue what their doing, then turn around and blame it on the product because it doesn't work well.

Jim.
0
 
LVL 7

Assisted Solution

by:Ned Ramsay
Ned Ramsay earned 125 total points
Comment Utility
Access is a very simple, but not too great database system. Its designed for home-office or small business requirements where there is limited IT knowledge.

Access can also run as a front end system that queries against a different database type e.g. SQL, PostGreSQL etc.

The truth is there are 10-15 commonly used database types, queries can be done on all of them although the process may change or be more difficult for the end user.

Why not ask IT the same questions you asked here and get their responses?
0
 
LVL 15

Assisted Solution

by:Ess Kay
Ess Kay earned 125 total points
Comment Utility
access is a small self contained database

The limit of records are 50,000

It is also restricted to using its own language, and cannot be easily run on anything other than windows PC


The point of access is to keep very small records, so if your operation is small and not going anywhere, and you dont want to spend any money, access is for you

If your business is evergrowing, however, moving the data to a normal database like Microsoft SQL Server is a better idea.

An sql Database, unlike MS Access will ONLY hold and / or manipulate data.

Access provides the user interface which is built in, but very limited.

The programmers will have to build a separate user ineterface, which simply connects to the database and displays everything you need.

For a windows application, typically .Net will be used to facilitate the Graphics. It will in turn connect to any database (including access), but as the data grows, the speed gets slower, so you are likely to experience slower browsing through records in Access.

SQL Server manages large data much more effeciently and thus give you an advantage in employee effeciency.



If you want to move the data yourself, heres a how to:
https://www.youtube.com/watch?v=q2eBNHTj86M


for more about pros and cons and uses, read this: http://www.quackit.com/microsoft_access/tutorial/microsoft_access_versus_sql_server.cfm



if you want the real comparison you can grasp it is here:
Microsoft Access is to MSSQLServer
as to Microsoft Paint is to Adobe Photoshop
as to Pee Wee Football as to Superbowl



They get the job done, but if you dont want the bare minimun, you can heed the programmers.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
Comment Utility
I have to disagree with the the previous two comments

1.  Its designed for home-office or small business requirements where there is limited IT knowledge.  Access is a robust RAD environment which allows developer to rapidly develop applications that can be used in the home, but Access applications written by qualified developers are constantly being written for companies of all sizes, including Fortune 500 companies.
 
2.  access is a small self contained database. It can be, but also has the ability to be the application layer for an application built on a more robust SQL Server or other RDBMS.

3.  The limit of records are 50,000.  I have numerous Access databases with well over a million records.  Only limit is a file size of 2GB per data file, but that can be overcome by splitting the data tables into separate back-end data files.

4.  Access provides the user interface which is built in, but very limited.  The built-in user interface and Rapid Application Development environment provided by Access mean that I can develop in hours what it would take most .Net developers several days or longer develop.

Access has one of the most flexible report writing interfaces on the market.  

Access is a great tool that can be easily scaled for developing enterprise level applications.

SQL Server is a great backend-database, but it is only a database, you must have the front-end application for any database to be truly useful, and Access is a great tool capable of interfacing with almost any RDBMS.
0
 

Author Comment

by:pdvsa
Comment Utility
Ok I see.  
Our operation is small.  Only 4-5 users tops.  It would take a long time before we would reach 50k records.  

Wouldnt SQL server be overkill?  

Jim:
<So can they build a "better" application than they can with Access?  Certainly.   But it will also take them a lot longer.

Yes, in our meeting this is what I told them.  Also, rework, edits etc will take a lot of time too and I am sure they would put it off as they have more impt tasks.  

The issue I am facing is the so called "supervisor' is completely intimidated by me and wants to kill anything that is not their idea.  This db is one item on the kill list.  


IT can simply save the access db as an SQL db and use the Access front end to access the SQL backedn?  Probably not saying this right but hopefully you can read between the lines.

thank you
0
 

Author Comment

by:pdvsa
Comment Utility
fyed, thank you.  I posted before seeing your comment.  Anything to add please feel free...
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:pdvsa
Comment Utility
maybe a step by step procedure for using what I have in access to using in SQL.  From what I gather it seems only a backend needs to be in SQL. I guess all the VBA code is unaffected by using an SQL backend.
0
 
LVL 7

Expert Comment

by:Ned Ramsay
Comment Utility
I agree with some of the things you are saying, except that it has a maximum concurrent users of 255.

Which boils down to the original question... speak to your IT team and see what they want to do and more importantly why they want to do it. Is it necessary? Is it an access 2000 DB that needs updating to newer versions?

Are they trying to make a web based platform instead and move away from the GUI or loading individual PCs with the front end?

If you are 4 to 5 users then I don't see there being an issue. Best of luck.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<IT can simply save the access db as an SQL db and use the Access front end to access the SQL backedn?  Probably not saying this right but hopefully you can read between the lines. >>

  More or less yes.   You can download the SQL Server Migration Assitant to move the data from JET (the database engine you get when you use Access out of the box) to SQL Server.

 For most applications, this works pretty well.  There may be some minor changes required and for most, performance will be better.

 You can stop there, or modify the app to take full advantage of SQL server (by using views, passthrough queries, stored procedures, and triggers).   With that, you can get lots of performance and usability.

 I have one client right now which has one table with over 30 million rows and there are no issues.

@nedramsay

<<I agree with some of the things you are saying, except that it has a maximum concurrent users of 255. >>

  It should be pointed out that this is with JET.   If your using SQL Server as a backend, and each user has a copy of the front end, then there is no user limit.

Jim.
0
 
LVL 7

Expert Comment

by:Ned Ramsay
Comment Utility
How do I "like a post" :) ^^
0
 
LVL 15

Expert Comment

by:Ess Kay
Comment Utility
I build access databases for 50 or so concurrent users, it typically works fine.

One example is CUNY HR information. It handles all classes, hours, employees salary, and calculations.

There were some limitations, and graphics are limited, but it overall does its job.


if you only have few employees, and work is not huge workload, Access is most likely the best way to go.

If you ever develop into a 5000 workorders per day, you might need to reevaluate your system. till then, find developers who can work with access.


as far as the 50k record limit, they might have extended it now, but it used to be the table limit, not for entire database
0
 

Author Comment

by:pdvsa
Comment Utility
thank you for the comments.  
I will split the points uless any objections and I think I have to choose a best comment.  I will take the default, which I think is the first comment.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now