Hey Guys,

Is there any way to insert a recordset from vba into an access table without doing it one insert at a time? This is how I am doing it now:

While Not rsData.EOF
 strqry = " insert into " & TableName & "(Telephone, Marked) " & _
          " values ('" & rsData("Telephone") & "'," & _
          " " & rsDataMarked") & ")"

        rsAction.Open (strqry)

        rsData.MoveNext
wend

//rsData is my record set that contains my data.

I want to do something like this:
strqry = " insert into tbl_ImportedList (Telephone)" & _
         " select tbl_txtFormat.phone" & _
         " FROM tbl_txtFormat"

         rsInsert.Open (strqry)

In this query, I am inserting everything from table tbl_txtFormat into table tbl_ImportedList in one shot without a loop. Is it possible to insert an entire recordset into a table with one query (ie. without a loop)? The way my code is now takes too long to process looping through insert statement after insert statement. Any information will really help me out. I have been wrestling with this for some time now.

Thanks,

Ryan




nikotromus

__________________
nikotromus

ryan
try this:

INSERT INTO tbl_ImportedList ( Telephone, Marked )
SELECT tbl_txtFormat.phone, tbl_txtFormat.marked
FROM tbl_txtFormat LEFT JOIN tbl_ImportedList ON tbl_txtFormat.ID = tbl_ImportedList.ID
ORDER BY tbl_txtFormat.ID;

john

John,

You listed the code to insert from one table to another. I need the code to insert from a recordset into a table.

Ryan


nikotromus

Ryan,
Iam using an event function -> lostfocus in Access Forms,
that is similar to your situation. I have an Access database
where my datas are imported from the internet as .csv Excel file ext to a gl table. I also have another table called log with fields and values same the as gl table.

After successfully downloading Excel gl datas to Access gl table,
I reconcile/update via function lostfocus in the event properties of the form. Meaning the other fields from the gl table that matches to log table will update those fields from the log table.

The only thing odd is I do it in access form manually, select the field with this function then click next record button.
I just havn't finished doing all this automatically.

Let me know if this recordset code is what you wanted.

john