Hello,
My engineering group at work is mostly old guys (no offense to "the olds" on here) who inherited an access file on our server that somehow references an excel file... that references our ERP system (for some common items) when we have pricing contracts with suppliers for that specific component.

The guy that initially set these files up is long gone.

Most of what I source is pneumatic fittings. For some reason, this group that I just recently transferred to sources components even if we have standard fittings listed in product catalogs. Don't ask me why because I have no idea and they didn't like me suggesting to change this...

Anyways, I'm wondering if anyone can point me to good resources on learning how this works. The guy that's responsible for maintaining it knows very little about how it works and he's about to retire. So it'll be me learning on the fly and I don't want to be responsible for this when all I know is how to watch YouTube videos to setup VERY basic vba files.

Additionally, I'm wondering if there's a way to automatically open and scan multiple supplier website searches if I enter requirements or know a component has an equivalent... for current lowest price and current lead time. If that's possible.

Thanks.

There are a ton of resources out there around Access, but honestly what you're describing doesn't sound 100% correct. I have to assume that Access is your DB and that Excel is getting data from there too. At least I would hope so. I've ton a ton of work with Access and while it's good for small projects, it just doesn't scale well. I would focus my time on understanding how this all works and putting together a spec for how to replace it. You can then incorporate the functionality of scraping websites to add more information. In an ideal world, these websites have an API you can consume to make your life easier.

DAM said:

---

...what you're describing doesn't sound 100% correct. I have to assume that Access is your DB and that Excel is getting data from there too. At least I would hope so...

---

There is an Access file we use to create new numbers that are used internally for non-standard components, mostly fittings and other hardware. The only useful information in this file is the (internal) part number column, a brief description, supplier name and supplier SKU.

After completing the customer drawing, we then use an excel file that references another file that is labeled "vlookup-year-month." This excel file is what we use to build a bill of material for all components used in the job.

After routing the engineering drawing to sales for customer sign off and pricing approves the BOM, the internal numbers which were created during that specials job will be loaded in our ERP system. I think the vlookup file is then updated to reference the information in ERP, which can be refreshed in the excel BOM file via data dropdown>edit links>open source>wait for source file to open>close source file>BOM file is updated. For our group, ERP link is useful for pricing because it will automatically fill out the excel BOM fields for description, lead time, purchase price, sales price and OEM discounts (if any apply).

Quote:

---

...I've ton a ton of work with Access and while it's good for small projects, it just doesn't scale well. I would focus my time on understanding how this all works and putting together a spec for how to replace it...

---

I know enough just to be dangerous when it comes to wanting to improve internal processes. After thinking through your questions, it's obvious that I need training on this. I just don't want to recreate the wheel if there is a better. With no background and no file management knowledge outside the basics of what I have to do every day to get the job done, I don't really know what my options are. I just know there has to be a better way. Perhaps an OTS or potential outsourced coding job that could help automate and consolidate the excessive manual processes involved for every job.

Quote:

---

...You can then incorporate the functionality of scraping websites to add more information...

---

I made an excel file a few months ago for myself. I used vba for the first time and managed to make it actually work! The file is table driven and meant to be filtered by category. Once i find what I'm looking for (ex: #10-32 x .5" SHCS - stainless steel, etc.) the row has a link that will open up my browser and create tabs to our common supplier websites for that component part.

See below for continuation on this.

Quote:

---

...In an ideal world, these websites have an API you can consume to make your life easier...

---

I don't know what API means, but I'm guessing it has to do with built-in search/filter tools on company websites? I do this on each website since they all have different ways of filtering down when I have an odd part requirement to see if one exists. Is there a better way?

Before joining this specials group, I worked exclusively in a "sustaining" role. Engineering drawing portions of my new role are pretty much memory reflex at this point, but the sourcing and pricing steps that have to be done on each job is what causes headaches. That and dealing with field salesmen who usually submit partial and/or incorrect information necessary to give the customer what they want. :]

An API is like a website address where something could get specific information. For example, the census bureau could have an API that gives you demographics by county. Pretty much it's how computers talk to computers.

Maybe figure out what is the biggest/most time consuming part of your process and see if that can be automated some how. Sounds like you have some technical chops and with some googling and patience you might improve your process significantly, but to automate the whole process it might be a much bigger task and might want to bring a pro.

dam