Access data from a database without copying it?

[rmahoney]

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?

Explanation: I have a database of part which have all sorts of fields associated with them - for simplicity's sake, the two fields I'm interested in are the part number and the description. I want to create a bill-of-materials which accesses this database. When the user enters a part number, I want Excel to check the database, if the part number exists add the description in the next column, and if it doesn't exist provide a warning. This is all very easy to do if I copy the database to a new table - I simply access the required information from that table, set up data validation, and use xlookup to extract the associated description. However, I don't want to copy the entire database to the workbook, I only want to pull the information related to a given part number.

What I've tried: I've tried several work-arounds:

1. I've tried setting up a second workbook which is a copy of the database. This works well until I close the other workbook. Once it is closed, Excel can't seem to access the named columns anymore, and keeps returning "N/A".
2. I've tried filtering results to just the result I want using Power Query. This works, but I need to create a new query for every entry, and I see no way of selecting the correct query based on the entered part number, nor do I see any way of accessing the list of existing part numbers.
3. I've tried doing a query merge between the external data source and the list of part numbers. This correctly pulls the data without copying the entire table, however it creates a new table with the combined data. If I try to edit this by, say, adding a new PN, it breaks. I have to add a PN in the original table, which does not have the description with it, nor does it have data validation. Further, if I add a PN that doesn't exist, I don't see how to get a warning when I refresh the connections.
4. I've tried doing this in Access. This works exactly as I want, but most of the users who will be doing this don't even know what Access is, so I'm stuck using Excel.

Thank you!

[#2]

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 

[#3]

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.

1. I don't want to have multiple tabs. This is meant to be used by everyone in the company, so the simpler, the better.
2. 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.
3. 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.