2

I'm considering building a MS Access front-end for an Oracle database.

I'm not a developer (I'm a public works guy), but I do know my way around MS Access and Oracle. The number of users would be 5, possibly growing to 10-20. The front end would be mostly reports, with the odd form for data entry. Security isn't a primary concern; that's handled by the database, and the information isn't sensitive.

I'm aware that MS Access projects often end up being disastrous monstrosities. As far as I know, MS Access is not meant to be an enterprise system.

Yet I'm considering it, because, well, I don't have any other options. I'm not in I.T., and my I.T. department simply doesn't have the resources to help. And in my organization, a proper, enterprise, out-of-the-box system is 5-10 years away. I can't wait that long. Instead, I have MS Access to work with.

I'm hoping that if I stick to a few key principles, that the front-end won't end up as a fragile, disastrous monstrosity, but rather be a sustainable and robust system.

I'm hoping to:

  • Keep it as simple as humanly possible. If functionality isn't absolutely necessary, then don't implement it. Force stakeholders to justify their requests.
  • Consider it only as a prototype, not as a formal enterprise system. Make all stakeholders solemnly swear to eventually migrate it to a proper enterprise system.
  • Configure, don't customize. Only customize (VBA) as an absolute last resort. Even then, consider not doing things, before resorting to customization. I say this, because I'm the only one in the office who knows how to script, and I'm not even that good at it.
  • Hold regular 'fire drills'. If it breaks, and I'm not around to help, what will happen? Hold regular training/knowledge sharing sessions to teach colleagues about the system.
  • Tend to the system as if I were tending to a garden. Stay on top of things. Continuously improve it by making it simpler, more efficient, and remove unnecessary functionality.

With all this said, even if I manage to do these things, I'm guessing that there are still problems associated making an enterprise system in MS Access.

What are the risks and inherent problems associated with an enterprise MS Access front-end?

User1974
  • 276
  • 1
  • 17
  • 63
  • 2
    Major problem you might encounter is just outgrowing the 2gb max file size. I developed a split db using Access for both FE and BE. It replaced a dBase4 program that ran for 20 years. I expect this Access version could run another 20 and not exceed the file size limit. It's been running 8 yrs now. Only major issue I encountered was IT added so many security restrictions, my code that automatically updated users' copy of the FE on their workstations no longer works. – June7 Mar 13 '17 at 03:03
  • 8
    The poster is using Oracle as the back end database - so the file limits and amount of data is limited by the Oracle database server - the 2 gig limit you note does not apply to this question nor is it relevant in this context. – Albert D. Kallal Mar 13 '17 at 06:32
  • 6
    Indeed for mostly reporting, and as you don't label yourself as programmer, Access will prove an excellent tool. – Gustav Mar 13 '17 at 07:33
  • 2
    I would strongly recommend you consider Oracle Apex instead. It's installed within the database, it runs in any browser (no client-side installation required), and it's easy to build simple applications much like you would in Access. – Jeffrey Kemp Mar 16 '17 at 07:34
  • 2
    You worry too much. As Gustav wrote, if you don't mess up really badly, this will work just fine, and *eventually migrate it to a proper enterprise system* won't actually be necessary. – Andre Mar 19 '17 at 07:42
  • 2
    BTW, here is a great read: http://stackoverflow.com/a/6393213/3820271 – Andre Mar 19 '17 at 08:01
  • @Andre - I reference the post you mentioned surprisingly often. – User1974 Nov 06 '17 at 19:58

1 Answers1

6

Firstly, you should give yourself credit! You definitely don't sound inexperienced; quite the contrary. Your approach to building and maintaining a system sounds top notch. Regarding your limitation, it sounds like you need a reporting tool, and it sounds like Access is your only option. I know there is a comment here that suggests Oracle Apex, but I assume that is not an option for you. A product using native access methods to Oracle would most certainly perform better rather than Access, but that doesn't mean you have hit a dead end. Access is a powerful tool if you understand its limitations (and by that, I don't just mean the 2GB file size limit; I doubt you would run into that). Here are a few of suggestions that I can offer, and hopefully this doesn't sound foreign:

  1. If you have the ability to write your SQL as stored procedures or views inside Oracle, then do that.
  2. Don't link to tables in Oracle, that will be painfully slow.
  3. Don't use ADO to transfer data from Oracle to Access, that too will be slow, as it will require row-by-row handling.
  4. Use SQL pass-through queries to connect to Oracle and execute your stored procedures/views/SQL. This will be your fastest option, as Access only sends your query to the server to execute; it does not do anything itself to run it (or estimate how to run it).
  5. Try to perform all of your logic inside Oracle. Meaning, if you don't have the capability to write stored procedures, and say you need to create temporary tables, then do that in the oracle session, or inside the SQL script that you execute through the SQL pass-through query.
  6. If you need to transfer data to Access, try to limit it. You are probably aren't creating a 200-page report, so don't return data that you don't need to Access; utilize the server's processing power to your advantage.
  7. Let's assume you got all this done, and are now ready to distribute the Access file. Don't put the database on a network drive and share it with users. Give each user their own copy. Now, this topic in itself would be a long discussion, as things like version control come into play, but I won't get into that here. If the users can always go to a website and download a clean Access file each time they need to use it, then do that. If not, and they will always launch their local copy, you need to implement some cleanup routines of any local data in Access, followed by enabling the compact-on-close setting, so it removes the clutter that could affect performance.
  8. If your queries against Oracle don't perform well, and your reports end up being slow, don't be afraid to reach out for help. DBA's won't like anything you do that will affect their database performance, so utilize them for assistance with tuning your SQL.
User1974
  • 276
  • 1
  • 17
  • 63
O. Gungor
  • 758
  • 5
  • 8
  • 1
    *2. linked tables* - only true if you do very complex Access queries e.g. with multiple outer joins. For "normal" queries, linked tables work just fine. *7. Give each user their own copy.* - definitely yes, this is a must. – Andre Mar 19 '17 at 07:39
  • 1
    Note that pass through queries are very fast but return a read-only Recordset. So you cannot use them in a bound form for example. You can however use linked queries using ODBC, making sure you have an indexed primary key for each table. – Mark3308 Mar 19 '17 at 19:44
  • @O.Gungor Thank you very much for your thorough answer. I have asked a question specifically about version control here: http://stackoverflow.com/questions/42896033/ms-access-front-end-does-each-user-need-their-own-copy – User1974 Mar 20 '17 at 04:51
  • glad to help. thanks – O. Gungor Mar 20 '17 at 15:19