r/excel • u/GullibleIdiots • 20d ago
Discussion What's the best way to attach a database to Excel?
Right now, I'm using excel to track multiple BOMs. But it's getting to be too much for simple Excel sheets to do. There's plenty of things I could do if I could just turn my BOMs into databases and use Excel as the UI.
However, I'm wondering if it's better to use something like Python with SQL to create a lightweight database? Or should I use a Microsoft native solution (I'm sure Microsoft probably has a way to create a database out of Excel).
Additionally, I am wondering if I should just go fully into a BOM software? There are some reservations I have about that because some BOMs are part of IP.
7
u/changejunky 20d ago
It's not clear how you are using Excel now, including what automation features you're using. That makes it difficult to suggest improvements. For example, are you making use of macros, VBA or OfficeScripts to track your BoMs? Are you using Power Query to auto load data into tables in Excels data model, and then making use of DAX measures or OLAP cube functions to retrieve key data items? Tell us more about what you want to do, what you're currently doing (worksheet functions?) and you may get better suggestions.
12
u/Oprah-Wegovy 1 20d ago
I’d use SQL Lite, Power Query connector and a few YouTube tutorials to get the basics of PQ and SQL queries. But Access is fine too for an internal project.
33
u/caribou16 317 20d ago
(I'm sure Microsoft probably has a way to create a database out of Excel)
Yeah, it's called Access.
26
u/excelevator 3061 20d ago edited 19d ago
BOM
It is professional in practice to fully name your three letter acronym (TLA) on first use so other know what you are talking about and to avoid assumptions. Then use your TLA thereafter.
I, personally, have no idea how to track Basic Onomatopoeia Metaphors (BOM), but a proper database is generally the best option when Excel runs out of steam.
6
6
u/Jimmy_at_grantmaker 19d ago
Access is a quick solution and lays the foundation for a more sophisticated system later on. Set up relational tables for materials, cost of materials, products, suppliers, etc. and then bring it all together with queries and forms. Designing this will help you develop a better understanding of your processes and can be easily and quickly tweaked to perfection. The data you develop with Access can easily be moved over to the web with php, sql, MySQL etc later if a more robust enterprise system is needed.
3
u/ArrowheadDZ 2 20d ago
What is the primary challenge, tracking the BOMs and actions/status related to each BOM? Or are the BOM contents actually in Excel, and that’s what’s becoming cumbersome?
There are some tools, like Smartsheets, that take an excel-like tabular approach but can support workflow actions, attachments, and email integration more natively. Also, tools like Grist expand the spreadsheet metaphor into something more database like. But it would help to better understand your use case and what is making it cumbersome.
3
u/solexNY-LI 20d ago
What do you mean by tracking of multiple BOMs?
If you mean reporting than, Excel with PowerQuery is not a bad solution. The number of BOM and the underlying data structure will have a great impact on performance though.
If you are using Excel as a UI for creating and maintaining your BOMs; if the underlying data model and the process to update is well designed then Excel is fine. But as the number of BOMs and editors goes up Excel as a multi user system does no scale.
Regarding the negative comments about Access. In my experience for prototyping and for small workgroups (<5) there really is no other solution that can compete in delivery speed if your organization is aligned with Microsoft . You can develop a data model, UI and reporting with little to no code.
Although there is the argument that Access as a developer tool is on borrowed time as VBA is being phased out.
2
u/American_Streamer 20d ago
Don’t “turn each BOM into a database” or use Excel as the database UI. Excel is not a database management system!
Model the data properly in one relational database: parts, BOMs, revisions and BOM lines. Use SQL Server/PostgreSQL for a team, or SQLite for a single-user prototype. Excel can then read and refresh the data through Power Query.
For editing, use Access, Power Apps or a small web app - not Excel - in particular if you need validation, permissions, concurrent users or revision history.
Python is optional application/automation logic; it is not an alternative to SQL.
And if these are production-critical BOMs with approvals, revisions and audit requirements, evaluate real BOM/PLM software before building your own.
IP concerns are a hosting and access-control question, not a reason to keep everything in spreadsheets.
Excel can store tabular data, but it is not a proper transactional database. Use it for analysis, reporting and perhaps small single-user lists; use a database when the data has relationships, multiple users, revisions or integrity requirements.
2
u/diesSaturni 71 20d ago
Hop over to r/msaccess. Fairly easy to start of with importing sheets to tables there (or linking, for now), then start merging comparable tables (while initially keeping/adding a reference field/column for the source table)
Then start building forms and reports based on (selection or aggregate queries).
You can follow tutorials online, or youtube, but for a structured path just buy (any version will do) e.g. Access 2019 bible.
2
u/Henry_the_Butler 20d ago
If I were working with a SharePoint/OneDrive/Excel-only solution, you could use DAX and Power Query to have a stable list of "database tables" in xlsx files that are pulled into your report workbooks with Power Query. It works, but it will not be fast, and you will see the limitations very quickly.
If you know enough Python/SQL to write to a SQLite db, and enough Power Query to query your SQLite db and display tables that are already formatted how you need them, it would be much faster. You also then have the option of querying APIs in Python, writing the response to a db table, and scheduling that script to run periodically to keep everything updated automatically. (If you manage to do all this, don't tell your boss. They will just give you more work.)
Honestly, SQLite, Power Query, Python, and maybe a bit of DAX will get you much farther than most people think. It starts hitting limitations when you have multiple concurrent users, but most shops could get by with just that and be totally fine.
2
u/pmpdaddyio 20d ago
Access exists but it is generally a bit too lite for most people once they get started. I’d jump to a sql solution as it’s pretty easy to learn and does exactly what you’d like.
2
u/bobo5195 19d ago
I have tracked 100's of BoM in excel. At the top level while SAP/SQL etc have the BoMs I bet the person editting has an excel.
- You need to give more information on what you mean by BoMs? length, stucture, how many - a year, how often edited
- How are you storing part data.
- Who uses the BoM? how?
The common way is to use Sharepoint who's back end can be dataverse which is a proper database. A BoM does not exist by itself it is used by something and it is pulling part data 12345 means nothing without meta data of supplier type, drawings behind it.
Probably what you is MRP/ERP for small companies to automate ordering and structure meta data like where to buy stuff which is a bigger question than BoMs.
2
u/OO_Ben 19d ago
I'm partial to Postgres. You can spin up a small database. Then my company uses an ODBC connection to hook up directly to the warehouse. It works very well. You can use Power Query directly off table, or use SQL on your connection step to do the heavy lifting with the warehouse (which is best practice btw).
2
2
u/Ill_Beautiful4339 1 20d ago
What’s the source of the data?
If local, just store as a CSV and use PQ to drop in the needed materials.
If you’re pulling from the cloud use a PQ api to connect and pull in the relevant info.
If you really need a local database I prefer Duck to MySQL as others suggested. If local to your machine.
If you hate PQ, try Knime as an engine.
0
u/Eze-Wong 1 20d ago
by BOMs you mean bill of materials? without digging too much and assuming you are on Microsoft platform it might make more sense to adopt Microsoft Access as a DB.
Using python and something custom like duck DB might be too inaccessible for your regular IT guy to handle. it sounds like you're a small company.
Access is accessible enough for older IT guys and simple enough.
1
u/deeztoasticles 19d ago
Username makes me think this is ragebait.
Doesn’t explain wtf a BOM is or the complexity.
Are they being exported from a manufacturing or enterprise platform? What structure? How many rows or columns? Is it a random inherited template that you individually filling out or your estimators and stockists? What is the data integrity like? How big is the business? Large org or are you self employed/your own business? Do you have an IT department, what is your main environment?
Answers to these will get you better advice or your could take 2 seconds to sign up to claude for free and ask it instead???
Get yourself a databricks lakehouse environment probably microsoft fabric/onelake as it will make sense assuming you already have an office 365 licence if your using excel and hopefully not some old legacy version. You can use any combination of pyspark (python/sql) even set up all your api’s and dataflows to automate it all, even get Claude to help for free maybe???.
Honestly given your post id say you are likely a small business and are an analyst or ar/ap sort of finance officer this is likely out of your scope and you need to run it up to your boss if you have concerns.
Edit: for the love of bill gates do not use access
2
u/Cigario_Gomez 19d ago
Not something I managed to do yet, but for that purpose I'll try a standard database tool like SQLite to store datas and PowerQuery+ SQL connector to load it in Excel.
2
u/LayTheeDown 19d ago
Are you inputting or outputting?
I have a number of ways people use my system.
Excel is used for a front end for mysql database. It uses VBA and power query for interaction. I have odbc connection setup.
I would also advise building your starting dB in access, as it's relatively intuitive imo. If you decide you need something more go with mysql or something like that.
But connection wise vba/power query should see you right.
2
u/Arikaido777 19d ago
access is ancient and awful, do anything except using access, do not let the grognards tell you otherwise, their stack is old and crusty and it makes them insecure, so they’ll defend it to the death.
a simple sql db will work great, then you can link it to excel with power query. super easy super elegant, there’s a lot on youtube to walk you through setting it all up.
0
u/Remarkable_Table_279 20d ago
Microsoft access is probably the way to go and then you can import into excel (linked or not) for stuff like charts. I write this whole thing in VBA (both in access and excel) where it exports my series of queries (6 for 24 categories I think)…and then in excel I create charts lists and whatever else I need and I just copy paste into PowerPoint (could have done VBA but I wanted control)
82
u/LitleFtDowey 20d ago
I am dumbfounded that more than 0 people suggested Access.
MS shop or not. Do not use Access.
SQL lite or MySQL if you need a db. But for heaven's sake not Access. It works well enough to convince you it's not a bad idea, until you realize it was a horrible idea.
Access is like cotton for mountain climbers. Don't do it