How do I insert values from Arrays into a table using SQL

[Guest]

I have created several arrays from which I want to create a table in a VB
routine. Within this routine, I have created a new table successfully and now
I wish to populate the table with data from the arrays. The SQL statement
INSERT INTO always seems to require values or select data from an existing
query or table. I keep getting errors from my array data. I know the data
content and the subscripts of the arrays are all ok. What is the SQL
statement to achieve this or is there another method?

 

[Allen Browne]

You may be able to INSERT the values using:
INSERT INTO Table1 (Field1, Field2) SELECT ...

OTOH, if you have a whole array of values to insert, it would probably be
more efficient to OpenRecordset, AddNew, and Update.

 

[Guest]

The INSERT INTO command doesn't seem to work with array variables, so i'll
try your other suggestion as I have a whole array of variables. I'd still
like to know for definite whether there is a way of doing this via SQL.

 

[Marshall Barton]

I have created several arrays from which I want to create a table in a VB
routine. Within this routine, I have created a new table successfully and now
I wish to populate the table with data from the arrays. The SQL statement
INSERT INTO always seems to require values or select data from an existing
query or table. I keep getting errors from my array data. I know the data
content and the subscripts of the arrays are all ok. What is the SQL
statement to achieve this or is there another method?

You would have to use VBA code to construct (via
concatenation) the Insert Into ... Values(...) form of the
query.

BUT, I think this is one of the few situations where using a
query would be the long way around. Try opening a recordset
on the new table and using AddNew to create the records.

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("thename")
For k = 0 To UBound(array)
rs.AddNew
rs!fielda = array(k)
rs.Update
Next k
rs.Close : Set rs = Nothing

If you need further assistance doing that, post back with
more details about the table's fields and the arrays.

 

[Brendan Reynolds]

Here's something I posted recently in response to a similar question ...

Public Sub ArrayToTable()

'for testing only, delete table if exists
On Error Resume Next
CurrentProject.Connection.Execute "DROP TABLE ArrayTest"
On Error GoTo 0

'create table
CurrentProject.Connection.Execute "CREATE TABLE ArrayTest " & _
"(NumberField int PRIMARY KEY)"

'fill array
Dim lngLoop As Long
Dim alngNumbers(9) As Long
For lngLoop = 0 To 9
alngNumbers(lngLoop) = lngLoop
Next lngLoop

'update table
For lngLoop = LBound(alngNumbers) To UBound(alngNumbers)
CurrentProject.Connection.Execute "INSERT INTO ArrayTest " & _
"(NumberField) VALUES (" & alngNumbers(lngLoop) & ")"
Next lngLoop

End Sub

 

[Guest]

Yes that's great thanks, it worked first time!

You would have to use VBA code to construct (via
concatenation) the Insert Into ... Values(...) form of the
query.

BUT, I think this is one of the few situations where using a
query would be the long way around. Try opening a recordset
on the new table and using AddNew to create the records.

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("thename")
For k = 0 To UBound(array)
rs.AddNew
rs!fielda = array(k)
rs.Update
Next k
rs.Close : Set rs = Nothing

If you need further assistance doing that, post back with
more details about the table's fields and the arrays.