Hello Spunky
Please find a sample of VBA procedure which read a SQL database and
generates a text file:
Hope this helps,
Gérard Ducouret
=================================
Dim cn As Object
Dim rst As Object
Dim rst_Task As Object
Dim Cmd As Object
Const DSN = "SurProjectSvr" '"NomDSN" = "x"
Const STR_USER_IDENT = "sa" '"NomIdentificationDSN" = "x"
Const STR_PASSWORD_IDENT = "sa" '"PassWord" = "x"
'
Sub Lecture()
Dim sQuery As String
Dim sPathFile As String, i As Integer
' Ouverture de connection à SQL Server par l'intermédiaire d'un DSN
système
Set cn = CreateObject("ADODB.Connection")
cn.Mode = adModeRead 'adModeReadWrite (Lecture seule!)
cn.ConnectionString = "DSN=" & DSN & ";UID=" & STR_USER_IDENT & " ;PWD="
& STR_PASSWORD_IDENT & ";"
cn.Open
'1ere Requête :
' Récupération des enregistrements de la table des projets de la base de
données pointé par le DSN
sQuery = "SELECT * FROM MSP_PROJECTS"
Set rst = CreateObject("ADODB.Recordset")
Set rst = cn.Execute(sQuery, , adCmdText)
sPathFile = "c:\InfoProj.txt"
Open sPathFile For Output As #1
Print #1, ""
With rst
While Not .EOF
i = i + 1
Print #1, "********************"
Print #1, "Nom du projet : " & ![PROJ_NAME]
Print #1, "Nom de l'auteur : " & ![PROJ_PROP_AUTHOR]
Print #1, "Nom de la compagnie : " & ![PROJ_PROP_COMPANY]
Print #1, "Date début du projet : " & ![PROJ_INFO_START_DATE]
Print #1, "Date d'état du projet : " & ![PROJ_INFO_STATUS_DATE]
Print #1, "Date fin du projet : " & ![PROJ_INFO_FINISH_DATE]
Print #1, "Nom du Manager : " & ![PROJ_PROP_MANAGER]
Print #1, "Heure de début par défaut projet : " &
![PROJ_OPT_DEF_START_TIME]
Print #1, "Heure de fin par défaut projet : " &
![PROJ_OPT_DEF_FINISH_TIME]
' 2e Requête :
' Lignes à insérer pour calculer le nombre de lignes:
Dim rst_Project As Object
sQuery = "SELECT MSP_PROJECTS.PROJ_NAME as ProjectName, count
(MSP_TASKS.Proj_ID) AS NumberOfTasks FROM MSP_TASKS INNER JOIN MSP_PROJECTS
ON MSP_TASKS.PROJ_ID = MSP_PROJECTS.Proj_ID WHERE(((MSP_TASKS.TASK_ID) Is
Not Null)) AND MSP_PROJECTS.PROJ_ID = " & ![Proj_ID] & " GROUP BY
MSP_PROJECTS.PROJ_NAME, MSP_TASKS.PROJ_ID "
Set rst_Project = CreateObject("ADODB.Recordset")
Set rst_Project = cn.Execute(sQuery, , adCmdText)
If Not rst_Project.EOF Then
Print #1, "Nombre de tâches : " & rst_Project![NumberOfTasks]
End If
rst_Project.Close
Set rst_Project = Nothing
'3e Requête : données d'avancement
sQuery = "select TASK_ACWP, TASK_BCWP, TASK_BCWS ,
TASK_PCT_COMP, TASK_PCT_WORK_COMP FROM MSP_TASKS WHERE PROJ_ID = " &
![Proj_ID] & " AND TASK_OUTLINE_LEVEL = 0"
Set rst_Task = CreateObject("ADODB.Recordset")
Set rst_Task = cn.Execute(sQuery, , adCmdText)
If Not rst_Task.EOF Then
Print #1, "Coût réel du travail effectué : " &
rst_Task![TASK_ACWP]
Print #1, "Coût budgétisé du travail effectué : " &
rst_Task![TASK_BCWP]
Print #1, "Coût budgétisé du travail prévu : " &
rst_Task![TASK_BCWS]
Print #1, "Pourcentage achevé : " & rst_Task![TASK_PCT_COMP]
Print #1, "Pourcentage du travail achevé : " &
rst_Task![TASK_PCT_WORK_COMP]
'
End If
.MoveNext
Wend
rst_Task.Close
Set rst_Task = Nothing
.Close
End With
Set rst = Nothing
Print #1, "********************"
Close #1
MsgBox "Fini : " & i & " plannings dans la Base <<LocalServer>>", , "X58
: Gérard DUCOURET : PragmaSoft ®"
End Sub
