Insérer des enregistrements Excel sous Access via VBA

Version imprimable

Bonjour,

Je souhaite insérer des données Excel dans une base de données Access via un code VBA. Mon excel contient un tableau avec 5 colonnes : date, réfproduit, marque, quantité, identifiant. Idem sur ma table Access.

Cependant mon code ne marche pas, j'ai l'erreur : "Erreur d'exécution '-2147217913 (80040e07)' - Type de données incompatible dans l'expression du critère".

Sauriez-vous m'aider s'il vous plait?
J'ai bien fait attention à mettre mes données Excel et Access sous le même format. Il semble que la ligne SELECT*FROM ..... WHERE pose problème.

Merci par avance pour votre aide.

Rose

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
Sub WritingWorksheetExcel_Access()
 
    Dim DATABASE As String
    DATABASE = "G:\AccessRose\Basededonnees1.accdb"
 
    Dim cn As Object
    Set cn = CreateObject("ADODB.Connection")
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    Dim rs2 As Object
    Set rs2 = CreateObject("ADODB.Recordset")
    Dim confirm As Integer
    Dim r As Integer
 
    confirm = MsgBox("Voulez-vous mettre à jour la base Access ? ", vbOKCancel)
 
    If confirm <> 1 Then
        Exit Sub
    End If
 
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source=" & DATABASE & ";"
    ' open a recordset
    'num de ligne = 2
    r = 2
    For r = 2 To 10000
        If Range("A" & r).Value = "" Then
            Exit For
        End If
 
        Set rs = New ADODB.Recordset
        Dim statement As String
        statement = "SELECT * FROM   [AccessRose]  " & _
        " WHERE ( (Dates = '" & Range("A" & r).Value & Format(Dates, "dd/mm/yyy") & "') AND (RefProduit = '" & Range("B" & r).Value & "') AND (Marque = '" & Range("C" & r).Value & "') AND (Quantite = '" & Range("D" & r).Value & "') AND (Id = '" & Range("E" & r).Value & "'))"
 
        rs.Open statement, _
            cn, _
            adOpenStatic, _
            adLockOptimistic, _
            adCmdText
 
 
        If rs.EOF Then
            ' create a new record
            ' add values to each field in the record
            rs.AddNew
        End If
 
        With rs
        .Fields("Dates") = Range("A" & r).Value
        .Fields("RefProduit") = Range("B" & r).Value
        .Fields("Marque") = Range("C" & r).Value
        .Fields("Quantite") = Range("D" & r).Value
        .Fields("Id") = Range("E" & r).Value
        .Update
        End With
    Next r
 
 
    cn.Close
    Set cn = Nothing
    rs.Close
    Set rs = Nothing
    rs2.Close
    Set rs2 = Nothing
 
 
End Sub

Bonjour,
effectivement attention aux type de champ de la base en fonction des valeurs excel!

ID numérique? ID=1 ou ID='1' (String); Quantite =10 ou Quantite ='10'

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub WritingWorksheetExcel_Access() 
   Const DATABASE = "G:\AccessRose\Basededonnees1.accdb": Dim R As Integer
    With CreateObject("ADODB.Connection")
        .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DATABASE & ";"
         With .Execute("SELECT * FROM   [AccessRose]")
               For R = 2 To 10000
                    .Filter = "(Dates = '" & Range("A" & R).Value & Format(Dates, "dd/mm/yyy") & "') AND (RefProduit = '" & Range("B" & R).Value & "') AND (Marque = '" & Range("C" & R).Value & "') AND (Quantite = '" & Range("D" & R).Value & "') AND (Id = '" & Range("E" & R).Value & "'))"
                    If .EOF Then .AddNew
                        .Fields("Dates") = Range("A" & R).Value
                        .Fields("RefProduit") = Range("B" & R).Value
                        .Fields("Marque") = Range("C" & R).Value
                        .Fields("Quantite") = Range("D" & R).Value
                        .Fields("Id") = Range("E" & R).Value
                        .Update
                    Next
            .Close
         End With
         .Close
    End With
End Sub

18/05/2017, 10h33
rose35

Merci pour votre aide

Id est sur Excel un numérique et sur Access un numéro automatique.

donc seul id est utile pour filtré puisque univoque!

il ne peut être ni ajouté ni Updaté!

Code:

1
2
3
4
5
6
7
8
9
10
 .Filter = "Id =" & Range("E" & R).Value
                  If .EOF Then .AddNew
                        .Fields("Dates") = Range("A" & R).Value
                        .Fields("RefProduit") = Range("B" & R).Value
                        .Fields("Marque") = Range("C" & R).Value
                        .Fields("Quantite") = Range("D" & R).Value
                       ' .Fields("Id") = Range("E" & R).Value 
                        .Update
                       Range("E" & R).Value= .Fields("Id") 'je récupère dans Excel ID qui s'incrémente automatiquement dans ma table en cas de création (.AddNew)

https://mon-partage.fr/f/LoCWKVjq/

18/05/2017, 10h57
rose35

Après la mise à jour de mon code :Erreur de compilation Next sans For
Pourtant j'ai mon For pour R donc je ne comprends pas

EDIT :
J'ai finalement l'erreur 3001 :

Citation:

Les argument sont de type incorrect ,en dehors des limites autorisées ou en conflit les uns avec les autres.

C'est la ligne

Code:

.Filter ="Id=" & Range("E"&R).Value & ""

Qui beug.

J'ai modifié R entre 2 et 12 et là ça marche mais quand je veux recommencer cela me dit que ce n'est pas possible les mises à jour.
18/05/2017, 11h58
Invité

Id est un champ unique, tu ne peux pas avoir plusieurs fois la même valeurs dans ce champs, si tu as déjà ajouté l'id E2 à E12 tu ne peux pas les rajouter à nouveau ou les modifier. De plus, tu n'as pas du mettre en commentaire la ligne de code qui pose problème comme l'a fait dysorthographie. :)

désolé!

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub WritingWorksheetExcel_Access()   Const DATABASE = "G:\AccessRose\Basededonnees1.accdb": Dim R As Integer, Cn As Object
   Set Cn = CreateObject("ADODB.Connection")
    With Cn
        .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DATABASE & ";"
         With CreateObject("ADODB.Recordset")
         .Open "SELECT * FROM   [AccessRose]", Cn, 1, 3
               For R = 2 To 10000
                     .Filter = "ID=" & IIf(Trim("" & Range("E" & R).Value) = "", "Null", Range("E" & R).Value)
                    If .EOF Then .AddNew
                        .Fields("Dates") = Range("A" & R).Value
                        .Fields("RefProduit") = Range("B" & R).Value
                        .Fields("Marque") = Range("C" & R).Value
                        .Fields("Quantite") = Range("D" & R).Value
                        .Update
                         Range("E" & R).Value = .Fields("Id")
                    Next
            .Close
         End With
         .Close
    End With
End Sub

18/05/2017, 12h00
rose35

Ah effectivement! Mais du coup comment faire pour créer un nouvel en enregistrement Access numéroté 13 si je rajoute une ligne sur Excel à la suite des autres svp?
18/05/2017, 12h09
Invité
c'est prévu là!
Code:

1 2 .Update Range("E" & R).Value = .Fields("Id")
tu laisses Range("E" & R).Value à vide le programme s'en chargera! .Filter = "ID=" & IIf(Trim("" & Range("E" & R).Value) = "", "Null", Range("E" & R).Value)
18/05/2017, 14h54
rose35

Merci beaucoup pour vos aides

Cependant, là des lignes vides se sont ajoutées sur mon Excel et le code tourne tourne... Cela ajoute 10000 lignes en fait. Je vais donc mettre une contrainte : arrêter d'ajouter des enregistrements quand les autres champs (ref, date, ...) sont vides
18/05/2017, 15h07
Invité

voila ton code!

Code:

For R = 2 To 10000

et voila ce qu'il faut mettre!

Code:

For R = 2 To Range("A1").CurrentRegion.Rows.Count
18/05/2017, 15h14
rose35

Parfait, merci beaucoup!! Tout marche