Migrating away from MS Access - Suggestions Please

I currently run Access 2003 on the server and the client pc The databases are all accessed from the server from the other computers. I have 3 employees that use them.  I'm entirely self-taught so can pick up new technologies to migrate these apps.

My app has grown from 1 to 3 databases over 10 years it is fragmented and disorganized. I want to get away from Access.  What are my choices?   I'm working out of my apartment so I can't afford big bucks
brothertruffle880Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
SQL Server Express would seem to be the next logical choice.  It's free, allows databases up to 10GB.  Here's a starting point: http://www.microsoft.com/web/platform/database.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
you can use the FREE SQL Express

see this SQL Express
0
Jeffrey CoachmanMIS LiasonCommented:
First, tell us the reason for wanting to migrate, ...many people "hear" that Access is not full featured, ...but never explore it to its potential.
(For example, ...just because you app runs slow, ...may not be a reason to migrate)
;-)

1. Are you quite sure you cannot integrate all 3 apps into one? (Linked tables, ...etc)
If this app can remain on the desktop, ...then this might be a good option.

2,. You can also migrate the backend to MySQL or SQL Server and keep the front end in MS Access.

3. Migrating to a Access "web" app is an entirely different animal....
There is no easy way to migrate an existing Access db to a web app.
(In effect),A redesign from the ground up is required.

4. Migrating Access to a .net front end (and a SQL Server Back end), might be even more daunting, ...as the .net programming language is far more complex than VBA, and is a lot less forgiving...

I am sure other Experts will be able to add more....

JeffCoachman
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I want to get away from Access.  >>

 What issues are you trying to address?

 Really for a single do it all tool, the features that it offers, and the price, there's not a lot out there.

 There's always File Maker Pro and Alpha 5, but you'll be pretty much right back to where you are with Access when your done.

Jim.
0
rindiCommented:
LibreOffice Base is OpenSource and free, similar to access but still different. As database backend on the server you could use MySQL or Postgresql.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Note that Access contains a database engine (JET or ACE, depending on the version), a Form builder, Report Builder, Query Builder, etc etc. Products like SQL Server (any edition) would be used to replace the database engine part, but you'd still have to pick something to replace those other components.

Others have mentioned FileMaker, etc, and that's what you'd have to use to completely replace Access - something that had all the "components" of Access, and not just a database engine. If you have the skillsets to work with .NET, then .NET + SQL Server Express would be a complete replacement for Access, but of course you'd have to do a full rewrite of the Forms, Reports, etc. You could migrate the database to SQL Server using the SQL Server Migration Assistant for Access.

But as Jim said - what's wrong with the current Access app that makes you want to migrate?
0
Gustav BrockCIOCommented:
>  it is fragmented and disorganized.

That's where to start. Get a subscription to Office365 that includes the Access license, and upgrade your app(s) to Access 2016 to be organized and not fragmented. This requires nothing more than your current skills.

Eventually, use SQL Server Express as your backend database, but it is not mandatory for three users if you run off a decent file server.

/gustav
0
David AndersTechnician Commented:
A thirty day demo of Filemaker with the 14 templates would give you an idea of it's features.
http://www.filemaker.com/products/demos/
Filemaker is currently offering a 2 for 1 promotion. Offer expires December 18
0
Eric ShermanAccountant/DeveloperCommented:
<<<<My app has grown from 1 to 3 databases over 10 years >>>

I would first evaluate why you have 3 databases as opposed to using just one db with multiple tables to contain all of your data.  You can probably re-design your application using one Access db on the server then link to your 3 user pc's.  I db size is the issue then migrate your data to MySQL or SQL server to the database then design your desktop GUI in Access to link up to your server database tables.  Either way  ... based on your opening post ... Access sounds like the best tool for you going down the road especially if you want to design/maintain it.

ET
0
PatHartmanCommented:
If money is an issue, I doubt that Filemaker will work for you.  Plus your client needs to buy it as well as a server.  The O365 subscription is excellent.  For $99 per year, you can get 5 copies of the full office suite that you can install you your computers or those in your household.  This version is not intended for business use so it wouldn't work for your client but it would work for you and is about as low a cost of entry as possible once you get beyond free.  

You seem to be blaming Access for the current disorder in your three applications.  I would look some other place to assign blame.

Start with an analysis of the three applications and decide where their touch points are.  
1. Do they share tables (or if they don't, should they)?
2. Are they all used to fulfill the same business process causing users to jump from one to another?
3. You don't have sufficient users to warrant a move to SQL Server but do you have sufficient data?  I have Jet/ACE databases with millions of rows that operate just fine.  SQL Server Express is free but simply converting Jet to SQL Server is more likely to make the application slower than faster.  The conversion needs to happen based on some understanding of client/server apps and will require at least some minimum query/form changes if you built traditional old-style Access apps.

Just those first two questions should get you thinking about a new structure.  Perhaps the BE's should be combined to make data usage more efficient and prevent dual data entry but the three FE's could remain separate to allow for better security and task separation.
1
brothertruffle880Author Commented:
My current database system is fragmented and disorganized.  I put it together piecemeal over the years.

What are the advantages using SQL Server vs SQL Server Express vs MYSQL

Or should I ask that in a separate EE question.  (I get scolded by members if I include too many questions on an EE post, OR, I get scolded if I close a question prematurely and ask a related question in new post.)

So, please let me know if I should close this question first and ask the above question in another post.
0
Dave BaldwinFixer of ProblemsCommented:
Regular SQL Server is not free.  SQL Server Express and MYSQL are free and are probably adequate for your program.  They are both SQL databases so most queries would be the same but some syntax is slightly different.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
MySQL is more equivalent to the full version of SQL Server, as their feature sets are pretty much identical. The big difference, as Dave pointed out, is that MySQL is free.

SQL Server Express is the "little brother" of full SQL Server, and is limited in some ways (database size, number of cores, etc). In most cases, those limitations don't make any difference at all, and SSE is more than capable of hosting your data.

But as others have said - determine why you think you need to move from Access. In many cases, the problem is not the environment, but rather the program.
0
rindiCommented:
Actually only the Community edition of MySQL is still free. But that would probably still be good enough for your needs.

MariaDB is a drop in replacement for MySQL, and that is free:

https://mariadb.org/
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.