Transfert d'enregistrements vers access en SQL via VB

**bobby5169** · 16/02/2017, 12h14

Bonjour à tous,
En premier lieu je ne sais pas si c'est le bon en droit pour poster cette demande, on verra bien n'hésitez pas à me le faire savoir.

Je vais essayer d'être le plus clair possible dans ma demande.

Je dois réaliser un fichier de recensement de besoins pour mon service. Pour des raisons obscure de vieilles habitudes et peur du changement, ce fichier sera sous Excel et non sur Access (à mon grand désespoir).

Il y a 3 utilisateurs, chacun avec son fichier Excel en local sur son poste.

Dans ces fichiers Excel un bouton sauvegarde, envoie les données sur une BDD Access située sur un site SharePoint (merci le pack office).

Le fichier de recensement pourra contenir une centaine de lignes environ, et contient actuellement 54 colonnes.

Je pensais transférer toutes les lignes du fichier Excel, vers la BDD Acces en VBA via une requête SQL.

Existe-t'il une requête de type:

Code :

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

1
2
 
INSERT INTO [TABLE_ACCESS]   VALUES (Variable_ARRAY_Excel)

Ou ma variable Array serait l'intégralité de mon tableau Excel.

Sachant que je n'ai pas besoin de clés primaire ni d'identifiant unique dans Access car c'est juste un bête stockage temporaire. Avant chaque ajout de données je supprime toutes les données précédentes concernant l'utilisateur.

Je vous l'accorde ce n'est pas du tout optimisé, mais je bricole avec les moyens du bord.

Je remercie par avance ceux qui prendront le temps de me lire.
Cordialement.

Invité · 16/02/2017, 13h02

bonjour,
déjà précise si les données excel son modifier avant envois vers Access!

si le travail consiste à ouvrir le fichier Excel et à enrichir un basse ACCESS!

Code :

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

.Execute "Insert into [TABLE_ACCESS] select * from [Feuil1$] in 'c:\Fichier.xlsx' 'Excel 12.0;HDR=YES;IMEX=1;'

ça fonctionne dans Access Dao (CurrentDb) ou dans Excel Adodb connexion Access!

**bobby5169** · 16/02/2017, 13h45

Merci pour cette réponse
En gros la logique:

L'utilisateur rempli les données dans les fichier Excel
Il clique sur un bouton
ca ouvre la BDD Access
Je supprime tous les enregistrements correspondants à son nom
Je créé ma variable tableau (données sans en têtes du fichier)
j'injecte la variable tableau

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
 
 
Dim Tbl_envoi As Variant
 
Set Rst_supp = New ADODB.Recordset
Set Rst_ajout = New ADODB.Recordset
 
str_supp = "DELETE * from [T_OP_18_23] WHERE Correspondant= '" & Environ("username") & "'"
 
Rst_supp.Open str_supp, Db_OP, adOpenStatic, adLockOptimistic
 
MsgBox "Suppression des " & Rst_supp.RecordCount & " enregistrements: Terminé"
 
Tbl_envoi = Range(Cells(ligne_depart + 1, 3), Cells(i, j))
 
Db_OP.Execute "INSERT into [T_OP_18_23] select * from " & Tbl_envoi 'En gros j'envoie le tableau d'un coup pour pas faire de boucle en envoyer un par un
 
Unload Me

Je suis parti de ce principe pour ne pas avoir à gérer les N° d'index et les mises à jour juste sur une ligne.

En tous cas je teste ta méthode et je te tiens au courant.

**bobby5169** · 17/02/2017, 12h05

Après plusieurs tests, je n'ai pas réussi à envoyer toutes données d'un seul coup.

Donc plan B, je boucle sur les valeurs de mon tableau dans les 2 dimensions, je créé une chaine de caractère et j'essaie d'envoyer ça dans Access et ...... ben toujours rien.

J'ai une erreur me disant que le point virgule est absent à la fin de l'instruction SQL.

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
Private Sub Btn_Confirmation_Click()
Dim Tbl_envoi As Variant
 
L_i = 1
 
Set Rst_supp = New ADODB.Recordset
Set Rst_ajout = New ADODB.Recordset
 
Tbl_envoi = Range(Cells(ligne_depart + 1, 3), Cells(i, j))
 
'Je boucle sur mes lignes de tableau
For L_i = 1 To UBound(Tbl_envoi)
    str_ajout = str_ajout & "('"
 
            'Je boucle sur mes colonnes (pour ajouter les simples cotes et les virgules
            For C_j = 1 To j - 3
            str_ajout = str_ajout & Tbl_envoi(L_i, C_j) & "', '"
            Next C_j
 
'J'ajoute ma parenthèse fermante et la virgule pour insérer une autre ligne
    str_ajout = str_ajout & "'),"
Next L_i
 
'Je supprime ma dernière virgule
str_ajout = Left(str_ajout, Len(str_ajout) - 1)
 
'J'ajoute mon instruction SQL et mon point à la fin
str_ajout = "INSERT INTO [T_OP_18_23] VALUES " & str_ajout & ";"
 
'J'essaie d'envoyer via un recordset ou en direct via la commande exécute (pour test)
Rst_ajout.Open str_ajout, Db_OP, adOpenStatic, adLockOptimistic
 
'Db_OP.Execute str_ajout
 
Unload Me
 
End Sub

Je pense qu'il ne me manque pas grand chose pour arriver à finir cette fichue requête.

Invité · 17/02/2017, 12h12

bonjour,

Code :

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

1
2
3
4
5
6
7
8
9
With CreateObject("ADODB.Connection")
       .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\données\PERSO\Gérard\SGCB\BDD\SGCB1.accdb;"
      str_supp = "DELETE * from [T_OP_18_23] WHERE Correspondant= '" & Environ("username") & "';"
      .Execute str_supp 
       str_ajout = "INSERT INTO [T_OP_18_23] ([Cham1],[champ2],[ECT]) VALUES " & str_ajout & ";"
      .Execute str_ajout 
      .Execute "Insert into  [T_OP_18_23] select * from [Feuil1$] in 'c:\Fichier.xlsx' 'Excel 12.0;HDR=YES;IMEX=1;'
     .Close End
With

**bobby5169** · 20/02/2017, 11h18

Finalement et malgré tous les efforts de dysorthographie que je remercie d'ailleurs, j'ai finalement opté pour une boucle sur tous mes enregistrements.

A titre d'info j'ai collé 2 timers (en début et fin de boucle) et sur une vingtaines d'enregistrement j'arrive à 0.3s.

Donc au final beaucoup d'énergie pour ne pas gagner grand chose.

Invité · 20/02/2017, 12h15

Bonjour,
si tu fournis une base de données vide et un fichier Excel bidon je te montrerai!

**bobby5169** · 22/02/2017, 15h04

Je vais retester ça avec ton code.

Ce qui marche au final:

Code :

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

1
2
3
4
 
For i = 1 to fin_de_mon_tableau
INSERT INTO [T_OP_18_23] VALUES ('a', 'b', 'c'); 'J'ajoute ligne par ligne les enregistrements
Next i

Ce qui ne marche pas

Code :

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

1
2
 
INSERT INTO [T_OP_18_23] VALUES ('a', 'b', 'c'), ('d', 'e', 'f');

A moins que le code soit

Code :

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

1
2
3
 
INSERT INTO [T_OP_18_23] VALUES ('a', 'b', 'c'), Chr(13)
('d', 'e', 'f');

Invité · 22/02/2017, 16h10

Bonjour,
ça c'est mysql.

Code :

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

1
2
 
INSERT INTO[T_OP_18_23] VALUES ('a', 'b', 'c'), ('d', 'e', 'f');

ça c'est Sql Serveur!

Code :

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

1
2
3
4
For i = 1 to fin_de_mon_tableau
        sql= sql & "INSERT INTO [T_OP_18_23] VALUES ('a', 'b', 'c')" & CbCrlf 'J'ajoute ligne par ligne les enregistrements
 Next i
CNX.excute sql

**bobby5169** · 22/02/2017, 16h12

Je vais poser une question bête mais c'est quoi la différence entre les 2?

Désolé de mon ignorance mais tout ce qui est sql, my_sql, sql_serveur et autre, je ne connais pas du tout

Invité · 22/02/2017, 16h16

Access utilise un script Sql propriétaire! tu peux exécuter les requêtes que une à une!

Code Access Uniquement :

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

1
2
3
4
For i = 1 to fin_de_mon_tableau
INSERT INTO [T_OP_18_23] VALUES ('a', 'b', 'c'); 'J'ajoute ligne par ligne les enregistrements 
Next i

Code Sql Serveur :

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

1
2
3
4
5
6
7
8
9
10
INSERT INTO [T_OP_18_23] VALUES ('a', 'b', 'c')

INSERT INTO [T_OP_18_23] VALUES ('a', 'b', 'c')

INSERT INTO [T_OP_18_23] VALUES ('a', 'b', 'c')

INSERT INTO [T_OP_18_23] VALUES ('a', 'b', 'c')

INSERT INTO [T_OP_18_23] VALUES ('a', 'b', 'c')

Code MySql :

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

1
2
3
4
5
INSERT INTO [T_OP_18_23] VALUES ('a', 'b', 'c'),
 ('a', 'b', 'c'),
 ('a', 'b', 'c'),
 ('a', 'b', 'c'),
 ('a', 'b', 'c')

**bobby5169** · 22/02/2017, 16h19

Au moins c'est très clair.

Encore merci du temps que tu as pris pour me répondre et m'expliquer.

**bobby5169** · 22/02/2017, 16h21

Ca me va j'ai une clio !!!

J'essaie de me former tout seul et c'est vrai que malgré la qualité des tutos et autres forum sur ce site, tout n'est pas facilement assimilable.

**Marc-L** · 22/02/2017, 16h36

Désolé, j'ai effacé mon message quand j'ai vu qu'entre-temps Robert (Salut !) t'avais déjà apporté une réponse technique …

(je comparais une Clio diesel et une Ferrari, toutes les deux sont des voitures mais attention au carburant !
Ici c'est pareil, la manière d'injecter du code SQL diffère d'un logiciel à un autre.)

Invité · 22/02/2017, 19h02

Envoyé par dysorthographie

Bonjour,
si tu fournis une base de données vide et un fichier Excel bidon je te montrerai!

Ça tient toujours

**bobby5169** · 23/02/2017, 10h05

Ci joint une BDD bidon et un excel bidon.

Sur mon excel (celui sur lequel je travaillais) le problème venait aussi du fait que les premiers enregistrements commençaient à la ligne 24 ( certaines des 23 premières lignes étaient réservées pour les listes de choix dans des cellules)

Reqt.zip

Invité · 23/02/2017, 11h45

Bonjour,
je n'est pas réussi à ouvrir la basse Access, tu as sens doute une version plus récente que la mienne mais c'est pas grave!

Code :

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

1
2
3
4
5
6
7
8
9
10
Sub test()
ThisWorkbook.Save
With CreateObject("Adodb.connection")
    .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test sql.accdb" & ";"
    .Execute "DELETE [T_OP_18_23].* FROM [T_OP_18_23];"
    Sql = "INSERT INTO [T_OP_18_23] select * from [Table1$]  in '" & ThisWorkbook.FullName & "'  'Excel 12.0;HDR=YES;IMEX=1;'"
    Set Rs = .Execute(Sql)
    .Close
End With
End Sub

Transfert d'enregistrements vers access en SQL via VB [XL-2010]

Macros et VBA Excel

Discussions similaires

Partager

Partager