Have a Question?

If you have any question you can ask below or enter what you are looking for!



VBA Using SQL Codes

 Hi, I need to extract data from SQL server through VBA as a front end.

I wrote the below query but geeting some error. Please help me out.

Sub DatatakenfromSQLserver()

Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.field
Dim mssql As String
Dim row As Integer
Dim col As Integer
Dim ws As ThisWorkbook
Set ws = ThisWorkbook
Application.ScreenUpdating = False
Set oConn = New ADODB.Connection
Set rs = New ADODB.Recordset
mssql = "select Max(salary) from Employee_Data where salary  Not in (select MAX(salary) from Employee_Data), " & _
oConn.ConnectionString = "driver={sql server};" & _
"server=LH7U05CG527243J\SQLEXPRESS2012;database = Mydatabase"
oConn.ConnectionTimeout = 30
oConn.Open
rs.Open mssql, oConn
If rs.EOF Then
MsgBox "No matching record found"
rs.Close
oConn.Close
Exit Sub
End If

row = 5
col = 1

    For Each fld In rs.Fields
    Sheet2.Cells(row, col).Value = fld.Name
    col = col + 1
    Next

rs.MoveFirst

row = row + 1

Do While Not rs.EOF

col = 1

    For Each fld In rs.Fields

    Sheet2.Cells(row, col).Value = fld

    col = col + 1

Next

row = row + 1

rs.MoveNext

Loop

rs.Close

oConn.Close
End Sub


Error message:


                                                                                                                                                                                                                                           

  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