VBA : Insérer champs via requête SQL

Bonjour,
j'ai une requête SQL

Code:

---

MySql = "SELECT * FROM ..."

---

que j'exécute avec le code

Code:

---

1 2	MySql = "SELECT * FROM ..." Set rs = cnx.Execute(MySql)

---

Ensuite j'efface le contenu de ma table ACCESS avec la requête

Code:

---

DoCmd.RunSQL "DELETE * FROM T_TMP "

---

Ma question est la suivante :
J'ai envie de remplir ma table T_TMP qui contient 27 colonnes avec tous les enregistrements de la requête SQL rexécuté avec le rs ci-dessus. Le souci c'est que 27 colonnes c'est beaucoup; càd que je ne pourrai pas mettre :

Code:

---

1 2 3 4 5 6 7

If rs.EOF Then Exit Sub rs.MoveFirst While Not (rs.EOF)   DoCmd.RunSQL "INSERT INTO T_TMP (col1, col2...,col27) VALUES ('" + rs.Fields(1) + "','" + rs.Fields(2) + "'...'" + rs.Fields(27) + "')" rs.MoveNext Wend

---

il doit y avoir des solution avec des fld.name que je ne MAITRISE PAS malheureusement...
HELP PLZ

Bonjour,

Voici un exemple qui copie les 10 premiers enregistrements de la table Commandes dans la table Cmdes2.
Les deux tables ont les mêmes champs, et ils sont dans le même ordre.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

Dim cnx As ADODB.Connection Dim rs As ADODB.Recordset, rs2 As ADODB.Recordset Dim i As Integer   Set cnx = CurrentProject.Connection Set rs = cnx.Execute("SELECT TOP 10 * FROM Commandes")   Set rs2 = New ADODB.Recordset rs2.CursorLocation = adUseClient rs2.Open "Cmdes2", cnx, adOpenStatic, adLockOptimistic   Do While Not rs.EOF rs2.AddNew ' Copie de tous les champs For i = 0 To rs.Fields.Count - 1 rs2.Fields(i) = rs.Fields(i) Next ' Enregistrement source suivant rs.MoveNext Loop If rs2.EditMode <> adEditNone Then rs2.Update   rs2.Close Set rs2 = Nothing rs.Close Set rs = Nothing Set cnx = Nothing

---

A+

Merci LedZeppII pour ta réponse, mais je pense que tu as compris ma question à l'envers. En effet, en écrivant

Code:

---

1 2 3

rs2.Open "Cmdes2", cnx, adOpenStatic, adLockOptimistic Do While Not rs.EOF rs2.AddNew

---

tu remplis plutôt la table dans SQL mais pas dans access.
Comment donc modifier ton code qui a l'air bon pour injecter les données d'une table T_SQL dans une table T_ACCESS.

P.S : STP, à quoi servent les lignes :

Code:

---

1 2	rs2.CursorLocation = adUseClient ........, cnx, adOpenStatic, adLockOptimistic

---

Merci d'avance

Bonjour,

Mon exemple copie les données d'une table Access dans une autre table Access.
Je n'ai donc qu'un seul objet Connection vers Access.
Dans ton cas il faut utiliser deux objets Connection.
Une connexion vers le système de base de données externe et une connexion avec la base de données Access.

Voici mon exemple remanié dans ce sens:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

Dim cnx As ADODB.Connection, rs As ADODB.Recordset Dim cnAcc As ADODB.Connection, rsAcc As ADODB.Recordset Dim i As Integer   ' Ouvrir connexion sur base distante Set cnx = New ADODB.Connection cnx.Open "Provider=SQLOLEDB;Data Source=xxxx; etc..." ' Ouvrir recordset sur données base distante Set rs = cnx.Execute("SELECT TOP 10 * FROM Commandes")   ' Référencer connexion Access existante dans le projet Set cnAcc = CurrentProject.Connection ' Créer recordset Set rsAcc = New ADODB.Recordset ' Ouvrir recordset sur table Access rsAcc.CursorLocation = adUseClient rsAcc.Open "Cmdes2", cnAcc, adOpenStatic, adLockOptimistic   ' Parcourir enregistrements du recordset base distante Do While Not rs.EOF ' Ajouter un nouvel enregistrement dans le recordset ' sur table Access rsAcc.AddNew ' Copier tous les champs For i = 0 To rs.Fields.Count - 1 rsAcc.Fields(i) = rs.Fields(i) Next ' Enregistrement source suivant rs.MoveNext Loop ' S'assurer que l'enregistrement en cours a été sauvegardé If rsAcc.EditMode <> adEditNone Then rsAcc.Update   ' Libération des objets ' 1. Access rsAcc.Close Set rsAcc = Nothing Set cnAcc = Nothing ' 2. Base externe rs.Close Set rs = Nothing cnx.Close Set cnx = Nothing

---

A propos de ces lignes ...

Code:

---

1 2 3 4 5

' Créer recordset Set rsAcc = New ADODB.Recordset ' Ouvrir recordset sur table Access rsAcc.CursorLocation = adUseClient rsAcc.Open "Cmdes2", cnAcc, adOpenStatic, adLockOptimistic

---

Je crée un nouvel objet recordset.
Je demande à ce que le curseur soit côté client, plutôt que côté serveur.
Ensuite j'ouvre le recordset, en précisant le nom de la table Access, l'objet Connection à utiliser, le type de curseur (Statique), ainsi que le mode de vérouillage (Optimiste).
L'ensemble de ces paramètres me garanti que le recordset (OLEDB.Jet) sera modifiable, c'est à dire que je pourrai ajouter des enregistrements, en modifier ou en supprimer.
Je me sert de tuto/aide mémoire http://ledzeppii.developpez.com/fonctionnalite-rs-ado/ pour choisir les paramètres en fonction des besoins.

Si d'avanture les champs entre la table source et la table destination sont identiques mais pas dans le même ordre,
remplacer

Code:

---

rsAcc.Fields(i) = rs.Fields(i)

---

par

Code:

---

rsAcc.Fields(rs.Fields(i).Name) = rs.Fields(i)

---

A+

Merci encore pour les éclaircicements.
Sauf que maintenant, j'obtiens le message d'erreur :

"Une opération en plusieurs étapes a généré des erreurs. Vérifiez chaque valeur d'état."

l'erreur survient au niveau de

Code:

---

1 2	rsAcc.Fields(i) = rs.Fields(i) Next

---

C'est peut être à cause du nombre de colonnes ?

De plus, comme j'ai des champs qui peuvent être les mêmes sur plusieurs lignes, j'obtiens le message d'erreurs :

Modifications non effectuées : risque de doublons dans champs index.....

Comment remédier à ce deuxième problème SVP ?

Bon, j'ai ajouté une petite gestion des erreurs.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70

Dim cnx As ADODB.Connection, rs As ADODB.Recordset Dim cnAcc As ADODB.Connection, rsAcc As ADODB.Recordset Dim i As Integer, iStep As Integer   ' Ouvrir connexion sur base distante Set cnx = New ADODB.Connection cnx.Open "Provider=SQLOLEDB;Data Source=xxxx; etc..." ' Ouvrir recordset sur données base distante Set rs = cnx.Execute("SELECT TOP 10 * FROM Commandes")   ' Référencer connexion Access existante dans le projet Set cnAcc = CurrentProject.Connection ' Créer recordset Set rsAcc = New ADODB.Recordset ' Ouvrir recordset sur table Access rsAcc.CursorLocation = adUseClient rsAcc.Open "Cmdes2", cnAcc, adOpenStatic, adLockOptimistic   ' Parcourir enregistrements du recordset base distante On Error GoTo ErrH iStep = 1 Do While Not rs.EOF ' Ajouter un nouvel enregistrement dans le recordset ' sur table Access rsAcc.AddNew ' Copier tous les champs For i = 0 To rs.Fields.Count - 1 rsAcc.Fields(i) = rs.Fields(i) ' ou aussi : rsAcc.Fields(rs.Fields(i).Name) = rs.Fields(i) Next ' Enregistrement source suivant rs.MoveNext Loop iStep = 2 ' S'assurer que l'enregistrement en cours a été sauvegardé If Not (rsAcc.BOF Or rsAcc.EOF) Then If rsAcc.EditMode <> adEditNone Then rsAcc.Update End If   ExitProc: ' Libération des objets ' 1. Access If rsAcc.State <> adStateClosed Then rsAcc.Close Set rsAcc = Nothing Set cnAcc = Nothing ' 2. Base externe If rs.State <> adStateClosed Then rs.Close Set rs = Nothing cnx.Close Set cnx = Nothing Exit Sub   ErrH: Select Case Err.Number '0x80004005 doublon Case -2147467259 ' Annuler sauvegarde de l'enregistrement rsAcc.CancelUpdate ' Si phase 1, réitérer AddNew If iStep = 1 Then rsAcc.AddNew ' Reprendre à l'instruction suivante Resume Next Case Else If iStep = 1 Then MsgBox Err.Number & " : " & Err.Description, , rs.Fields(i).Name & " -> " & rsAcc.Fields(i).Name Resume Next End If MsgBox Err.Number & " : " & Err.Description Resume ExitProc End Select

---

En cas de doublon, l'ajout est annulé et on passe à l'enregistrement source suivant.

Si c'est une autre erreur ...

• Dans la boucle :
  Un MsgBox affiche l'erreur avec dans le titre : Champ Source -> Champ Destination
  Puis le programme continue à l'instruction qui suit celle ayant causé l'erreur.
• Après la boucle :
  Un MsgBox affiche l'erreur.
  Puis le programme continue à l'étiquette ExitProc.

J'ai réussi à avoir le message d'erreur "Une opération en plusieurs étapes a généré des erreurs. Vérifiez chaque valeur d'état.",
en faisant exprès d'avoir un champ destination trop petit.
Ex: Long -> Byte

Ça peut être un problème de différence de structure entre table source et table destination.
Ou bien les champs ne sont pas dans le même ordre.

une remarque sur

Code:

---

1 2 3 4

' Copier tous les champs For i = 0 To rs.Fields.Count - 1 rsAcc.Fields(i) = rs.Fields(i) Next

---

Tu as le droit de l'écrire de catte façon:

Code:

---

1 2 3 4 5

' Copier les champs rsAcc.Fields("Champ01") = rs.Fields("Champ01") rsAcc.Fields("Champ01") = rs.Fields("Champ01") ' ....... rsAcc.Fields("Champ27") = rs.Fields("Champ27")

---

A+

Merci encore une fois LedZeppII, ton aide était très utile. En fait c'est bien de rajouter comme ça une gestion d'erreur mais en fin de compte j'ai trouvé d'où venaient ces erreurs ci. Je les cite au cas où quelqu'un en aura besoin plus tard.

La description des variables dans la table SQL n'est pas la même que dans ACCESS. C'est à dire que j'ai mis des variables dans SQL avec "varchar" mais dans ACCESS on n'a le choix que pour "text"; le prix à payer pour que la correspondance marche bien c'est bouffer plus de mémoire dans SQL en choisissant "text".

La deuxième erreur c'est que par défaut, une table ACCESS a pour première colonne un champs "N°" qu'on lui associe "NuméroAuto"; et le remplissage de la table commence donc à ce champs et désordonne par conséquent tous les autres champs et c'est ce qui crée une infinité d'erreurs à gérer...

Merci encore à LedZeppII. Sujet résolu

Bonjour,

Merci pour la description de la source des erreurs.
Je comprends mieux.

D'après moi, varchar et text c'est pareil, à ceci près que varchar n'est pas unicode.
Il faut juste s'assurer que les longueurs de champs (nombre de caractères max) correspondent.
J'ai fait l'essai avec une table SQL Server dont tous les champs sont de type nvarchar, et une table Access dont tous les champs sont de type Text.
Ça fonctionne bien.
Note : J'ai choisi nvarchar parce que ce type de champ est unicode comme le type Text d'Access.
Cela évite les problèmes de pages de code pour certains caractères.

A+