Styjun

Just to give you background of my work, i have to fetch data from MS Sql on daily basis and for that every time have to go to other server to run the query. Once the query is executed, have to paste into my common drive, which takes a lot time. ~55 mins to paste 5,00,000 row & 30 fields to common or to move file. In total 2 hours for execution & movement from one location to other.

To reduce this i would need your help to use the SQL queries through excel with the below things:

If possible,

Point1: Query will be stored in the text file in the common location
Point2: Query Parameter to be populate to get
Or
Point2:Range to be defined for parameter

If not possible above,

Query will be pasted into the code and parameter to be populated based on the above mentioned suggestion.

Connection type is windows authentication, it will work based on logged in users windows name.

This code will allow you to provide variables that you use within your SQL statement and put those into cells on a spreadsheet (In this case Cred2) and return the results on a separate sheet (Sheet2).

The first portion of the code establishes a connection with the SQL server.

The column Headers will be started in Row 2 and then the data will begin populating starting on row 3. I have used this to pull well over 100,000 records at a time and this works very quickly.

Private Sub CommandButton1_Click()

Dim cn As Object
Dim rs As Object
Dim strCon As String
Dim strSQL As String


strCon = "DRIVER=SQL Server;SERVER=ServerName;DATABASE=DBName;Trusted_Connection=True"
Set cn = CreateObject("ADODB.Connection")
cn.Open strCon

' if not a trusted connection you could replace top line of strCon with
 strCon = "DRIVER=SQL Server; Server=myServerAddress;Database=myDataBase;User Id=myUsername; Password=myPassword"



' set up where you are getting your variables to include in the SQL statement

stat = Sheets("Cred2").Range("c7").Value
barg = Sheets("Cred2").Range("c10").Value
worksite = Sheets("Cred2").Range("c11").Value


' Construct SQL statement

strSQL = "select * " _
& " FROM tableName A , table2 B " _
& "WHERE A.[field1] = B.[field1] " _
& " and field1 like '" & stat & "'" _
& "and field2 like '" & barg & "'" _
& "and field3 like '" & worksite & "'" _
& " order by Field? "

 ' Build Record Set

Set rs = CreateObject("ADODB.RECORDSET")
rs.ActiveConnection = cn
rs.Open strSQL


' Display Data

For intColIndex = 0 To rs.Fields.Count - 1
 Sheet2.Range("A2").Offset(0, intColIndex).Value = rs.Fields(intColIndex).name
Next


Sheet2.Range("A3").CopyFromRecordset rs

' Close Database
rs.Close
cn.Close
Set cn = Nothing

end sub