How To Use Excel to Lookup Data In Access?

[icon123]

We use an Excell based quoting form at work. I would like to start keeping our vendor pricing in an Access DB. What I would like to do is take the bill of materials that is in Excel and look them up in the Access database and pull back the pricing from Access to the form in Excel. Pretty much as automated as possible.

It seems easy enough, but I need help getting started. I guess I am looking for the acutal macro/VBA command that will assist me with this. Thanks for any input.

BTW - This is using Excel and Access 2010.

 

[Asator]

You're going to need to give a detailed description of both how your data is structured in access/excel, and what it is precisely you're looking for.

Or you could hire a consultant

 

[Jared_8878]

If your bill of materials is in excel already, why not just add prices to them and do lookups off of that?

That way you don't need to round trip your process to another application.

Sounds like everything you need is already in excel.

 

[icon123]

If your bill of materials is in excel already, why not just add prices to them and do lookups off of that?

That way you don't need to round trip your process to another application.

Sounds like everything you need is already in excel.

We currently don't have all the prices in Excel and look up each price manually. So it was my idea to try to automate this process. Since we have over 60k different parts with pricing, I thought it would be a better idea to keep this data in a database. However, since we pretty much have the front end developed in Excel, I thought we would re-use that functionaility. I'm not sure if this will matter or not, but I plan on getting regular pricing updates from my vendors and thought it would be easier to maintain/update from a database than from Excel.

 

[ndendrinos]

maybe this will help:
http://www.mrexcel.com/forum/showthread.php?t=179936&highlight=ndendrinos&page=2

Not sure if this will work for you using Excel 2010

 

[icon123]

Thank you for your reply. Is it true that you can only search the database's table from field 1 or did I misunderstand? If I can search via whatever field I want, then I think something like that would work.

 

[ndendrinos]

Good question but I can't remember. However you could always add a "first" column in your database that would mirror (linked) the one you want to use