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

Open Source Database Recomendation

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?

  • 7
  • 4
  • 3
  • +8
7 Solutions
Ephraim WangoyaCommented:

You can try Firebird. This is fully featured yet light

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.

Davisron867Author Commented:

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Davisron867Author Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
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)

Aaron TomoskySD-WAN SimplifiedCommented:
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
Davisron867Author Commented:
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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' ?

gskoczylasSenior Software DeveloperCommented:
- Firebird
- PostgreSQL
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 :)
Robert JacksonSystem DeveloperCommented:
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.  


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

@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.
Davisron867Author Commented:
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:

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.

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!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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.


@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.
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.

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! :)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"This isn't always true--but often enough--and sometimes with good justification."
ok.  But 99.314 % of the time :-)

Davisron867Author Commented:
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.
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.
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
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!
Davisron867Author Commented:

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.
Davisron867Author Commented:
Strong participation and sound advice did more than answer my question...it changed the question altogether.
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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