Decoding a part number Vs. Read/Write from DB file

[hockeyadc]

I have a program I made, which allows users to go through a series of drop down menus, creating a part number, then clicking a button which brings up the build of materials for that part number.

I now need to add an option where the user can just paste in a part number, and it gives them the BOM. I thought of two ways to do this:

1. Create code the will "decode" the part number, piece by piece. The issue here is that there are about 10 different formats the part numbers can follow.

2. Loop my code I already have with a bunch of nested loops, going through each part number option, and creating every single part number there is. Being that there will be millions or part numbers, I will have to export them to a database, and then the user will use a Userform front end, to dump in the file, and find it in the DB file.

I am thinking about programming it using option 2, but I'm worried that the lookup time will take a while? Any thoughts on this?