Data fetching from SQL Server in VBA

Question

Hi Below is my code, i am not able to fetch the data from my SQL server, its throwing error as

Compiler error :  object required.

There is no problem with the connection, connection is sucessful.

please correct my code, help me with this thing

Private Sub CommandButton1_Click()
Set SQLConn = CreateObject("ADODB.Connection")

SQLConn.Open "provider =sqloledb; Data Source = xxxx; Initial Catalog = jjjj; User Id = yyyy; Password = zzzz"

       MsgBox "Connection Succesful"

Set SQLData = CreateObject("ADODB.Recordset")
With SQLData

    ' Assign the Connection object.
    .ActiveConnection = SQLConn

    ' Extract the required records.
    .Open "select invoice_num, invoice_date, invoice_amount from im_invoice where billing_account = 'HS0076A' and invoice_date ='01-apr-2011'"

    ' Copy the records into cell A1 on Sheet1.
    Sheet1.Range("A1").CopyFromRecordset SQLData

    ' Tidy up
     .Close

End With

SQLConn.Close
Set SQLData = Nothing
Set SQLConn = Nothing

End Sub

Thank you

thank you its working .... :)

Hello! If you found out how to make it work, would you be able to accept the answer that helped you? :) You can accept an answer by clicking on the checkmark beside the answer. — hammythepig
– hammythepig, Commented Feb 6, 2017 at 22:47

Tim Williams · Accepted Answer · 2011-04-15 16:40:02Z

4

Missing "Set"...

' Assign the Connection object. 
Set .ActiveConnection = SQLConn

answered Apr 15, 2011 at 16:40

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JTG · Accepted Answer · 2014-11-21 21:03:51Z

4

I am writing this query when you want to establish a connection between Excel & SQL Server in VBA using OLEDBConnecion. And yes it's for Windows authentication. Solution is mentioned below. You need to add 'Microsoft.ActiveX Object Library 2.8'.

Sub GetDataFromADO()

    'Declare variables'
        Set objMyconn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset
        Dim rc As Long

    'Open Connection'
        objMyconn.ConnectionString = "Provider=SQLOLEDB;Data Source=SAXAM\SQLEXPRESS;Initial Catalog=AdventureWorks2012; Integrated Security=SSPI;"

        objMyconn.Open

    'Set and Excecute SQL Command'
        Set objMyCmd.ActiveConnection = objMyconn
        objMyCmd.CommandText = "select * from [Person].[BusinessEntity] "
        objMyCmd.CommandType = adCmdText
        objMyCmd.Execute

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyconn
        objMyRecordset.Open objMyCmd

    'Copy Data to Excel'
        'ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)
        Application.ActiveCell.CopyFromRecordset (objMyRecordset)
        rc = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        ActiveSheet.Cells(rc + 1, 1).Select
        'Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Value
        objMyconn.Close

End Sub

Thanks!

edited Nov 21, 2014 at 21:03

JTG

8,8566 gold badges33 silver badges38 bronze badges

answered Nov 21, 2014 at 20:25

Satyarth Rao

411 bronze badge

1 Comment

RockScience Over a year ago

does this work for data which output size is unknown?

Collectives™ on Stack Overflow

Data fetching from SQL Server in VBA

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related