VBA Using SQL Codes

An estate agents eaten apple pics "Our goal is to send a message to cyclists about safety," Gascon said. "Just because you are riding a bicycle doesn't mean all bets are off. All of the rules of the road that apply to everyone else apply to you, too."

  Rupesh Kumar

I got below code to extract data from SQL server using VBA code:

Option Explicit

Const constrSQL As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=LH7U05CG527243J\SQLEXPRESS2012;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LH7U05CG527243J;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Mydatabase"

Sub copydatafromdatabase()

      Dim Mydatabaseconn As ADODB.Connection
      Dim Mydatabasedata As ADODB.Recordset
      Dim Mydatabasefield As ADODB.Field
      Set Mydatabaseconn = New ADODB.Connection
      Set Mydatabasedata = New ADODB.Recordset
      Mydatabaseconn.ConnectionString = constrSQL
      On Error GoTo closeconnection

    With Mydatabasedata
         .ActiveConnection = Mydatabaseconn
         .Source = "select * from employee_data"
         .LockType = adLockReadOnly
         .CursorType = adOpenForwardOnly
End With

On Error GoTo closerecordset


For Each Mydatabasefield In Mydatabasedata.Fields
         ActiveCell.Value = Mydatabasefield.Name
         ActiveCell.Offset(0, 1).Select
Next Mydatabasefield

Range("A2").CopyFromRecordset Mydatabasedata

On Error GoTo 0



End Sub

  Rupesh Kumar       15 Sep 2016       0       0     

Hi Rupesh,

is this code working for you?


  Devbrat Tripathi       16 Sep 2016       0       0     

Yes Dev....

  Rupesh Kumar       19 Sep 2016       0       0     

Post Your Answers:

Please use the CODE HIGHLIGHT Button to format/highlight your codes if any