Pb_requete_access_VBA

Question

Bonjour j'ai un problème de syntaxe dans ma requête et je ne vois pas d'ou cela provient, pourriez vous m'aider svp.

COde : les types de ma table sont Date pour DateTest , et varchar(90) pour Code et valeur.

Sub Insere_Lignes_Generique()

Dim col As Integer, lig As Long, i As Long

Dim BDD As String, Head As String, S As String, Req As String, Nomtable As String, IntReq

Dim Cnx As Object, Rst As Object

Dim A As Date, B As String, C As String, D As String, E As Double

Dim nbrow

Dim ListeNomColonnes(250) As String

Dim ListeLignes(250, 250) As String

Dim j As Integer

Dim nbcol As Integer, nblig As Integer

Dim Valuesreq As String

BDD = ActiveWorkbook.Path & "\BaseTestGenerique.accdb"

Nomtable = UserForm2.TextBox1.Value

With ActiveSheet

nbcol = Range("A2").SpecialCells(xlCellTypeLastCell).Column

nblig = Range("A2").SpecialCells(xlCellTypeLastCell).Row

For i = 1 To nbcol

ListeNomColonnes(i) = Cells(1, i).Value

Next i

IntReq = ""

For i = 1 To nbcol - 1

IntReq = IntReq + ListeNomColonnes(i) + " ,"

MsgBox (IntReq)

Next i

IntReq = IntReq + ListeNomColonnes(nbcol)

MsgBox (IntReq)

For i = 1 To nbcol

If Cells(2, i).Value = "" Then

Cells(2, i).Value = "NULL"

ListeLignes(1, i) = Cells(2, i).Value

Else

ListeLignes(1, i) = Cells(2, i).Value

End If

Next i

For j = 2 To nblig

If Cells(j, 1).Value = "" Then

Cells(j, i).Value = "NULL"

ListeLignes(j, 1) = Cells(j, 1).Value

Else

ListeLignes(j, 1) = Cells(j, 1).Value

End If

Next j

For j = 1 To nblig

For i = 1 To nbcol - 1

Valuesreq = Valuesreq & "'" & ListeLignes(j, i) & "'" & " ,"

Next i

Valuesreq = Valuesreq & "'" & ListeLignes(j, i)

le pb est là , erreur de syntaxe dans la requête !!

Req = "INSERT INTO TableTest2 (" & IntReq & ") VALUES ( '" & Valuesreq & "')"

Set Cnx = CreateObject("ADODB.Connection")

Cnx.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & BDD

Set Rst = Cnx.Execute(Req)

Valuesreq = ""

Next j

Set Cnx = CreateObject("ADODB.Connection")

Cnx.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & BDD

Set Rst = Cnx.Execute(Req)

Cnx.Close

Set Cnx = Nothing

Set Rst = Nothing

End With

End Sub

Invité · Answer

Pour insérer une date dans une table Access, la date doit être : * au format mm/jj/aaaa * entourée de diése Pour le 11 mai 2017 la requête serait donc du genre : Req= "INSERT INTO TableTest2 (DateTest, Code, Valeur) VALUES (#05/11/2017#, 'code machin123', 'valeur bidule456')" Pierre...