Display Sql-server Data into Excel Using VBA

-->

Display Sql-server Data into Excel Using VBA , Working code

Private Sub CommandButton1_Click()

Dim adoCN As ADODB.Connection

Dim sConnString As String

Dim results As ADODB.Recordset

Dim sSQL As String

Dim lRow As Long, lCol As Long

sConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=devdb;Data Source=sqlserverdv1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=USER-PC;Use Encryption for Data=False;Tag with column collation when possible=False"

Set adoCN = CreateObject("ADODB.Connection")

adoCN.Open sConnString

    sSQL = "select top 10 * from tempdata"

Set results = adoCN.Execute(sSQL)

   

    

    

    For Each f In results.Fields

      'Debug.Print f.Name & " " & f

      ActiveSheet.Range("A1").CopyFromRecordset results

    Next

adoCN.Close

Set adoCN = Nothing

End Sub

Parent Child Recursive Relation SQL-Server 2008 JDE

 

drop table #temp2

select yaan8,a.ABALPH ChildName, yaanpa,b.ABALPH ParentName into #temp2  from proddta.F060116

left outer join PRODDTA.F0101 a on yaan8=a.aban8

left outer join PRODDTA.F0101 b on yaanpa=b.aban8

where  yapast in('1','0','6') order by 2

go

 

WITH Recursive_CTE AS (

SELECT

  child.YAAN8,

  CAST(child.ChildName as varchar(100)) ChildName,

  child.yaanpa yaanpa,

  CAST(NULL as varchar(100)) ParentUnit,

  CAST('>> ' as varchar(100)) LVL,

  CAST(child.YAAN8 as varchar(100)) Hierarchy,

  1 AS RecursionLevel

FROM #temp2 child

WHERE YAAN8 = 123456

 

UNION ALL

 

SELECT

  child.YAAN8,

  CAST(LVL + child.ChildName as varchar(100)) AS ChildName,

  child.yaanpa,

  parent.ChildName ParentUnit,

  CAST('>> ' + LVL as varchar(100)) AS LVL,

  CAST(Hierarchy + ':' + CAST(child.YAAN8 as varchar(100)) as varchar(100)) Hierarchy,

  RecursionLevel + 1 AS RecursionLevel

FROM Recursive_CTE parent

INNER JOIN #temp2 child ON child.yaanpa = parent.YAAN8

)

SELECT * FROM Recursive_CTE ORDER BY Hierarchy

Writing Word from Database or Excel

 

 

Writing Word from Database

Best way to do this using Access Database, do following:-

 

1.       Create a Table in access database and define all of the related data for mail merge.

2.       Open this table and click on External Data Word Merge.

3.       Word document will open, Select second option –> Create a new document and then link the data to it.

4.       Type your letter and if you want to attach a field you can click at insert merge field and select your field.

5.       After this you can check the preview.

6.       Click at Finish & merge, once you done.

I think you can use excel by attaching it to access database.

Rajeev Kumar

 http://www.nanthalat.com/jk/sdd4yzc7lqsw7ww9u8z0kjuwx9/