Forum Discussion
Access data from a database without copying it?
Hello, I can't believe I can't find this answer, which makes me think I'm asking the wrong question. How can I extract data from a database without copying the database into the current Workbook? Ex...
I don´t know if I understand the problem... I tried to do it with power query but I think I'm doing exactly the 3rd option haha
rmahoney
Hi alannavarro, thanks for the reply. Yes, that is the best result I've had as well (outside of using Access). There are a few shortcomings to this method, though, ordered least important to most important.
- I don't want to have multiple tabs. This is meant to be used by everyone in the company, so the simpler, the better.
- I want the fields to fill in after the user enters the part number. In Excel, this would be done by having a parts table and a BOM table. When the user enters a PN in the BOM table, xlookup checks the parts table to pull the appropriate data from the parts table. The best I could do with power query was to enter the PN, update the query, then use xlookup to pull the data from the updated table. The biggest problem here is that it is not very user friendly.
- The biggest problem with this method is it doesn't allow for data validation. If the user enters a part number that doesn't exist, there is no way for me to put up a warning or a message. If the user then refreshes the connection, the line gets added with blank spaces in all the columns that should have data.
Ideally, this should all be done in one table, but I have not yet figured out how to do this using power query.