using an array for the values in a sql insert statement?

[nimarii]

Is it possible to use an array to populate the values of a sql insert statement? i get an error, it seems that access thinks i am calling a function.

(Overall, the situation is that i need to import data from an excel spreadsheet into multiple access tables.
The problem is that only certain columns pertain to certain access tables.

The solution that i thought of was creating recordsets for each table, and storing the values of the recordsets into arrays, then using a SQL insert statement to insert the values into the tables......)

here's some of the code:

Dim varRecords As Variant
strsql = "Select CustomerName, customermainphone, billingaddressln1, billingaddressln2, billingcity, billingstate, billingzip, contactname, contactphone from orderform"
Set rec = db.OpenRecordset(strsql)
rec.MoveLast
intRecords = rec.recordCount
MsgBox intRecords
rec.MoveFirst
varRecords = rec.GetRows(intRecords)

For t = 1 To intRecords
x = 0
DoCmd.RunSQL ("INSERT into t_customers(CustomerName, customermainphone, billingaddressln1, billingaddressln2, billingcity, billingstate, billingzip, contactname, contactphone) VALUES( varRecords(x,y), varRecords(x+1,y), varRecords(x+2,y), varRecords(x+3,y), varRecords(x+4,y), varRecords(x+5,y), varRecords(x+6,y), varRecords(x+7,y), varRecords(x+8,y) ) ")
y = y + 1
rec.MoveNext
Next


i know this is crap, but i'm pretty new to all this, and can't think of any other way.

any advice would be greatly appreciated!!!

 

[Golom]

You should be able to do it in a single statement
[tt]
DoCmd.RunSQL ( _
"INSERT INTO t_customers
(CustomerName, customermainphone, billingaddressln1, " & _
"billingaddressln2, billingcity, billingstate, " & _
"billingzip, contactname, contactphone) " & _

"SELECT CustomerName, customermainphone, billingaddressln1, " & _
"billingaddressln2, billingcity, billingstate, " & _
"billingzip, contactname, contactphone " & _
"FROM orderform ")
[/tt]

 

[baderms]

Nimarii,

I assuming by looking at your query that the information in the array are strings. The problem is the sql statement in incorrectly formatted. It should be like this example.

Dim strSQL  As String

strSQL = "Insert into table(field1, field2) Values ('" & varRecords(x, Y) & "', '" & varRecords(x + 1, Y) & "')"

DoCmd.RunSQL strSQL

 

[nimarii]

baderms, i corrected the format, and it seems that the sql runs ok, but i get an error message...it says that its going to append one row, but then an error message appears that it wasn't able to append due to validation rule violations.
I'm not sure what the problem could be, all the fields in the access table are set as 'text', and i don't have any null values in the recordset either...?
any thoughts on this?

 

[nimarii]

golom,
i also tried your suggestion, and it also worked, however, i am still getting the same validation rule violation error....

 

[nimarii]

somehow i figured out the error message, although since i was trying a million different things, i'm not quite sure what it was that worked. i was messing around with the properties of the tables.
thanks a TON for fixing my sql statement, golom, you saved me from having to loop through each record. tek-tips rocks!

 

[Golom]

You will get validation errors is you attempt to

[li]Duplicate a value in a field or fields with a unique index[/li]
[li]Insert a NULL in a field that doesn't allow NULLs[/li]
[li]Insert a value in a field of the wrong data type[/li]
[li]Insert a value that violates the validation rule for a field[/li]