1

I am a working student at a bank and my current task is to create an in-house database for customer-specific lookups which often rely on complex underlying data aggregation from different sources. I was expecting to get access to something like a SQL Server or similar in order to implement this but was left with the challenge to do it without a dedicated DB or server to work with. My goal is to implement the most sustainable solution and not do something like a quick'n dirty fix.

I am always up for a challenge but certain things like data security/integrity and multi-user access popped up when I thought of implementing it all with MS Access. On top of that I worry that it might become messy and also cause some drama should I eventually leave the company.

There are of course dedicated things like an Oracle Data Warehouse but using this will be problematic as other departments are denied access to it and therefore will not be able to use it.

Next thing that came to mind was using something like a portable DB. I downloaded PostgreSQL Portable and it works great, but since I am pretty much the only one fluent enough in SQL to use it, it would require some kind of frontend like Access yet again.

To outline my situation: I have access to my own computer and Network Storage with daily backups. I, as for now, do not have the ability to leave my laptop on all the time and have no access to a dedicated server.

On top of being a DB, I would like it to update and load data from other sources at regular intervals, but I could also automate that via PowerShell scripts if needed.

My questions are as follows:

  1. Is using something like MS Access a smart or rather stupid idea? Are there any points I was missing in my assessment?

  2. Are there any free alternatives I could use for my needs?

  3. Is the plan in its current state even doable or should I rather sit down and negotiate about access to more resources?

I know that the questions are more subjective than I would like but I don't want to upset our DBA's and I think most of you guys have faced similar situations and know what would have been a far better solution. I appreciate any input.

  • What exactly does it need to do? You say "customer related look ups." I could do that in Excel. – Anthony Genovese Jan 28 '19 at 15:42
  • Sorry for being unclear, edited my answer. It needs to be a portable ETL/DB, although that sounds like a unicorn that sh*ts gold. It is less about the things it needs to do, but rather the multitude of things it should be able to do and the aspect of portability. – huhnmonster Jan 28 '19 at 15:45
  • I guess I am trying to figure out why you need a DB at all. Excel is very portable and you can connect to different data sources and load data in it. – Anthony Genovese Jan 28 '19 at 17:15
  • That is totally true, but for one I have faced multiple situations where I just downright was not able to use excel since I passed the row limit by multiple millions and secondly because I am trying to aggregate many different sources of data into different structures and having many excels files and having to update them by hand is a tedious process – huhnmonster Jan 28 '19 at 18:51
1

You can use the free SQL Server Express Edition, and use Access or many other tools as a front-end. You can take nightly backups to network storage, or even store the database files directly on the network storage device.

Later the database can be migrated to a enterprise-managed SQL Server with no code changes.

Not the answer you're looking for? Browse other questions tagged or ask your own question.