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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Banterista - 06 Dec 2005 13:29 GMT

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?

Reply to this Message

Allen Browne - 06 Dec 2005 14:08 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>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
[quoted text clipped - 4 lines]
> content and the subscripts of the arrays are all ok. What is the SQL
> statement to achieve this or is there another method?

Reply to this Message

Banterista - 06 Dec 2005 14:26 GMT

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.

> You may be able to INSERT the values using:
> INSERT INTO Table1 (Field1, Field2) SELECT ...
[quoted text clipped - 10 lines]
> > content and the subscripts of the arrays are all ok. What is the SQL
> > statement to achieve this or is there another method?

Reply to this Message

Brendan Reynolds - 06 Dec 2005 15:35 GMT

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

Signature

Brendan Reynolds

> 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
[quoted text clipped - 19 lines]
>> > content and the subscripts of the arrays are all ok. What is the SQL
>> > statement to achieve this or is there another method?

Reply to this Message

Marshall Barton - 06 Dec 2005 14:34 GMT

>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
[quoted text clipped - 3 lines]
>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Banterista - 06 Dec 2005 15:41 GMT

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

> >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
[quoted text clipped - 23 lines]
> If you need further assistance doing that, post back with
> more details about the table's fields and the arrays.

Reply to this Message