4

Thinking of moving a couple of linked spreadsheets into a database model instead. However, I have a lot of non-technical uses who can just barely use Excel but having them use something like MS Access would be out of the question. (or OOBase/MySQL as we don't have MS Access)

I want to keep Excel as the main user interface for reporting and was thinking of using ODBC connections for this but I've not used it before and wondering if it's suitable.

  1. If I build the query, import the data into an Excel file and save that... is the data persistent? Will it still be there after closing & opening the file or would users have to select a query every time?

  2. Can I use it as a source for Excel's charts (non-pivot) and is it easy to automate similar to Excel's normal tables using structured referencing?

  3. Can I easily append an excel table for calculated columns based on the database data or would it be best to include such calculations in the database model itself?

  4. I understand the queries are read only? Is there any possible data entry method to write to the database using excel, rather than a form within the DBMS? Simpler the better for end users.

  5. The database files themselves will be stored on a shared network. Will I have to configure/install anything on each local machine for them to be able to access the data? (all machines already have excel)

1 Answers1

2

  1. Excel stores a copy of the queried data in the spreadsheet. You can set an option whether you want it to refresh on load, or only upon some manual intervention or VBA-based event.

  2. You can use Excel query data the same as you would any other data in Excel. In the absolute worst case, if there is some kind of limitation, you could copy your queried data to a new worksheet and "Paste Values" to break the linkage to the query.

  3. Whether to do calculated columns locally or on the DB depends on your performance needs. In one respect, certain calculations can be done more efficiently on a dedicated database server with high-end specs and an optimized SQL engine (Excel's ODBC is definitely not that, trust me). On the other hand, if you're going to have thousands of clients hitting the DB, you might hit CPU or throughput limitations if you tack on a lot of extra data to be transferred, especially if you don't have a particularly fast uplink between the clients and the DB. So you might want to let each client's workstation do the calculated fields, which spreads out the load among all the clients, and reduces the amount of data transferred over the network. Excel is just not as efficient at handling enormous scale data as dedicated databases, though. The answer is 'it depends'.

  4. The "Microsoft Query" engine itself within Excel may not be able to do update/insert queries, but you can write a VBA macro or a C# addin that uses ADO to run arbitrary SQL against the backend.

  5. This depends completely on which database you select. You mentioned Access, OOBase (I'm not even sure this will work at all), and MySQL so far. Access has a built-in ODBC driver on any system that has Access installed. MySQL would have to have the custom MySQL ODBC driver installed on each client system. Most other databases would also need some kind of ODBC driver installed on the system, unless you luck out and the DB you want to use has a built-in driver already supplied with Windows.

allquixotic
  • 34,882