Hi, please can anyone help me out with this.
I have been struggling with it for days and no result yet.
I am trying to run SQL queries that retrieve Tables stored in SQL server and then place the table in a VBA FORM label or textbox.
According to the code below, the name of my label is ResultLabel.
So far there has not been any success as this is only returning back the query I give in on my FORM label.
I am sure the query is working because I can see the result in an excel sheet.
Please can someone tell me where I am getting it wrong.
I have also attached a screen shot of the FORM to this question.
Private Sub ENTER_Click()
Dim cn As Object
Dim rs As New ADODB.Recordset
Dim strFile As String
Dim strCon As String
Dim strSQL, strInput As String
strCon = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=KBOW;Data Source=10:0.8\KBOW;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;"
Set cn = CreateObject("ADODB.Connection")
If ComboBox1.ListIndex = -1 Then
MsgBox "No Test Selected!", , "BADE
ElseIf ComboBox1.Value = "Functional Test" Then
strSQL = "SELECT ModuleId,EntryDate FROM inventoryModuleLocation INNER JOIN " _
& " dbo.InventoryLocationList ON dbo.InventoryLocationList.LocationCode=dbo.inventoryModuleLocation.LocationCode; "
Set rs = CreateObject("ADODB.RECORDST")
rs.ActiveConnection = cn
For iCols = 0 To rs.Fields.Count - 1
Worksheets("Sheet2").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
ResultLabel.Caption = strSQL