Solved

Open Source Database Recomendation

Posted on 2011-03-22
23
430 Views
Last Modified: 2012-08-14
I manage two MS Access databases that have outgrown the application.

Can anyone recommend a more powerful open source application that

Would be easy for an MS Access user/VBA programmer to use?
Can be managed by a desktop user without requiring IT Dept support?

Are the open source versions of MySql or PostGres too heavy as a next step?

thanks
0
Comment
Question by:Davisron867
  • 7
  • 4
  • 3
  • +8
23 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35195407

You can try Firebird. This is fully featured yet light

http://www.firebirdsql.org/
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 35195455
SQLite is a great single file backend db. Both MySQL and PostgreSQL are full db server installs. If you have a db system (SQL Server, Oracle, MySQL, PostgreSQL, etc.) already set up, you will get better performance and more features. However, it doesn't sound as if that backend infrastructure is currently available and would require significantly more effort for you than SQLite at this point.

Lee
0
 

Author Comment

by:Davisron867
ID: 35195490
Thanks...

My specific problem is that I manage a large precalculated table (a cube i guess) in Access that now has >800k rows and 42 columns.

My users manage the data in Excel Pivot tables, but Pivot Tables load the ENTIRE Access table into memory when accessing the data, so my pivot tables are now > 1.6GB in memory and are crashing peoples machines.

So what I need is an application that delivers data in a way that is more digestible by Excel, for ex, Sql loads just what you ask for in what i think are pages.

Does that sound like a good candidate process for Firebird?

thanks again







0
 

Author Comment

by:Davisron867
ID: 35195572
lee555J5:
Actually, backend infrastructure is not a problem. I can implement just about any solution that'll work. The problem is being certain enough of my choice to then push for IT to actually help me.

I run a bunch of tables every 2 hrs out to a network location from PeopleSoft (Oracle back-end), and then use a VBA routine running off of the crappy Timer Function in Access (cant use Windows Scheduler on my Desktop) to link the tables to Access for processing.

When I suggested Sql Server, they said that it requires regular maintenance a Sql Server management team, and it would probably be overkill given the cost that would be charged to my department. I suppose I need to get an actual cost and make a decision.

BUT, I want full control over the data. It won't help me if I need to go through a QA process every time I want to rename a field.

Then they suggested a virtual machine to get the processing from the updates (every 2 hrs) off of my desktop...and I can use wndows scheduler to kick it off, but the windows server team has had the ticket for 2 months and I feel like I'm going to have to make a major big deal out of it to get some attention. I was thinking with a VM I'd have admin rights and could install and run any application i want.

So, I can ask my dept head to go to the head of IT and ask nicely that this gets done, but I won't ask for that kind of help unless I know exactly what I'm talking about.

Hence the question...

btw, I'm an accountant and Excel guru, who loves VBA and data problems, but not having a formal database or programming background, I'm struggling a bit with this decision.

Thanks again.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35195629
What if Excel loads the data in the exact same way (The entire table) with your DB alternative?
This seems to be an Excel issues, not one with the RDMS

"1.6GB in memory and are crashing peoples machines."
How much memory is installed on their machines?
I'd hate to go through all the trouble of switching the RDBS if all the users needed was one more stick of Ram...
;-)
(The cheapest Dell Inspiron comes with 4GB installed)

JeffCoachman
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 35195699
Mysql or mssql express can both easily be installed without much knowledge on a workstation.
For mssql By default only localhost can connec so it's not even a security risk
0
 

Author Comment

by:Davisron867
ID: 35195798
Thank you all.

Aaron: thanks for the tip. I'll check these out.

Boaq: We all have 4gb Ram. But when I've had to connect to Sybase or SqlServer from Excel, i found they delivered the data in pages. I suppose because they're fast enough to retrieve more data when needed. So, I'm thinking that probably all the more robust RBMS do this.



0
 
LVL 75
ID: 35195961
"MS Access databases that have outgrown the application."
Sorry, I'm not clear on what the actual limitation with Access in your situation?

"Access that now has >800k rows and 42 columns."
But how big is the MDB ?

"I run a bunch of tables every 2 hrs out to a network location "
What does that mean ? 'run' ?

mx
0
 
LVL 3

Expert Comment

by:gskoczylas
ID: 35196824
- Firebird
- PostgreSQL
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 25 total points
ID: 35196991
If you're dealing with Microsoft products, then MS SQL Server Express is definitely the simplest way to go, and the one that will integrate most easily with other Microsoft products. While it's not "open source" it's free and easy to install and manage. You install it on your workstation and it can be shared among other users, or the IT folks can install it on the server and it be shared.

Do you not have a server database already running somewhere? Given that you have an IT team, it's a sure bet that you'll have a server database somewhere, and at that point it's just a matter of adding a new database and moving your data to there.

However, if you have an IT team involved in this, then you're almost certainly out of luck. They'll almost certainly want to install <insert name of cool new softwaree here> because it's what was featured in last month's issue of <insert name of current favorite online magazine here>. After all, most IT people HATE the thought of actually doing any work :)
0
 
LVL 4

Expert Comment

by:rjplus
ID: 35197259
Looking for a database? This handy database feature comparison matrix and glossary let you compare and contrast the supported features of four popular open source databases: Apache Derby 10.1, MySQL 5.0, PostgreSQL 8.1, and One$DB 4.0.  

http://www.devx.com/dbzone/Article/29480

Hope it is of help?
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.

 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 375 total points
ID: 35202030
As @LSMConsulting noted MS SQL Server Express is free.
It is also probably your least painful upgrade option for the backend.
The SQL Server Migration Assistant tool was pretty good already a few years ago -- also free.
It has probably come along since I used it last.

The pain will come if someone's been naughty in naming fields
Date
PO#
Bob's Field
Spaces Are Nice In Fields

Well, you get the idea.

The plus side is, if you have local admin access and can install both SSEE 2008 and SSMA, you can deal with the pain first.
Work out all the issues with field names (if you have any)
Work out any show stopping performance issues.

Then empty the tables, and do mass appending from the production data and roll out new front ends.
With planning and some work, it could be seamless.

And if you ever need, or are wanting, to upgrade to full-blown SQL Server, there will be no pain.
0
 
LVL 5

Assisted Solution

by:simonpaul64
simonpaul64 earned 100 total points
ID: 35202240
"btw, I'm an accountant and Excel guru, who loves VBA and data problems, but not having a formal database or programming background, I'm struggling a bit with this decision."

Strangely enough, I work for an accounting firm and met with our managing partner yesterday and one of the things that he admitted is that accountants think their opinion on everything is the right one - even when they are totally inexperienced in the area that they are discussing. Jeez.

So - Your company runs PeopleSoft. Good thing - that is a multi million dollar ERP system. They run it on an Oracle database. Good thing - arguably the best and most robust dbms there is. Costs thousands a year just to continue licensing it. This combination of systems is used by some of the worlds leading organisations and is capable of managing and allowing efficient reporting on massive amounts of data.  Then you do some processing to pull the data into ACCESS and want to start changing fieldnames??? WHY???

If I were you, I would sit down with the people who actually earn a living doing this stuff and talk about what you're doing and why. Don't go in there guns blazing talking about how they should supply you with a database, server and so on when you know zip about what you are talking about. How would you feel if they come to you and start pointing out your COA is badly designed and your accounting systems are all screwed up??? Trust your colleagues and ask them what they suggest to accomplish what you need to do. You may be surprised, they may not be the morons you take them for. 800k rows is nothing (I was hitting a table with 100 million rows today) and the cube concept is easily supported by PeopleSoft. If it's financials data you probably want to hit that with nVision or if you really need it, some flavour of cube with a BI tool. PeopleSoft and Oracle has them built in.

 
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 375 total points
ID: 35202817
@simonpaul64 makes some good points.  Re-reading your post @ID:35195572, it's apparent you don't have technical issues, you've got turf war issues.
You're an Oracle shop, so those guys are gonna say things like
<it requires regular maintenance a Sql Server management team, and it would probably be overkill>

I suspect they've got you doing things the way you do, because nobody wants to take the chance that the Oracle machines get hammered by an idiotic query.
It doesn't work for you anymore.

Now you've got to make the $ and ¢ case that too much man-time is being wasted.
A Dell PowerEdge T410 goes for $1100.
If you're an MS enterprise customer with a Windows Server team, the server OS is going to be <$600
SQL Server Express edition is free, so are the tools.

Where will the end data be stored?
Won't matter, because IT has that problem already

How will the data get on the new server?
Won't matter, the Oracle guys already have to make it spit out, or have pulled the same data already.
It's just going to a new destination

What if you 'end-users' bust your new toy?
IT can image it before it goes into production, and final data can be stored elsewhere.

Someone in IT can fix your grief.
And they'll find a better way to do it than we can.

You just got to make the case that it could be done for $2K + some elbow grease in reworking your Access app, AND that more $ is being wasted by the status quo.
Then it'll get done.
0
 

Author Comment

by:Davisron867
ID: 35204677
Oh my GOD!! You Guys are Awesome!! Reading through this thread makes me proud of what EE has built. Thank you for your feedback!

To answer your questions:

DatabaseMX:
Q: "Sorry, I'm not clear on what the actual limitation with Access in your situation?"
A: 1. My limitation is that Excel Pivot Tables (the front end) load the entire Access cube on open causing the Excel files to be 1.6gb in RAM. I want a DB that causes Excel to not do this.
2. I use three databases in a kind of ETL framework. (A) One to link to the data files and run the queries, which output a final table to (B) a second DB, which I then use to (C) completely overwrite my production database because every time someone's pivot table hits the data, I lose Exclusive Access and cant update it. So I just paste the updated version over the old one, and for some reason the users are unaffected and their data connections are maintained.

Q: "Access that now has >800k rows and 42 columns." But how big is the MDB ?
A: If it were all in one Access file it would prob be 1.5Gb (when compacted).

Q: "I run a bunch of tables every 2 hrs out to a network location "
What does that mean ? 'run' ?
A: I run 7 or 8 scheduled, recurring queries in PS Query that output to network folders that I've set up

LSM Consulting: LOL! Have you considered doing standup? I’d like to book you for the next IT event that comes up :)

Nick67: Sound advice. Thanks.

simonpaul64
Actually, I have great respect for my colleagues in IT, and many of them are brilliant.

To answer your heartfelt question, its because we need a dataset that will allow us to scan 15 months of balances across the entire firm; by account, department, or legal entity, and be able to quickly drill-down (in 1 sec) not only to the ledger details, but right down to the subledger details.

There are many reasons why I developed an offline solution, but I basically created an ad-hoc version of Essbase. That means Union Queries of the ledger and subledgers, with multi-use fields depending on the source.

Initially, only one or two people used it to survey account-balances during the close to make sure entries were not missed and to quickly analyse the transactions that made up any balances requiring a closer look. Now there are 20 people on two continents using it.

Nick67 - You and simonpaul have a point that IT should be able reproduce what my application does. But, they just have bigger fish to fry. I've found that people like me develop user-specific solutions in the gaps between the recognition of a departmental need, and the final roll-out of the enterprise solution that "by the way will address your need" from IT, which in some cases can be years.

So, if I tally the votes,
3 for MS Sql Express
2 for Firebird
2 for renewing my vows with IT and seeing what they suggest.
1 for MySql, SqLite and PostGres

Thanks Lee555j5 for the file-based SqlLite.

Thanks to all so much for your suggestions!
0
 
LVL 75
ID: 35204742
"I want a DB that causes Excel to not do this."
Guess I don't get why the DB would be causing this ?

"If it were all in one Access file it would prob be 1.5Gb (when compacted)."
Ok ... but how big is the biggest single db?  And will this continue to grow ?

So, I may have missed this ... but is there any 'front end' involved in this process ? Or is this Access table the 'front end' ... so to speak.

And I would vote for SQL Express also, so that's 4.

For sure ... IT peeps HATE Access.  Our 1500+ IT Dept 'supplies' Access to us via Office (2010 now), but they do not 'support' Access.  That really works for me, cuz I totally have that covered. So, for the most part ... Access is under the radar ... and I can do whatever I want.  

The cool thing IT does do is ... supply our group with a 1Gbit fiber optic connection (Read: WAN) between our location and our shared drive/server.  This is a 60 mile round trip.  Because of this, when the user clicks a 'Submit' button ... I can open a table in a backend, find one record out of 700K, update a fields, close the db, come back and update the local mimic copy of that table (minus all the 'completed records') ... in approx 0.2 seconds!  And I am *not* making this up.

mx

0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 375 total points
ID: 35207892
@DatabaseMX has a point that you should consider more carefully--because you may lose your turf war, or your backend ISN'T the problem.
<"I want a DB that causes Excel to not do this."
Guess I don't get why the DB would be causing this ?>

We haven't asked you what version of Excel is in play here.
Excel 2003 can handle 256 columns and 65K rows.
Is your data cube exceeding Excel's limits and making it barf?
If the problem is the front-end, and you win your back-end turf war, but don't fix the problem you will, as the French say, be dans le merde.
Access 2007/2010 have bigger limits -- but there still are limits.  Are you hitting them?

As @DatabaseMX also point out <IT peeps HATE Access>
This isn't always true--but often enough--and sometimes with good justification.
My engineer fancies himself GOD.  Thinks that P.Eng. designation means he'll excel at anything he puts his hands to.
He's never heard of the ten commandments.
http://mvps.org/access/tencommandments.htm
Never mind obeyed them.
I hate touching his stuff, it's a nightmare -- and a lot of IT people have had that nightmare.

So, get YOUR stuff in order first.
Comment your code.
Document your work flow
Clean up your VBA
Document your queries
Be prepared for someone else to look over your stuff.

Next.
This thing has grown on you over time.  More people use it.  More people work on it.
At one time, the dataset and the workload was small enough that pulling it all into one massive cube was good.

Is that still true?

Can you segment it?
Does Excel HAVE to put everything in, in order to do the work?
You may lose your turf war, in which case altering the way the front end works is all you can do.
Start preparing for that possibility.

Next, you posted your question in the Access zone.
Which means that the comments come from guys who live and breathe Access.
When our back-ends outgrow Access they go to SQL Server Express Edition, and full blown SQL Server.
It's our thing, mostly.
It's like going to the church choir and asking if Jesus is the Son of God.
You should know the answer just by looking at the people you're asking.
So the straw poll here needs to be taken in perspective.
Had you posted your question elsewhere, you'd have gotten a different answer.
You're in an Oracle shop.  Those guys may well look down their nose at SQL Server and its variants.
There'll be some emotion in your turf war.

Finally, <they just have bigger fish to fry>
IT really only has three fish to fry:
1. Pushing productivity to the desktop
2. Limiting risk to the data and the network
3. Controlling costs

Whatever project has the best balance of those three is what's on the go.
To win your turf war, you've got to make the case that your needs rank high enough on those scales to be prioritized.
That means getting some insight into what IT is working on as an ongoing thing, and picking your time well, too.

Good Luck! :)
0
 
LVL 75
ID: 35208224
"This isn't always true--but often enough--and sometimes with good justification."
ok.  But 99.314 % of the time :-)

mx
0
 

Author Comment

by:Davisron867
ID: 35225727
Sorry for the delayed response...

DbMX and Nick67: Thanks for taking the time to share your perspective.

Many of the comments here reminded me that "the right thing to do" would be going through proper channels to let IT solve the problem. So I devoted the better part of a day to that, and (...dreamy music and screen goes wavy)...

I asked A (mid-level) why would the windows team not respond to my ticket for a VM. She said they were short on drive space and were ordering more, but B could expedite it, so I send him an email, then printed it, and walked over and handed it to him.

10min later C from windows team calls. VM's only get 2Gb of disk space and since my estimate is up to 5Gb, I should check with D (a dba) to see if I can share space on an existing box running Sql Server. D said no space available so we'd need to purchase a server (from the price he quoted me it had to be a 9 series PowerEdge).

I bring this to my dept head E, who said I should check with F who runs the budget. F tells me the same thing he told me years ago...you cant have a GL outside of the GL and I need to shut down my database. Except now, I know its been designated as critical by E, so I go back to A fuming that I just want help, so she sends me back to B (who's a stellar guy and very supportive) who tells me of course our division has boxes running apps and I should follow up with him next week.

Here we go round the mulberry bush, So early in the morning

So, your perspectives helped me to focus on "Doing the right thing", which will (i hope) lead to the better solution. So thank you for that, and for the time and energy you invested in this.

to answer some of your questions:
-I'm using Office 2007 with 800k rows of data in Access. Excel Pivot Tables are the Front End.
-turns out that Excel needs to connect through Sql Server Analytical Services to recognize its connecting to a cube and thereby not load the entire source table into memory which I think rules out Sql Server Express.
-I did segment it, but some users still need to see everything.

thanks again.
0
 
LVL 5

Assisted Solution

by:simonpaul64
simonpaul64 earned 100 total points
ID: 35227803
This story reminds me of several companies where I have provided services that had similar problems. Users often have needs that they feel need immediate solutions that don't get solved quick enough by IT. So - they somehow get permission and ability to implement some solution in a tool such as access (Lotus Notes is another culprit). Pretty soon the business has developed all sorts of (often critical to the business) applications and hired a contractor Notes/Access/whatever "expert" that taught himself enough to be dangerous - often creating mini IT departments outside of IT.   Then something goes wrong. Usually some application craps out and because it is not within IT, the app isn't properly backed up, not properly secured, no disaster recovery ability and so on. Or the (now critical) app with 20 users isn't performing right because the app wasn't developed with enough thought of future needs and wasn't developed in a toolset that is capable of supporting the growth of the app. Now you have business users all p'ed off at IT because someone now says why are you bringing your complaint to me when you are desperate for help with a problem you created all by yourself.

I've seen it time and time again. At one memorable client an IT audit of the company revealed OVER 500 Lotus Notes applications that were developed outside of IT - many redundant systems repeated by different sections of the business with shared needs. It needs strong control from IT agreement within the company that IT needs to be managed with planning - not a free for all, who dares wins SNAFU.

To DavisRon - Glad to hear you are trying to do the right thing - in the long run I hope you will find a better solution that is planned and supported correctly.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 375 total points
ID: 35228187
I am, not at odds with @simonpaul64, but in disagreement with him
<It needs strong control from IT agreement within the company that IT needs to be managed with planning>

But as I said

Finally, <they just have bigger fish to fry>
IT really only has three fish to fry:
1. Pushing productivity to the desktop
2. Limiting risk to the data and the network
3. Controlling costs

That is what needs to be kept firmly in mind.
< an IT audit of the company revealed OVER 500 Lotus Notes applications that were developed outside of IT>
That to me means that IT almost completely forgot #1, or didn't understand its clients

Don't be disheartened!
There are three ways to get what you want: diplomacy, assault and subversion.
Welcome to the underground!
You've tried diplomacy, and you have't got the position  for assault.  Subversion it is!

Now, you have the resources you need.
An IT deparment that is big enough to have an MSDN subscription, a firm big enough to have surplus hardware around, and yourself.
What you need to ask for is an 'evaluation' server running whatever the company is running presently for SQL Server and Windows Server.
You're going to 'evaluate' how much of a load your migrated Access/Excel app would theoretically put on a production server.

Your 'evaluation' may take years ;)


What you're dealing with is CYA -- cover your a55.
Nobody knows what your app is going to do to a prodcution server -- and nobody ever got skidded for stalling.
People DO get skidded for having production database servers bite the dust -- so don't run a 'production' server!

Running an 'evaluation' will let IT estimate the load your app will put on a production machine.
If it runs well enough, they may never get around to putting you on a production machine.

Let the dust from your dipolmacy settle, and then try to get an evaluation machine.
If that fails, let the dust settle and try to get a surplus 64-bit box and an MSDN licence for yourself--and set one up!

In the mean time, have a look here
http://bofh.ntk.net/BOFH/index.php
and smile.  Your app is considered mission critical.  YOU aren't going anywhere.

Start documenting your requests and replies, and build your case over time.
You'll get what you need eventually!
0
 

Author Comment

by:Davisron867
ID: 35229095
Nick:

The evaluation server idea and your explanation is priceless. You definitely "get" where i'm coming from. Clearly, its sometimes difficult for users and IT people to understand each others priorities. But, that said, I'll assign points and I'll post a followup comment when I get to some kind of resolution.

Thanks again.
0
 

Author Closing Comment

by:Davisron867
ID: 35229170
Strong participation and sound advice did more than answer my question...it changed the question altogether.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

18 Experts available now in Live!

Get 1:1 Help Now