VBA - ODBC - Type TEXT dans mysql
Bonjour à tous,
J'ai un petit bout de code qui me permet de récupérer des données dans une base MySQL. Je débute en VBA donc j'ai trouvé ça en farfouillant sur le net et en adaptant à mes besoins.
Sub ProduitDolibarrSQL()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim Categorie As String
Dim ValCat As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Server_Name = "IP_SERVEUR"
Database_Name = "MaBASE"
User_ID = "MonUSER"
Password = "MonMDP"
Sheets("Parametres").Select
ValCat = Range("C4").Value
Select Case ValCat
Case "1"
Categorie = Range("B5").Value
Case "2"
Categorie = Range("B6").Value
Case "3"
Categorie = Range("B7").Value
Case "4"
Categorie = Range("B8").Value
Case "5"
Categorie = Range("B9").Value
Case "6"
Categorie = Range("B10").Value
Case "7"
Categorie = Range("B11").Value
Case "8"
Categorie = Range("B12").Value
Case "9"
Categorie = Range("B13").Value
Case "10"
Categorie = Range("B14").Value
Case "11"
Categorie = Range("B15").Value
Case "12"
Categorie = Range("B16").Value
Case "13"
Categorie = Range("B17").Value
Case "14"
Categorie = Range("B18").Value
Case "15"
Categorie = Range("B19").Value
Case "16"
Categorie = Range("B20").Value
Case "17"
Categorie = Range("B21").Value
Case "18"
Categorie = Range("B22").Value
Case "19"
Categorie = Range("B23").Value
Case "20"
Categorie = Range("B24").Value
Case "21"
Categorie = Range("B25").Value
Case "22"
Categorie = Range("B26").Value
Case "23"
Categorie = Range("B27").Value
Case "24"
Categorie = Range("B28").Value
Case "25"
Categorie = Range("B29").Value
Case "26"
Categorie = Range("B30").Value
Case "27"
Categorie = Range("B31").Value
Case "28"
Categorie = Range("B32").Value
Case "29"
Categorie = Range("B33").Value
Case "30"
Categorie = Range("B34").Value
Case "31"
Categorie = Range("B35").Value
Case "32"
Categorie = Range("B36").Value
Case "33"
Categorie = Range("B37").Value
Case "34"
Categorie = Range("B38").Value
Case "35"
Categorie = Range("B39").Value
Case "36"
Categorie = Range("B40").Value
Case "37"
Categorie = Range("B41").Value
Case "38"
Categorie = Range("B42").Value
Case "39"
Categorie = Range("B43").Value
Case "40"
Categorie = Range("B44").Value
Case "41"
Categorie = Range("B45").Value
Case "42"
Categorie = Range("B46").Value
Case "43"
Categorie = Range("B47").Value
Case "44"
Categorie = Range("B48").Value
Case "45"
Categorie = Range("B49").Value
Case "46"
Categorie = Range("B50").Value
Case "47"
Categorie = Range("B51").Value
Case "48"
Categorie = Range("B52").Value
Case "49"
Categorie = Range("B53").Value
Case "50"
Categorie = Range("B54").Value
Case "51"
Categorie = Range("B55").Value
Case "52"
Categorie = Range("B56").Value
Case "53"
Categorie = Range("B57").Value
Case "54"
Categorie = Range("B58").Value
Case "55"
Categorie = Range("B59").Value
Case "56"
Categorie = Range("B60").Value
Case "57"
Categorie = Range("B61").Value
Case "58"
Categorie = Range("B62").Value
Case "59"
Categorie = Range("B63").Value
Case "60"
Categorie = Range("B64").Value
Case Else
Categorie = ""
End Select
SQLStr = "SELECT c.ref, c.label, c.fk_product_type, c.tosell, c.tobuy, c.description, c.url, c.customcode, c.fk_country, c.accountancy_code_sell, c.accountancy_code_sell_intra, c.accountancy_code_sell_export, c.accountancy_code_buy, c.accountancy_code_buy_intra, c.accountancy_code_buy_export, c.note, c.note_public, c.weight, c.weight_units, c.length, c.length_units, c.width, c.width_units, c.height, c.height_units, c.surface, c.surface_units, c.volume, c.volume_units, c.duration, c.finished, c.price_base_type, c.price, c.price_ttc, c.price_min, c.price_min_ttc, c.tva_tx, c.datec, c.cost_price, d.ref, c.tobatch, c.stock, c.seuil_stock_alerte, c.desiredstock, c.pmp, c.barcode FROM llx_product c JOIN llx_entrepot d ON c.fk_default_warehouse = d.rowid WHERE c.rowid IN (SELECT distinct a.fk_product FROM `llx_categorie_product` a JOIN `llx_categorie` b ON a.fk_categorie = b.rowid WHERE b.label='" & Categorie & "')"
Set Cn = New ADODB.Connection
Cn.Open "Driver={MariaDB ODBC 3.0 Driver};Server=" & Server_Name & ";Database=" & Database_Name & ";Uid=" & User_ID & ";Pwd=" & Password & ";"
rs.Open SQLStr, Cn, adOpenStatic
With Worksheets("Export Dolibarr").Range("a2:az15000")
.ClearContents
.CopyFromRecordset rs
End With
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End SubCe code marche pas trop mal, je récupère bien les infos que je veux suivant la catégorie que je choisi dans la feuille "Parametres" (SELECT CASE).
Mon souci se site au niveau des données récupéré pour le champ c.description
Je récupère toujours le même, c'est à dire, le 1er enregistrement qu'il lit.
La même requête SQL directement dans l'interface de PHPMyAdmin marche très bien et ne pose pas se souci.
En regardant la structure de la table, ce champ est au format TEXT et j'ai l'impression que s'est ça qui pose souci dans Excel par la suite.
Si, dans ma requête, je remplace c.description par SUBSTR(c.description,1,2000) je n'ai plus ce souci, je récupère bien une description différente pour chaque ligne mais je ne sais pas à l'avance combien de caractère j'ai dans ma description. Si je mets un nombre trop élevé, j'ai une erreur (je suppose que le volume de données est trop important pour Excel).
Par contre, si je passe par l'option "Données" puis "Obtenir des données de ODBC" et que je mets dans la case "Instruction SQL" ma requête, je récupère correctement toutes les données. Par contre par ce biais, je ne sais pas comment un paramètre dans ma requete.
Est ce que quelqu'un à déjà été confronté à ce problème? Une piste pour le résoudre?
Merci par avance
Bonjour BourbonSid,
Pour commencer, voici ton code optimisé, pour moi inutile d'utiliser un "Select Case" dans ces conditions
Sub ProduitDolibarrSQL()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim Categorie As String
Dim ValCat As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Server_Name = "IP_SERVEUR"
Database_Name = "MaBASE"
User_ID = "MonUSER"
Password = "MonMDP"
Sheets("Parametres").Select
ValCat = Range("C4").Value
' Choix de la catégorie
If cnum(ValCat) >= 1 And cnum(ValCat) <= 64 Then
Categorie = Range("B" & 4 + cnum(ValCat))
Else
Categorie = ""
End If
' Requête SQL
SQLStr = "SELECT c.ref, c.label, c.fk_product_type, c.tosell, c.tobuy, c.description, c.url, c.customcode, c.fk_country, c.accountancy_code_sell," _
& "c.accountancy_code_sell_intra, c.accountancy_code_sell_export, c.accountancy_code_buy, c.accountancy_code_buy_intra, c.accountancy_code_buy_export," _
& "c.note, c.note_public, c.weight, c.weight_units, c.length, c.length_units, c.width, c.width_units, c.height, c.height_units, c.surface, c.surface_units," _
& "c.volume, c.volume_units, c.duration, c.finished, c.price_base_type, c.price, c.price_ttc, c.price_min, c.price_min_ttc, c.tva_tx, c.datec, c.cost_price," _
& "d.ref, c.tobatch, c.stock, c.seuil_stock_alerte, c.desiredstock, c.pmp, c.barcode" _
& "FROM llx_product c JOIN llx_entrepot d ON c.fk_default_warehouse = d.rowid" _
& "WHERE c.rowid IN (SELECT distinct a.fk_product FROM `llx_categorie_product` a JOIN `llx_categorie` b ON a.fk_categorie = b.rowid WHERE b.label='" & Categorie & "')"
Set Cn = New ADODB.Connection
Cn.Open "Driver={MariaDB ODBC 3.0 Driver};Server=" & Server_Name & ";Database=" & Database_Name & ";Uid=" & User_ID & ";Pwd=" & Password & ";"
rs.Open SQLStr, Cn, adOpenStatic
With Worksheets("Export Dolibarr").Range("a2:az15000")
.ClearContents
.CopyFromRecordset rs
End With
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub@+
Bonjour à tous,
3 remarques :
* pourquoi utiliser le driver MariaDB sur une base MySql?
Si besoin le driver MySql DRIVER={MySQL ODBC 8.0 Driver}; est dispo ici=>
https://dev.mysql.com/downloads/connector/odbc/
la connexion devient alors :
Cn.Open "Provider=MSDASQL;DRIVER={MySQL ODBC 8.0 Driver};" & _
"SERVER=" & Server_Name & ";" & _
"DATABASE=" & Database_Name & ";" & _
"USER=" & User_ID & ";" & _
"PASSWORD=" & Password & ";"* si ça ne résout pas la question, est-ce qu'un CStr(c.description) ne serait pas utile?
* enfin même remarque que Bruno, se passer du select case (et du Sheets("Parametres").Select) pour simplifier le code
With Sheets("Parametres")
ValCat = .Range("C4").Value
if Valcat=0 or Valcat>60 then Categorie ="" else Categorie = .Range("B" & ValCat+4).Value
end withPierre
Bonsoir,
je n'es pa changer le driver mais je suis d'Accor avec pierrep56!
Sub ProduitDolibarrSQL()
Const Server_Name = "IP_SERVEUR", Database_Name = "MaBASE", User_ID = "MonUSER", Password = "MonMDP"
Dim SQLStr As String
Dim Categorie As String
With Sheets("Parametres")
Categorie = .Range("C4").Offset(Val(.Range("C4").Value)).Value
End With
SQLStr = "SELECT c.ref, c.label, c.fk_product_type, c.tosell, c.tobuy, c.description, c.url, c.customcode, c.fk_country, c.accountancy_code_sell, c.accountancy_code_sell_intra, c.accountancy_code_sell_export, c.accountancy_code_buy, c.accountancy_code_buy_intra, c.accountancy_code_buy_export, c.note, c.note_public, c.weight, c.weight_units, c.length, c.length_units, c.width, c.width_units, c.height, c.height_units, c.surface, c.surface_units, c.volume, c.volume_units, c.duration, c.finished, c.price_base_type, c.price, c.price_ttc, c.price_min, c.price_min_ttc, c.tva_tx, c.datec, c.cost_price, d.ref, c.tobatch, c.stock, c.seuil_stock_alerte, c.desiredstock, c.pmp, c.barcode FROM llx_product c JOIN llx_entrepot d ON c.fk_default_warehouse = d.rowid WHERE c.rowid IN (SELECT distinct a.fk_product FROM `llx_categorie_product` a JOIN `llx_categorie` b ON a.fk_categorie = b.rowid WHERE b.label='" & Categorie & "')"
With New ADODB.Connection
.Open "Driver={MariaDB ODBC 3.0 Driver};Server=" & Server_Name & ";Database=" & Database_Name & ";Uid=" & User_ID & ";Pwd=" & Password & ";"
Worksheets("Export Dolibarr").Range("a2:az15000").ClearContents
Worksheets("Export Dolibarr").Range("A2").CopyFromRecordset .Execute(SQLStr)
.Close
End With
End SubBonjour,
Merci pour vos retours et les conseils pour l'optimisation du code.
Je ne m'étais pas encore penché sur ce sujet, je voulais deja faire en sorte que le reste fonctionne :)
Concernant le driver ODBC, s'est bien le bon. Je parle de MySQL par habitude mais s'est bien un serveur MariaDB.
Dans le doute, j'ai refais une petite installation de serveur avec MySQL cette fois et j'ai modifié le driver en conséquence mais le résultat est le même.
Concernant l'utilisation de CStr(c.description), ça me fait l'erreur si dessous. J'ai tenté avec CVar(c.description) mais le résultat est le même.
Mon 1er message n'était peut être pas très clair, je complète avec 2 captures d’écran. La 1ère est ma requête directement dans PHPMyAdmin. On voit bien que pour chaque ligne de processeur j'ai une description différente. La 2eme est le résultat du code que je vous ai mis dans Excel. La description reste bloqué sur celle de la 1ere ligne
Bonjour,
Je suis dubitatif sur ton type de jointure !
https://www.commentcamarche.net/faq/34631-mysql-quelle-jointure-choisir
je t'invite à utiliser Inner Join!
ici FROM `llx_categorie_product` a JOIN `llx_categorie` b ON a.fk_categorie = b.rowid
tu demande à ta requête d'associer toutes les ligne de la table llx_categorie_product à toutes les ligne de la table llx_categorie et c'est ce que fait ta requête!
le nombre d'enregistrement est count(llx_categorie_product) * count(llx_categorie )
Merci pour ton retour sur la requête.
Effectivement elle n'est pas juste, mais j'ai mis ça de coté quand je me suis rendu compte de l'autre souci.
J'irai creusé les Inner Join dès que je reviendrai sur cette partie la :)
Bonjour le fil
@BourbonSid, je pense que justement, votre problème est lié à ce INNER JOIN
@+
J'ai remplacé ma requete par
SELECT * FROM llx_productpour éviter tout souci à ce niveau la.
Le résultat est le même, je récupère bien mes données, sauf la colonne description, ou j'ai toujours celle de la 1er ligne
tu fais ça?
With Worksheets("Export Dolibarr").Range("a2:az15000")
.ClearContents
.CopyFromRecordset rs
End Withou tu fais ça?
With New ADODB.Connection
.Open "Driver={MariaDB ODBC 3.0 Driver};Server=" & Server_Name & ";Database=" & Database_Name & ";Uid=" & User_ID & ";Pwd=" & Password & ";"
Worksheets("Export Dolibarr").Range("a2:az15000").ClearContents
Worksheets("Export Dolibarr").Range("A2").CopyFromRecordset .Execute(SQLStr)
.Close
End WithPour le moment, je fais ça. J'ai pris en compte les remarques pour l'optimisation et j'ai fais une requete beaucoup plus simple pour etre sur que le souci ne vient pas de la
Sub ProduitDolibarrSQL()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim Categorie As String
Dim ValCat As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Server_Name = "192.168.200.9"
Database_Name = "dolitest"
User_ID = "dolibarrExcel"
Password = "11111"
With Sheets("Parametres")
ValCat = .Range("C4").Value
If ValCat = 0 Or ValCat > 60 Then Categorie = "" Else Categorie = .Range("B" & ValCat + 4).Value
End With
SQLStr = "SELECT * FROM llx_product"
Set Cn = New ADODB.Connection
Cn.Open "Driver={MariaDB ODBC 3.0 Driver};Server=" & Server_Name & ";Database=" & Database_Name & ";Uid=" & User_ID & ";Pwd=" & Password & ";"
rs.Open SQLStr, Cn, adOpenStatic
With Worksheets("Export Dolibarr").Range("a2:az15000")
.ClearContents
.CopyFromRecordset rs
End With
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End SubVoici le résultat sur une feuille Excel. Les données sont bien récupérées, sauf la colonne ou il y a la description
En modifiant le code comme ça
Sub ProduitDolibarrSQL()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim Categorie As String
Dim ValCat As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Server_Name = "192.168.200.9"
Database_Name = "dolitest"
User_ID = "dolibarrExcel"
Password = "11111"
With Sheets("Parametres")
ValCat = .Range("C4").Value
If ValCat = 0 Or ValCat > 60 Then Categorie = "" Else Categorie = .Range("B" & ValCat + 4).Value
End With
SQLStr = "SELECT * FROM llx_product"
' Set Cn = New ADODB.Connection
' Cn.Open "Driver={MariaDB ODBC 3.0 Driver};Server=" & Server_Name & ";Database=" & Database_Name & ";Uid=" & User_ID & ";Pwd=" & Password & ";"
' rs.Open SQLStr, Cn, adOpenStatic
With New ADODB.Connection
.Open "Driver={MariaDB ODBC 3.0 Driver};Server=" & Server_Name & ";Database=" & Database_Name & ";Uid=" & User_ID & ";Pwd=" & Password & ";"
Worksheets("Export Dolibarr").Range("a2:az15000").ClearContents
Worksheets("Export Dolibarr").Range("A2").CopyFromRecordset .Execute(SQLStr)
.Close
End With
' With Worksheets("Export Dolibarr").Range("a2:az15000")
' .ClearContents
' .CopyFromRecordset rs
' End With
' rs.Close
' Set rs = Nothing
' Cn.Close
' Set Cn = Nothing
End SubLa description n'est même plus récupéré. D'ailleurs il récupère plus aucune colonne après la colonne description
d'une par ça c'est pas bon!
With Worksheets("Export Dolibarr").Range("a2:az15000")
.ClearContents
.CopyFromRecordset rs
End Withça c'est bon
With Worksheets("Export Dolibarr")
.Range("a2:az15000").ClearContents
.Range("a2).CopyFromRecordset rs
End Withllx_product c'est une table ou une vue car selon ton Imprim écran il n'y a pas quel ma même ligne!
La description n'est même plus récupéré. D'ailleurs il récupère plus aucune colonne après la colonne description
c'est normal si elle fait partie de la deuxième table!
Je crois que nos messages se sont croisés ^^
llx_product s'est le nom de la table dans MariaDB.
Dans un post précédent, j'ai mis une capture de ma feuille Excel et une capture de PhpMyAdmin avec la même requête lancé dans les 2. Le résultat n'était pas le même
Je viens de tester autre chose, peut etre que ça fera avancer le truc
Avec l'enregistreur de macro, j'ai fais ça
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveWorkbook.Queries.Add Name:="Requête1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Odbc.Query(""dsn=DoliTest"", ""SELECT * FROM llx_product"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Source"
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Requête1;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Requête1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Requête1"
.Refresh BackgroundQuery:=False
End With
End SubEt j'obtiens dans Excel ça. Avec cette façon de faire, j'ai bien une description différente par produit.
Bonjour,
Après des heures à chercher et à faire des tests, j'ai finalement résolu mon souci.
Il suffisait d'utiliser un autre connecteur ODBC
Je suis passé du "MariaDB ODBC 3.0 Driver" à "MySQL ODBC 8.0 ANSI Driver" et maintenant je récupère bien correctement les données de la colonne Description...
Merci à tous pour vos conseils
Effectivement, je vais garder cette info dans un coin de ma mémoire
Mais, comme je l'avais dis au départ, je suis bien sur une base MariaDB, d’où le connecteur MariaDB utilisé au départ...
Finalement j'ai crié victoire un peu trop vite...
Je ne récupère que les 66 1er caractères du champs...
Je ne comprends vraiment pas pourquoi ça réagit comme ça
Voici ma requete de base
SELECT c.ref, c.label, c.fk_product_type, c.tosell, c.tobuy, c.description, c.url, c.customcode, c.fk_country, c.accountancy_code_sell, c.accountancy_code_sell_intra, c.accountancy_code_sell_export, c.accountancy_code_buy, c.accountancy_code_buy_intra, c.accountancy_code_buy_export, c.note, c.note_public, c.duration, c.finished, c.price_base_type, c.price, c.price_ttc, c.price_min, c.price_min_ttc, c.tva_tx, c.datec, c.cost_price, d.ref, c.tobatch, c.stock, c.seuil_stock_alerte, c.desiredstock, c.pmp, c.barcode, e.serieYesNo, e.deee, e.cpriv FROM llx_product c INNER JOIN llx_entrepot d ON c.fk_default_warehouse = d.rowid INNER JOIN llx_product_extrafields e ON c.rowid = e.fk_object WHERE c.label LIKE '" & Libelle & "'En faisait ça, je récupère bien la description du produit mais tronqué à 66 caractères.
Par contre, si je cible un produit précis avec cette requete
SELECT c.ref, c.label, c.fk_product_type, c.tosell, c.tobuy, c.description, c.url, c.customcode, c.fk_country, c.accountancy_code_sell, c.accountancy_code_sell_intra, c.accountancy_code_sell_export, c.accountancy_code_buy, c.accountancy_code_buy_intra, c.accountancy_code_buy_export, c.note, c.note_public, c.duration, c.finished, c.price_base_type, c.price, c.price_ttc, c.price_min, c.price_min_ttc, c.tva_tx, c.datec, c.cost_price, d.ref, c.tobatch, c.stock, c.seuil_stock_alerte, c.desiredstock, c.pmp, c.barcode, e.serieYesNo, e.deee, e.cpriv FROM llx_product c INNER JOIN llx_entrepot d ON c.fk_default_warehouse = d.rowid INNER JOIN llx_product_extrafields e ON c.rowid = e.fk_object WHERE c.label LIKE '" & Libelle & "' AND c.rowid = '1137'J'ai la description au complet...
Du coup, pour "contourner" mon souci, je me dis que je pourrai faire une boucle qui exécute la requête pour chaque rowid et copie les données dans Excel ligne par ligne. Mais je trouve pas comment faire?