0

In following code I can export 1 line of data from Excel to an Acces DataBase. But how do I have to change my code such that more lines are exported, e.g. all the lines where value in A column is not empty? (And maye there is an easier code?) 

Sub TestAccess()

Dim DONNEE_1 As String
Dim DONNEE_2 As String
Dim DONNEE_3 As String
Dim DONNEE_4 As String

Dim WBK1 As Workbook
Dim WST1 As Worksheet

Set WBK1 = ActiveWorkbook

Set WST1 = WBK1.Worksheets("Pays")

The first row of data I export 

DONNEE_1 = CStr(WST1.Range("A2"))
DONNEE_2 = CStr(WST1.Range("B2"))
DONNEE_3 = CStr(WST1.Range("C2"))
DONNEE_4 = CDate(Now())


Set cn = CreateObject("ADODB.Connection")

name of my destination Access database:

Fichier = "V:\20-STAT-statistiques\WorkDJ\Access test\Test.accdb"

Make a connection with my Excel file: 

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & 
Fichier & ";Persist Security Info=False"
cn.Open

The columns in my Acces database where I want to add data 

Set oCm = New ADODB.Command
oCm.ActiveConnection = cn
oCm.CommandText = "INSERT INTO Voyages (Ville,Pays, Continent, Ajout)" &  
_"VALUES ('" & DONNEE_1 & "','" & DONNEE_2 & "','" & DONNEE_3 & "', #" & 
DONNEE_4 & "#)"


oCm.Execute

cn.Close

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Daphné Janssens
  • 1
  • Easier code, perhaps. Bulk inserts, not really. All inserts typically go through some kind of looping procedure. With you situation, you'd ideally open the connection, and then loop through each portion of data you want to insert and perform the insert before moving to the next portion of data. Its a bit of a bear. – Mike Jul 15 '19 at 14:55
  • Review https://www.askeygeek.com/vba-code-to-export-excel-data-to-access/ – June7 Jul 15 '19 at 19:04
  • Possible duplicate of [Using Excel VBA to export data to MS Access table](https://stackoverflow.com/questions/16161865/using-excel-vba-to-export-data-to-ms-access-table) – June7 Jul 15 '19 at 19:07

0 Answers0