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
| Sub connect_base_access()
Dim Db As DAO.Database
Dim strSQL As String
Dim derlign As Long, i As Long
'connexion à la base
Set Db = DAO.OpenDatabase("C:\Users\FCL\Desktop\Base test.mdb", False, False)
Range("K1").Replace What:="'", Replacement:="''"
strSQL = "CREATE TABLE Presse (" & Range("A1").Value & " VARCHAR(30)," & Range("B1").Value & " VARCHAR(30)," & Range("C1").Value & " VARCHAR(30)," & Range("D1").Value & " VARCHAR(30)," & Range("E1").Value & " VARCHAR(30)," & Range("F1").Value & " VARCHAR(30)," & Range("G1").Value & " VARCHAR(30)," & Range("H1").Value & " VARCHAR(30)," & Range("I1").Value & " VARCHAR(30)," & Range("J1").Value & " VARCHAR(30)," & Range("K1").Value & " VARCHAR(30)," & Range("L1").Value & " VARCHAR(30)," & Range("M1").Value & " VARCHAR(30)," & Range("N1").Value & " VARCHAR(30)," & Range("O1").Value & " VARCHAR(30)," & Range("P1").Value & " VARCHAR(30)," & Range("Q1").Value & " VARCHAR(30)," & Range("R1").Value & " VARCHAR(30)," & Range("S1").Value & " VARCHAR(30)," & Range("T1").Value & " VARCHAR(30)," & Range("U1").Value & " VARCHAR(30)," & Range("V1").Value & " VARCHAR(30))"
'exécution de la requête
Db.Execute strSQL
'requête insertion données dans table
'recherche de la dernière ligne pleine de ma feuille
derlign = Cells.Find("*", , , , , xlPrevious).Row
'boucle de la ligne 2 jusque la derniere ligne
For i = 2 To derlign
strSQL = "INSERT INTO Table2 VALUES ('" & Range("A" & i) & "', '" & Range("B" & i) & "')"
Db.Execute strSQL
Next i
'deconnexion de la base
Db.Close
End Sub |
Partager