Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help needed in SQL to Excel VBA Form

  Asked By: Shawn    Date: Jan 28    Category: MS Office    Views: 3159
  

I need a help in displaying recordset values(from SQL) in Excel VBA form. I am
importing the data from SQL to EXCEL using VBA and I could get the result in
the excel sheet. But now, I would like to create one form and I want the values
retrieved from SQL Query through recordset be displayed on the User form. I am
able to display only one record on the form, but not all the records.

Can some one please suggest me on this , how to display a set of records we
retrieve from SQL on the Form.

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Hollie Hughes     Answered On: Jan 28

Do you have any code that we can look at?

 
Answer #2    Answered By: Jackson Williams     Answered On: Jan 28

Please find the code mentioned below.

I need a help  in displaying  recordset values(from SQL) in excel  VBA form. I am
importing the data  from SQL to EXCEL using vba  and I could get the result  in the
excel sheet. But now, I would like to create  one form  and I want the values
retrieved from SQL Query through recordset  be displayed  on the User form. I am
able to display  only one record  on the form, but not all the records.

Can some one please suggest  me on this , how to display a set  of records  we
retrieve from SQL on the Form.

Private Sub CommandButton1_Click()
Set cnhrbpo = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"
'Connect to the hrbpo database.
strConn = "driver={SQL Server};server=hrbpochennai" & sServer & _
";database=hrbpo;uid=hr;pwd=hr"
'Now open the connection.
cnhrbpo.Open strConn
' Create a recordset object.
Dim rshrbpo As ADODB.Recordset
Set rshrbpo = New ADODB.Recordset
With rshrbpo
' Assign the Connection object.
.ActiveConnection = cnhrbpo
' Extract the required records.
.Open "SELECT * FROM orders "
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rshrbpo
' Tidy up
.Close
End With
cnhrbpo.Close
Set rshrbpo = Nothing
Set cnhrbpo = Nothing

End Sub

 
Answer #3    Answered By: Ethan Evans     Answered On: Jan 28

I do not know which control you are using however, you will probably either set
the control source to the recordset  or, as I would do, loop through the
recordset and use additem to add the row to the control. See code below for an
example. Please post again to let me know if it works or not. this code adds
values to a combo box.

Private Function cboAnalysisLoad(cboDatabase As String)
Dim strParam As String

ConnectDB

Set objSourceRS = New ADODB.Recordset

With objSourceRS
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.ActiveConnection = objConn
End With

gstrSQL = "SELECT Name, Description, Rundate, ID FROM " & cboDatabase &
".dbo.RDM_ANALYSIS RDM_ANALYSIS WHERE EXPOSURETYPE in (8017,8029) ORDER BY Name,
Rundate"

objSourceRS.Open gstrSQL
With cboAnalysis
.Clear

Do While Not objSourceRS.EOF
.AddItem objSourceRS.Fields("Name").Value
.List(0, 1) = objSourceRS.Fields("Description").Value
.List(0, 2) = objSourceRS.Fields("Rundate").Value
.List(0, 3) = objSourceRS.Fields("ID").Value
objSourceRS.MoveNext
Loop

End With


objSourceRS.Close
End Function

 
Didn't find what you were looking for? Find more on Help needed in SQL to Excel VBA Form Or get search suggestion and latest updates.




Tagged: