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

[khelza]

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.


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 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.



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:

Private Sub CommandButton21_Click()

' Create a recordset object.
Dim rsMaterialsdb As ADODB.Recordset
Set rsMaterialsdb = New ADODB.Recordset


'connect to your mysql server
ADOExcelSQLServer


With rsMaterialsdb
    ' Assign the Connection object.
    .ActiveConnection = cn
    ' Extract the required records.
    .Open "SELECT * FROM Manufacturers"
    ' Copy the records into cell AL6 on Sheet1.
    Sheet1.Range("AL6").CopyFromRecordset rsMaterialsdb
    
    ' Tidy up
    .Close
End With


rsMaterialsdb.ActiveConnection = Nothing 'close recordset
cn.Close    'close connect to db
                
End Sub