Sql
Bonjour le forum
Voilà j'ai besoin de recuperer et traité des données en provenance d'un programme de relevé de température, mais n'ayant jamais fait ca je patauge completement
je voulais savoir si certain d'entre vous ont déjà fait ca et si il peuvent me donner un coup de main.
j'ai dejà quelques elements en main
l'adresse du serveur, et les requettes que j'aimerai tester
j'ai declarer plein de référence en esperant n'avoir rien oublié....
je me suis lancer dans la redaction du code vba
et je suis perdu....
Dim sommaire As Variant
requete_sql = "select app.*"
requete_sql = requete_sql & " from thermo.parametres as app"
requete_sql = requete_sql & " order by app.nom_capteur ASC"
sommaire = ConnectToSql(requete_sql)
mais j'ai deja une erreur sur la ligne sommaire = ConnectToSql(requete_sql)
j'ai recupere une fonction connextion au serveur qui fonctionnait sur l'ancienne
version
Function ConnectToSql(requete_sql As String) As Variant
'Déclaration de la variable de connexion
Dim cnx As ADODB.Connection
Set cnx = New ADODB.Connection
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim nb_col, nb_lig As Integer
'Définition de la chaîne de connexion
'labo = Sheets("Accueil").Cells(5, 2).Value
' Dim olab As New Collection
Dim x%, Server As Variant
x = 1 ' Sheets(1).Cells(7, 2)
If x = 1 Then cnx.ConnectionString = "UID=readonly;PWD=OS.34!ro;DRIVER={MySQL ODBC 5.3 UNICODE Driver};SERVER=10.181.208.71;PORT=33006;DATABASE=thermo;"
If x = 2 Then cnx.ConnectionString = "UID=readonly;PWD=OS.34!ro;DRIVER={MySQL ODBC 5.3 UNICODE Driver};SERVER=10.181.207.190;PORT=33006;DATABASE=thermo;"
'Ouverture de la base de données
cnx.Open
rst.CursorType = adOpenStatic
'requete
rst.Open requete_sql, cnx
'rst.MoveLast
'rst.MoveFirst
'Vérification des erreurs dans le cas d'une mauvaise connexion
If cnx.Errors.Count > 0 Then
'Affichage des erreurs
MsgBox cnx.Errors.Item(0)
InitConnection = False
Exit Function
Else
InitConnection = True
End If
'Exit Function
BadConnection: If cnx.Errors.Count > 0 Then
'Affichage des erreurs
MsgBox cnx.Errors.Item(0)
InitConnection = False
Exit Function
Else
'MsgBox Err.Description
End If
Dim MonRes() As String
Dim i As Integer
i = 0
Dim b As Integer
b = 1
'on compte le nb de données en sortie de requete
While Not (rst.EOF)
rst.MoveNext
i = i + 1
Wend
'on dimensionne le tableau de résultat en conséquence
ReDim MonRes(1 To i + 1, 1 To rst.Fields.Count)
'on rempli le tableau avec les résultats de requete
If i > 1 Then
'rst.MoveLast
rst.MoveFirst
While Not (rst.EOF)
For j = 1 To rst.Fields.Count
MonRes(b, j) = "" & rst.Fields(j - 1)
Next
rst.MoveNext
b = b + 1
Wend
b = b - 1
Else
If i = 1 Then
For j = 1 To rst.Fields.Count
'MsgBox (rst.Fields(j - 1))
rst.MoveFirst
MonRes(1, j) = "" & rst.Fields(j - 1)
Next
End If
End If
'fermeture requete et connexion
rst.Close
If cnx.State = adStateOpen Then
cnx.Close
End If
Set cnx = Nothing
ConnectToSql = MonRes
End Function
Donc ma question est ce quelq'un à du temps à me consacrer pour m'aider
Donc merci pour le ou les volontaires
- Messages
- 1'794
- Excel
- 2010
- Inscrit
- 25/08/2014
- Emploi
- Consultant VB6 / SQL / VBA / Excel / Access
Bonjour,
Il faudrait confirmer dans un premier temps que la connexion à la base fonctionne :
Le message d'erreur est-il lors de l'ouverture de la connexion (cnx.Open) ou après ?
Cas 1 : chaîne de connexion / paramètres à revoir
Cas 2 : problème au niveau de la requête.
Pour les références :
> normalement la référence "Microsoft ActiveX Data Objects 2.8 Library" devrait suffire (pour la partie SQL)
Pour la connexion :
> les chaînes de connexion sont propres à chaque SGBD (SQL Server, ACCESS, ...)
> quelques exemples :
> impossible de dépanner pour les valeurs à renseigner, propres à chaque entreprise
> le USER : "READONLY" (?)
> les autres informations (indiquées en clair dans le fichier transmis, c'est un peu dangereux ...)
L'autre solution est de configurer une connexion "ODBC" sur le poste :
> taper "odbc" dans le menu démarrer
> créer et configurer la connexion
> tester la connexion (cela te permettra de vérifier que tu accèdes bien à la base)
> ensuite, on utilisera cette connexion en VBA
Sinon, sur le principe :
> OK pour ce qui est fait
> Mais il est mieux de dissocier la partie connexion, de la partie requêtage :
- Etape 1 : ouverture de la connexion à la base de données (procédure séparée)
- Etape 2 : exécution des différentes requêtes (procédure séparée)
- Etape 3 : fermeture de la connexion à la base de données (procédure séparée)
Bouben
Merci Bouben,
Je viens de tester via
'autre solution est de configurer une connexion "ODBC" sur le poste :
> taper "odbc" dans le menu démarrer
> créer et configurer la connexion
> tester la connexion (cela te permettra de vérifier que tu accèdes bien à la base)
> ensuite, on utilisera cette connexion en VBA
Connection succesfull !
avec en passwrd= OS.34!ro
Donc par quoi je commence :
- Etape 1 : ouverture de la connexion à la base de données (procédure séparée)
Function ConnectToSql(requete_sql As String) As Variant
'Déclaration de la variable de connexion
Dim cnx As ADODB.Connection
Set cnx = New ADODB.Connection
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim nb_col, nb_lig As Integer
cnx.ConnectionString = "UID=readonly;PWD=OS.34!ro;DRIVER={MySQL ODBC 5.3 UNICODE Driver};SERVER=10.181.208.71;PORT=33006;DATABASE=thermo;"
cnx.Open
Ou il faut aller plus loin ?
Bonjour à tous,
J'avais écrit un article sur la question (avec fichier excel exemple)=>
http://tatiak.canalblog.com/archives/2014/04/06/29605283.html
avec une 2ème partie ici =>
Pierre
ok Merci PierreP56
- Messages
- 1'794
- Excel
- 2010
- Inscrit
- 25/08/2014
- Emploi
- Consultant VB6 / SQL / VBA / Excel / Access
Voilà des articles bien complets, il n'y a plus qu'à suivre !
Bouben
Bonjour à vous deux,
Merci pour le support mais je n'arrive pas à adapter dans un premier modul j'ai :
Public oConnect As ADODB.Connection
Private Sub ConnectionDB()
Dim S As String
Set oConnect = New ADODB.Connection
S = 1
If S = 1 Then cnx.ConnectionString = "UID=readonly;PWD=OS.34!ro;DRIVER={MySQL ODBC 5.3 UNICODE Driver};SERVER=10.181.208.71;PORT=33006;DATABASE=thermo;"
If S = 2 Then cnx.ConnectionString = "UID=readonly;PWD=OS.34!ro;DRIVER={MySQL ODBC 5.3 UNICODE Driver};SERVER=10.181.207.190;PORT=33006;DATABASE=thermo;"
'Ouverture de la base de données
cnx.Open
oConnect.Open S
End Sub
Dans un second
Sub InsertData()
Dim Rs As ADODB.Recordset
Dim Derligne As Integer, i As Integer
Dim Requete As String
Set Rs = New ADODB.Recordset
Call ConnectionDB
With Sheets(1)
Derligne = .Range("A130000").End(xlUp).Row
For i = 2 To Derligne
Requete = "select app.*"
Requete = Requete & " from thermo.parametres as app"
'Requete = requete_sql & " where app.nom_link='Hematnsl'"
Requete = Requete & " order by app.nom_capteur ASC"
Rs.Open Requete, oConnect, adOpenDynamic, adLockOptimistic
Next
End With
oConnect.Close
Set Rs = Nothing
End Sub
Sub LireData()
Dim Rs As ADODB.Recordset
Dim Derligne As Integer, i As Integer
Dim Requete As String
Dim col As Integer
Set Rs = New ADODB.Recordset
Call ConnectionDB
With Sheets(1)
Derligne = .Range("G65000").End(xlUp).Row
For i = 2 To Derligne
Requete = "SELECT * FROM voitures WHERE id=" & .Cells(i, 7)
Rs.Open Requete, oConnect
If Not (Rs.EOF And Rs.BOF) Then
Rs.MoveFirst
While Not (Rs.EOF)
If Rs.Fields(0) = .Cells(i, 7).Value Then
For col = 1 To 3
.Cells(i, 7).Offset(0, col).Value = Rs.Fields(col)
Next col
End If
Rs.MoveNext
Wend
End If
Rs.Close
Next
End With
oConnect.Close
Set Rs = Nothing
End Sub
Lorsque je lance InsertData j'ai une erreur sur Call ConnectionDB qui est inconnue....
Et je pense que je vais avoir un probleme pour stocker les données de ma requete vu le code qui j'ai fait... mais on en est pas là
Si vous avez une idée, ja'i passé une partie de la nuit à ne pas resoudre ce probleme...
Merci
- Messages
- 1'794
- Excel
- 2010
- Inscrit
- 25/08/2014
- Emploi
- Consultant VB6 / SQL / VBA / Excel / Access
Bonjour,
La procédure "ConnectionDB" est déclarée en privée (private), donc visible uniquement à l'intérieur du module où est elle écrite.
Pour l'appeler depuis un autre module, il faut la passer en public
Public Sub ConnectionDB()
Par ailleurs, l'ouverture de la connexion se fait sur l'objet "cnx" (non déclaré) :
cnx.ConnectionString =
A remplacer par l'objet "oConnect"
Public oConnect As ADODB.Connection
De même, la première ligne est à supprimer :
cnx.Open
oConnect.Open S
Pour éviter d'utiliser des variables non déclarées, je te conseille d'ajouter la ligne suivante, au début de chaque module, feuille, ...
Option Explicit
Cette instruction peut être ajoutée automatiquement (pour les modules futurs), en cochant l'option :
Outils->Options->Editeur->"Déclaration des variables obligatoires".
Bouben
Bonjour à tous,
Voici du code fonctionnel sur mon PC (avec d'autres identifiants sur d'autres bases MySql)
Les identifiants de connections sont à vérifier, ainsi que la rédaction de la requête (est-ce que le nom de la table est ok?), et enfin est-ce que le bon driver est bien installé sur le PC, ici => {MySQL ODBC 5.3 UNICODE Driver}
Le reste est garanti fonctionnel à 100% (copier/coller à partir de mes propres appli)
Option Explicit
' ***********************************************************************
' ***** *****
' ***** CODE PierreP56 : http://tatiak.canalblog.com/ *****
' ***** *****
' ***********************************************************************
Public Cnx As Object, Rst As Object
Public Req As String
Sub Edledle()
Dim T As Variant
Req = "SELECT app.* " & _
" FROM thermo.parametres AS app " & _
" ORDER BY app.nom_capteur ASC"
Connect_MySql 1 ' 1 pour le serveur n°1, ou 2 pour le serveur n°2
T = Select_Db(Req, 1) ' 1 pour avoir l'entête des champs, 0 pour ne pas les avoir
Close_Cnx
ActiveSheet.Range("A1").Resize(UBound(T, 1), UBound(T, 2)) = T
End Sub
' *************************************************************************************************
Function Select_Db(Req As String, Optional Head As Integer = 1) As Variant
Dim T As Variant, Rcd As Variant, f As Integer
Dim lig As Long, Col As Long, i As Long, j As Long
On Error GoTo errhdlr
ReDim Rcd(1 To 1, 1 To 1)
Rst.Open Req, Cnx, 3
lig = Rst.RecordCount
If lig > 0 Then
Rst.MoveFirst
T = Rst.GetRows
Col = Rst.Fields.Count
ReDim Rcd(1 To lig + Head, 1 To Col)
For j = 0 To Col - 1
Rcd(1, j + 1) = Rst.Fields(j).Name
For i = 0 To lig - 1
Rcd(i + 1 + Head, j + 1) = IIf(IsNull(T(j, i)), Null, T(j, i))
Next i
Next j
End If
Select_Db = Rcd
Exit Function
errhdlr:
Rcd(1, 1) = Req & vbCrLf & "Erreur n°" & Err.Number & vbCrLf & Err.Description
Select_Db = Rcd
f = FreeFile()
Open ThisWorkbook.Path & "\Log.txt" For Append As #f
Print #f, Now() & " | " & Rcd(1, 1) & vbCrLf
Close #f
End Function
Sub Connect_MySql(Optional S As Byte = 1)
Dim Srv As String, Db As String
Set Cnx = CreateObject("ADODB.Connection")
Cnx.Provider = "MSDASQL"
Srv = IIf(S = 1, "10.181.208.71", "10.181.207.190")
Db = "DRIVER={MySQL ODBC 5.3 UNICODE Driver};SERVER=" & Srv & _
";DATABASE=thermo;USER=readonly;PASSWORD=OS.34!ro;Option=3"
Cnx.Open Db
Set Rst = CreateObject("ADODB.Recordset")
End Sub
Sub Close_Cnx(Optional x As Byte)
On Error Resume Next
If x > 0 Then Rst.Close
If Cnx_IsOpen Then Cnx.Close
Set Cnx = Nothing
Set Rst = Nothing
End Sub
Function Cnx_IsOpen() As Boolean
On Error Resume Next
Cnx_IsOpen = (Cnx.State = 1)
End Function
Merci Bouben je suis un boulet pour le private / puplic par contre le reste j'étais passé à coté !
Donc ca fonctionne mieux mais je n'arrive pas à écrit les valeurs recuperés
Sub InsertData()
Dim Rs As ADODB.Recordset
Dim Derligne As Integer, i As Integer
Dim Requete As String
Set Rs = New ADODB.Recordset
Call ConnectionDB
With Sheets(1)
Derligne = .Range("A130000").End(xlUp).Row
For i = 2 To Derligne
Requete = "select app.*"
Requete = Requete & " from thermo.parametres as app"
' Requete = requete_sql & " where app.nom_link='Hematnsl'"
Requete = Requete & " order by app.nom_capteur ASC"
Requete = Requete & " insert into app.nom_capteur ASC VALUES(" & .Cells(i, 1) & ", '" & _
.Cells(i, 2) & "', '" & _
.Cells(i, 3) & "', " & _
.Cells(i, 4) & ")"
Rs.Open Requete, oConnect, adOpenDynamic, adLockOptimistic
Next
End With
oConnect.Close
Set Rs = Nothing
End Sub
Je penses que ma récuperetion de donnée est foireuse ( vu que rien ne s'ecrit) alors que la connection se fait bien maintenant, je pense qu'il faudrait que je stock les données dans un tableau?
J'ai un fichier excel recuperer avec le détail des tables de la base, et je voudrais dans un premier temps recuperer les valeur nom_link et les nom_capteur associé
Merci pour votre aide
Merci Pierre je regarde egalment ton code et test merci !
Re Pierrep56
Je viens de tester ton code qui fonctionne apparement il manquait juste le port que j'ai ajouté,
Mais je n'ai toujours rien qui s'écrit..... ActiveSheet.Range("A1").Resize(UBound(T, 1), UBound(T, 2)) = T
Comment je peut verifiéque des données sont bien récuperé??
Merci
- Messages
- 1'794
- Excel
- 2010
- Inscrit
- 25/08/2014
- Emploi
- Consultant VB6 / SQL / VBA / Excel / Access
Bonjour à tous !
Ci-dessous la procédure LireData2 modifiée, pour récupérer les données de la table [MaTable] dans l'onglet [XXXXX].
A adapter et tester !
Private Sub LireData2()
Dim oShR As Worksheet
Dim Requete As String
Dim oRS As Recordset
Dim iCol As Integer
'connexion à la base
ConnectionDB
Requete = "SELECT * FROM [MaTable] "
'onglet destination
Set oShR = Worksheets("XXXXX")
'exécute la requête, résultat dans un RecordSet
Set oRS = oConnect.Execute(Requete)
'copie les données du recordset dans l'onglet
oShR.Range("A2").CopyFromRecordset oRS
'titres de colonnes
For iCol = 0 To oRS.Fields.Count - 1
oShR.Cells(1, iCol + 1).Value = oRS.Fields(iCol).Name
Next iCol
Set oRS = Nothing
Set oShR = Nothing
'fermeture de la connexion
oConnect.Close
Set oConnect = Nothing
End Sub
Bouben
Merci Bouben
Ca fonctionne parfaitement, juste une question est il possible de selectionner les champs à rappartier?
- Messages
- 1'794
- Excel
- 2010
- Inscrit
- 25/08/2014
- Emploi
- Consultant VB6 / SQL / VBA / Excel / Access
Hello,
Pour sélectionner seulement certaines colonnes de la table :
Requete = "SELECT [Champs1], [Champs2] FROM [MaTable] "
Le nom de colonne peut être préfixé avec le nom de la table, notamment s'il y a plusieurs tables, avec des jointures, et que 2 colonnes ont le même nom dans 2 tables différentes.
Requete = "SELECT [MaTable].[Champs1], [MaTable].[Champs2] FROM [MaTable] "
Bouben
Je test merci ! vous etes trop fort
Je ferme pas le sujet tout de suite car je sens que je encore besoin de vaux lumieres, car je vais devoir verifier que ma table est toujours à jour à un moment ou à un autre
Finalement je reviens plus vite que je ne pensais.
Bon voilà j'ai pas reussi charger que les champs qui m'interresse
Cette opération devra me permermettre de en fonction des secteur ( nom_link) selectionné dans l'userform de lancer une requete pour n'appeller que les champs de l'onglet demande correspondant au secteur définie par l'utilisateur.
Donc ma premiere question est il possible de charger l'userform sans avoir à écrir la table? mais directectement depuis la requete ?
Merci pour vos reponses
Rebonjour, Mon dimanche n'est manifestement pas le jour de la revelation,
j'ai beau avoir parcourru des dizaines de site je ne parvient pas faire ma requete que sur le champs nom_link
Requete = "SELECT [parametres.nom_link], FROM [thermo.parametres]
Il doit manifestement me manquer quelques choses...
Et je comprend pas pourquoi
Requete = "SELECT * "
Requete = Requete & " parametres.nom_link"
Requete = Requete & " from thermo.parametres as app"
me ramene l'ensemble de la table et pas que le champs nom_link.
si vous avez des explications,
Je trouve dommage de devoir charger 47 colonnes pour recuperer 6 colonnes pour mon utilisation futur.