Help need to add sql to vba code

[tatendatiffany]

Could someone please i have to create this vba that runs through different tables and return the result on an sql sheet. I have done some of it but i am stuck on a part where it loops through the tables on a dropdown list: and combining this with sql is proving difficult. this is what i have done so far:

Public Sub LoopTable()
Dim TABLES_S As range
For Each TABLES_S In Worksheets("Sheet 1").range("C2:C39").Cells ' runs through the column of table names
If TABLES_S = "" Then
End If
Next TABLES_S
End Sub
Public Sub LoopColumn()
Dim TableClmn As range
Dim Index As Worksheet
Set Index = ActiveSheet
Index.Activate
For Each TableClmn In Worksheets("Index").range("W2:W39").Cells 'runs through the column of column names
If TableClmn = "" Then
End If
Next TableClmn
End Sub
Public Sub Retrieve()
Dim j As Long
Dim SQL1 As String
Dim TABLES_S As range
Dim TableClmn As range
Dim rst As Recordset
For j = 1 To 38
Cells(j + 1, 24).Value = j 'column were value will be placed
Dim i As Long
With Sheets("Index")
For i = 1 To .range("TABLES_S").Rows.count
SQL1 = "SELECT FROM " & .range("TABLES_S")(i, 1) & .range("TableClmn")(i, 1) & Sheets("Element").range("GESTATUS")(i, 1)
Debug.Print SQL1 ' loop through the ranges in excel sheet
Next i

Dim rngCell As range
For Each rngCell In range("GESTATUS")
Call execSQL(SQL1, "Get Element", "GEREGS")
Next Rng

 

[tatendatiffany]

ok sorry i didn't give as much text earlier...well the excel file has a login that already connects to the database so the two columns are the names of the tables and tablecolumn(excel formatted) in the database...so the queries are suppose to once i login in loop through the database running the queries and display the results on the table.

 

[Norie]

Sorry but I'm still confused.


That's only 2 columns of data, is that one table name and one field name per row.


From the example data you posted what should the SQL look like to get the results you are looking for?

 

[tatendatiffany]

So the two columns are one for the table name and the other for the column name in the table. in total there are 18 tables and i just need a loop that runs through them then execute the sql then display results on the page.
an example of a working sql query is
="SELECT COUNT (" INUNACPT ") FROM AISAM4.KTPT80T WHERE CDPRODCO =RETAIL_MAP"

 

[xenou]

I'm not sure if you mean each table and field are a pair (8 queries), or if for every table you want to run a query for all the fields listed (8 * 8 = 64 queries).

Anyway, assuming the former here's a sample of how to put the query string together. I've assumed that the tables are in C2:C39 and the fields are in W2:W39 as per your original post. You may want to step back from this all and create a working procedure that runs *one* query successfully. When that's working, then you can adapt to make it run all the queries.

UNTESTED (beware of syntax errors):

Public Sub LoopTable()
Dim cell As Range
Dim strTable As String
Dim strField As String

For Each cell In Worksheets("Sheet 1").Range("C2:C39").Cells
        If Len(cell) > 0 Then
            strTable = cell.Value
            strField = Range("W" & cell.Row).Value
            SQL1 = "SELECT Count(" & strField & ") FROM " & strTable & ";"
            Debug.Print SQL1
            'Run SQL
	End If
Next cell

End Sub

 

[tatendatiffany]

Thank you very very much for your help, i cant tell you how greatful i am i am going to use it for my project, you are a lifesaver