Use Access as back end for Excel

[chromachem]

Hello All,



I have no clue how to do what we want.

First off a little history:

We run an environmental testing lab. At least 15 years ago, we made a critical error. We chose the wrong tool for the job. Instead of writing our lab management software as a database, we used Excel and Excel VBA.

The Excel workbook is used to log samples, write reports, update QAQC charts, etc... The workbook does everything we need, albeit at times slowly. It is an XLSB workbook.

The draw back is twofold. First off, we can't have more than one user working at a time. Secondly, everything references a cell. It is a pain when we add a colunm to a sheet and a bunch of cell references neet to be updated.

What I am thinking of is to use excel as a front end, and have all sheets that hold data only be in an access database. Use Excel (365) as a front end.

We have searched. There doesn't appear to be any information regarding multiple users being able to share an XLSB workbook.

Bottom line: I'm clueless about a direction to take that would allow us to have more than one user at a time working in the workbook. We'd hate to lose all the VBA code we've amassed over the years.



We see 2 options:

1) Deal with it
2) Split the data part into a database and use Excel as a front end.

So.... We're basically interested in hearing how you would go about handling the situation.
At this point, a total rewrite would be too much work.

As always, thanks to all for taking a look.

[NoellaG]

Certainly don't make the same mistake now. A total rewrite might be a lot of work, but Excel as front end for Access tables is again making the same mistake: choosing the wrong tools. When using Access, you can publish results in Excel, but for input I would rely on Access forms.
Even more: if you have multiple users and a lot of data I would recommend to use Access forms on a SQL background (SQL server, MySQL, PostgreSQL, ...)
If rewriting is to much trouble, just stick with the old solution and deal with it.

[Micron]

Excel as a front end? How would that solve the concurrent users issue? To elaborate on what Orange said, Excel data is wide (data is related across columns) whereas db data is tall (data is in rows and relationships are created amongst tables). I can only see Excel working with tables that are designed like spreadsheets, and that would be worse than what you have now. IMO your only real options are to stick with what you have until the single user constraint and sluggish performance becomes too much to bear, or bite the bullet and redesign now. I'd create a new application with Access as the front end and if desired, use Automation to push data into Excel for charting purposes. Unless you only need bare bones pie or bar charts, Access charting is woefully inadequate.

[chromachem]

Thanks for the responses. Much as I thought. I'll start creating a new applicaton.

[Micron]

If you mean to create an Access db, you really must understand db normalization as a minimum. I'd advise you to follow the links in post 4 here
https://www.accessforums.net/showthr...773#post521773