Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Concurrently access database with Excel as frontend - doable?

Suppose you have an database with the largest tables containing about 200.000 rows, and frequently modified. The client wants Excel to connect via ODBC to the database, and work as a frontend to manage the data. The data should be modifiable by up to 25 users concurrently.

My first instinct would be to recommend something else, for example a web frontend. But suppose the client insists on the Excel solution, would you regard it as doable, and what pitfalls would you see in it?

My doubts would be about:

  1. data integrity (how to manage users modifying same data at the same time)
  2. large amounts of data moved unnecessarily (when opening the Excel workbook I imagine that the whole database has to be transferred)
  3. security (showing only parts of data to appropriate users in a secure way would be challenging - see previous point)
  4. using a tool (Excel) for something, in which it doesn't excel (pardon the pun)
like image 847
simon Avatar asked Feb 19 '10 09:02

simon


People also ask

Can front end Access database?

When you split a database, you reorganize it into two files — a back-end database that contains the data tables, and a front-end database that contains all the other database objects such as queries, forms, and reports. Each user interacts with the data by using a local copy of the front-end database.

Can Excel be used as a database successfully?

As a spreadsheet program, Excel can store large amounts of data in workbooks that contain one or more worksheets. However, instead of serving as a database management system, such as Access, Excel is optimized for data analysis and calculation.


1 Answers

I do this all the time. No you don't have to bring in the whole database or even the whole table. I use ADO and VBA and send SQL statements via the Command object. For example, I have a royalty database with an Excel front end.

The user types in an invoice number and a SELECT statement retrieves that one record and populates some custom classes. The user enters/modifies some data and clicks 'Save'. Then the class has a method that writes the record back to the database with and UPDATE or INSERT depending on the situation.

At the end of the month, the user enters a date range and retrieves some records into a report, again just a SELECT statement filling some classes and outputting to a sheet.

Use Transactions so you can roll back if you hit any record locking problems, but with 25 users you probably won't.

like image 145
Dick Kusleika Avatar answered Oct 04 '22 20:10

Dick Kusleika