insert an array with INSERT INTO

[filo65]

I'm aware that to append values from an array to a table I should use withing a for loop the "INSERT INTO...." statement.

What I wonder is if there is a way to "paste" the whole array "in once" like I can do in EXCEL or like I can do when I open a table and paste an excel sheet selection.

Any help would appreciate.

filippo

 

[modest]

I wouldn't use INSERT INTO, I'd use DAO, get the recordset and use .addnew/.update within a loop

 

[DB7]

filo, I'm not sure if DAO has the option, to insert an entire array at once but, ADO does.

Dim rec as ADODB.Recordset
Set rec = New ADODB.Recordset

'My syntax is off here
rec.Open "tblCountry", CurrentProject.Connection, acDynamicCursor, acLockOptimistic

rec.AddNew Array(txtCountry, txtCapital, txtCoastLine), Array("Lebanon", "Beirut", "Mditerranean")
rec.Update


rec.close: set rec = nothing

 

[Pat Hartman]

An array is not being added to the table in this case. The array is being used as a coding shortcut so you can condense several lines of code into one.

A recordset is essentially a multi-valued array. The easiest no-code solution is to link to the spreadsheet and use an append query to copy rows from the spreadsheet and append them to a table.

 

[DB7]

Yes, you're right Pat.
An oversight on my part.