1

In my office, we often use MS Access for many applications (eg creating reports, data analysis and data processing) because it is convenient to use (easy data import, fast query building, easy automatization with VBA)

Unfortunately, it starts to be insufficient for me due to the size limitations of the database file. This is one thing. Another thing is that Access is often unable to execute my complex SQL queries (I omit that the SQL used by Access is limited). Is anyone out there aware of any tools similar to MS Access that can be used with databases over 2GB?

Possibly, I am considering using Access as a front-end, but what about the back-end? Can you recommend something?

closed as off-topic by Colin 't Hart, mustaccio, MDCCL, Michael Green, ypercubeᵀᴹ Feb 10 at 15:40

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Colin 't Hart, mustaccio, MDCCL, Michael Green, ypercubeᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Your question is off-topic, but if you like the Access front end, PostgreSQL has ODBC drivers, which you can use to connect Access to a PostgreSQL backend. PG is fully open source and production worthy; there are no limitations on data size (besides your disk space). – jpmc26 Feb 9 at 1:41
  • 1
    I second @jpmc26's recommendation for Postgres. Besides being fully open source, they have an excellent community. For example, here is the novice mailing list, with the description "No question is too simple for this list." – Wildcard Feb 9 at 2:15
3

Check out Linked Tables to SQL Server if you're running up against complexity requirements for your data. Basically, as you are expecting, Access would still work as your front-end and SQL Server would work as the back-end. For complex SQL statements, you can make use of Stored Procedures that you call from Access as well. If nothing else, this could give you some more time to rewrite the Access application in something a little more robust like .NET (language/platform of your choosing). Honestly though, if there's no pressing need to change out the Access application, keep it around if it's doing the job. I support clients that run tons of business functionality off of an Access front end tied to a basic availability group on SQL Server Standard Edition for high availability. It does the job just fine and it doesn't cost an arm and a leg.

2

SQL Server Express has a 10GB limit.

It should work well with MS Access as a front end.

  • I've seen access used as a front-end to postgresql too. – Jasen Feb 9 at 22:17

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