VBA : Insérer champs via requête SQL

**lesguignols** · 29/12/2008, 18h39

Bonjour,
j'ai une requête SQL

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MySql = "SELECT * FROM ..."

que j'exécute avec le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**LedZeppII** · 29/12/2008, 19h12

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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+

**lesguignols** · 30/12/2008, 10h03

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Merci d'avance

**LedZeppII** · 30/12/2008, 11h06

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 vb :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

A+

**lesguignols** · 30/12/2008, 13h18

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

**lesguignols** · 30/12/2008, 16h42

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 ?

**LedZeppII** · 30/12/2008, 21h46

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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+

**lesguignols** · 31/12/2008, 10h11

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

**LedZeppII** · 31/12/2008, 12h17

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+

VBA : Insérer champs via requête SQL

VBA Access

Vue hybride

Discussions similaires

Partager

Partager