Thread: Inner_Join Help

I am trying to use VBA to automatically update queries each time a new table is created (the tables are created using excel and the updates would occur during this time). Now should I be using a one-many-relationship or inner_joins to link all tables together each week?

If relationships are used then the below is true:
Each week a new table is created with the prefix "Weekof". The new "Weekof" table will be linked to the table tblEmpNames while maintaining the existing links from all prior "Weekof" tables. The query TotalPoints would also be updated in that the sum column will now contain an additonal table in the sum column.

If Inner_Join is used I think this would allow me to maintain one master table that sums each week in a simplified way. Is this true? I should be able to figure out which tables to inner join using vba code. When finding table names with VBA code the order they are added to an array, is that based on the name of the table? Or is that based on the creation date?

I have this code for Inner_Join but I assume something is missing from it as nothing happens that I can notice.

    Dim cn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim cmd As ADODB.Command
    Set cn = New ADODB.Connection
    
    JoinNameWeekof = "select * from " & tdf(1).Name & " INNER JOIN " & tdf(2).Name & " on " & tdf(2).Name & ".fKey" & " = " & tdf(3).Name & ".pkey"

'MsgBox JoinNameWeekof
TheDbPath = currentdb.Name

    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=" & TheDbPath & ";"
     
    If cn.State <> 1 Then
        MsgBox ("Problem with connection")
'        Exit Sub
    End If
     
    Set cat = New ADOX.Catalog
    Set cmd = New ADODB.Command
     
    cat.ActiveConnection = cn
    
On Error GoTo 0

    cmd.CommandText = JoinNameWeekof