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
      Mydatabaseconn.Open
     
      On Error GoTo closeconnection


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


On Error GoTo closerecordset


Worksheets.Add



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


Range("A4").Select
Range("A2").CopyFromRecordset Mydatabasedata
Range("A4").CurrentRegion.EntireColumn.AutoFit


On Error GoTo 0


closerecordset:
     Mydatabasedata.Close


closeconnection:
     Mydatabaseconn.Close


End Sub






  Rupesh Kumar       15 Sep 2016       0       0     

Hi Rupesh,

is this code working for you?

Thanks,

  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