0

When using Excel as a front end and Access as a back end, is it better to import data from queries already created in Access, or to query tables directly from Excel using VBA for better performance and/or avoiding concurrency issues, for multi users?

Sofistikat
  • 111
  • 1
  • 10
  • 1
    Which ever you use Access won't scale well with more than four or five users in my experience - you're better off using SQL server – Jeremy Thompson Dec 29 '15 at 23:46
  • @JeremyThompson In my experience, MS Access can work with much more than 5 users, 15-20 even 50+ given read/write access. One should always [split Access applications](https://support.office.com/en-us/article/Split-an-Access-database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc) (frontend app and backend db) on a LAN network. To recommend an enterprise solution like MSSQL just for 5 users is not truly utilizing the power of Access. The Jet/ACE engine is designed to hold 255 [concurrent users](http://stackoverflow.com/questions/1672077/setting-up-an-ms-access-db-for-multi-user-access). – Parfait Dec 30 '15 at 01:38
  • Use Sql Express.. 255 concurrent Access DB users, yeah you can do it but the performance and reliability will suck. I've got an very popular application with thousands of customers and if you don't want tech support calls don't exceed 5 or so users! – Jeremy Thompson Dec 30 '15 at 01:57
  • 1
    just use connections directly from Excel, you can add your own sql with parameters from the sheet, all you need is a connection string to the database -- no need for VBA – WhiteHat Dec 30 '15 at 02:05

1 Answers1

0

I would say it does not matter too much. However I found it easier in the long run to create the views (queries) in access. It was just much easier to test and maintain.

Mitch VanDuyn
  • 2,838
  • 1
  • 22
  • 29
  • Jeremy Thompson, I wish! But luckily, there are only about 6 users, so Access should be able to handle it. Thanks for your input @Mitch VanDuyn. That's how I've got it set up at the moment, but the question's been nagging me in the back of my head, and since this is the first time I've developed something that combines both Excel & Access, I figured it's probably better to be safe than sorry and ask people who might know! – Sofistikat Dec 30 '15 at 00:16