Filling data from MySQL db into excel sheet, where ID matches

Hey everyone,

I haven't been able to find any similar examples online to what I'm trying to do.. hoping someone can help out here

I have a database in MySQL called 'materialsdb' which holds tables for Materials, Manufacturers and Vendors. Materials is the main table of use, linked with foreign keys to Manuf_ID and Vendor_ID.

EERD.jpg

I also have an excel workbook for Bill of Materials (BOM). The BOM has columns for part_ID, name, manufacturer, vendor, cost, etc.
part_ID (actually called CW_ID) and name are the only columns which are populated, the rest are left blank to be filled in from the database.
The main goal is to be able to check off items in the BOM that will be used for a project, and then click a Macro button, which will populate the empty fields with data from the database tables.
If item is not checked off, it's columns will not be populated, and possibly filtered so it no longer shows up.

So far, I have been able to create a button which can import any one of the database tables into specified location in the BOM. However, it just imports the entire table in one location.
Because both the BOM and the materials table in the db have a column for part_ID, I'm hoping to have it where it will see a match for part_ID and fill in the columns like Manufacturer from the manufacturers table, vendor from vendor table, price from materials table, etc.

BOM.jpg

Because the manufacturer and vendor tables are linked to the material tables, I'm not sure if you can use views or joins or something like that, but any help would be appreciated.

This is what I have so far for the button click:

Please Login or Register  to view this content.

@khelza ,

a) You need to loop through the column (CW_ID) of your BOM Sheet.
b) Pass the sql statement to extract the details for a specific CW_ID
c) Populate the date in the respective cells

This loop continues until you hit the last row.

The issue here is that you might have more than one vendor so you might have more than 1 single row of data for each CW_ID.

Your sql will be something like this :

Please Login or Register  to view this content.

Not sure about your primary_Vendor info.

Will look into this later as i am signing off now.

@xlbiznes, When you say

Your sql will be something like this :

Are you talking about an SQL query within mySQL? Or is this written in VBA?

@khelza,

the query is written in vba code.

I guess it would be something like this :

Please Login or Register  to view this content.

I really appreciate that! I have added the new code to my existing button click.

Please Login or Register  to view this content.

I changed CONN to cn because my other module declares:

Please Login or Register  to view this content.

When I run this code, I get a runtime error at line:

Please Login or Register  to view this content.

Please check the my_sql part if something is wrong with that.

In order to test the other part of the code, you can try to hard code my_sql string to fetch a couple of fields from your main table and see what happens.

if it fetches the required data then we need to check the sql statement , which is :

Please Login or Register  to view this content.

You sql string :

Please Login or Register  to view this content.

seems incomplete, as the where statement is missing as you need to link the tables.