Populate table with data in recordset

[jnevada]

Can I populate a table with the data in a recordset? I have table1 and rst1.

table1.[data1] = rst(0)  '????

Many thanks.

[CreganTur]

Truth be told, the code snippet you provided doesn't tell us anything.

It is possible to move the contents of a recordset into an existing table. There are many ways, but the fastest way I have found it to create a loop that moves through each record of the recordset and then use an INSERT INTO query to load the record into the recordset.

There may be an even faster way than this, but I don't know of any.

The fastest version of an INSERT INTO query (more commonly called an Append query) has this syntax: INSERT INTO [tableName] VALUES ([field values]). Where tableName is the table's name, and field values are the values you want to insert.

You must declare the field values in the exact same order as the fields in the table. Since you are working with a recordset you will fill the parentheses with methods to pull each field value in the correct order. You must remember to wrap each of these field values in the correct data symbol: no symbol is for numbers, a single quote is for string values, and a pound sign (#) is for dates/times.

Here's an example:
[VBA]DoCmd.RunSQL "INSERT INTO Table VALUES ('" & rst.Fields(0).Value & "', #" & _
rst.Fields(1).Value & "#, " & rst.Fields(2).Value & ");"[/VBA]
Fields(0) is of the string data type, Fields(1) is date/time, and Fields(2) is a number data type.

HTH

[jnevada]

Here's an example:
[vba]DoCmd.RunSQL "INSERT INTO Table VALUES ('" & rst.Fields(0).Value & "', #" & _
rst.Fields(1).Value & "#, " & rst.Fields(2).Value & ");"[/vba]
Fields(0) is of the string data type, Fields(1) is date/time, and Fields(2) is a number data type.

HTH

Hi Randy, It works very well and all data is appended to the temp table. Thank you very very much.

But when I try to create another recordset (rst2) from this table, I got error. It seems like the temp table is not recognized. I think I may not reference to this table correctly. Here is my code

[VBA]
DoCmd.RunSQL "Create table TempT ([P_Date] Date, [Value1] double)"

Do until rstA.eof
DoCmd.RunSQL "INSERT INTO TempT VALUES ( #" & rstA.Fields (0).Value & "#, " & rstA.Fields(1).Value & " );"
Loop

strSQL_M = _
"Select Avg(Value1), [P_date] " & _
"From [TempT] " & _ 'error, can't find TempT
"Group by [P_Date] "

With rst2
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
.Open strSQL_M, objConn, adOpenStatic, adLockOptimistic
End With
[/VBA]

Your advise is greatly appreciated.

[CreganTur]

But when I try to create another recordset (rst2) from this table, I got error.

What error?

FYI: In SQL you only need to put brackets around object names that have spaces in them.

[jnevada]

Here's an example:
[vba]DoCmd.RunSQL "INSERT INTO Table VALUES ('" & rst.Fields(0).Value & "', #" & _
rst.Fields(1).Value & "#, " & rst.Fields(2).Value & ");"[/vba]
Fields(0) is of the string data type, Fields(1) is date/time, and Fields(2) is a number data type.

HTH

Randy,

It works very well and all data is appended to the temp table. The only thing that bothers me is the msgbox popup everytime the data is populated to the table. Is there any way to disable the msgbox?

Thanks again

[CreganTur]

Is there any way to disable the msgbox?

What you're seeing is a standard message that Access generates so that the User knows that a change is going to be made to a table's records. It's called a warning. They are very easy to turn off, but you must remember to turn them back on! Here's the code to turn off and on warnings:
[vba]DoCmd.SetWarnings False '<<<Turn warnings off
DoCmd.SetWarnings True '<<<Turn warnings back on[/vba]

You would turn off the warning before your DoCmd.RunSQL statement, and turn them back on before you exit your sub. YOU MUST TURN WARNINGS BACK ON!!! If you turn your warnings off, but never turn then back on then this means that all of Access' warnings will never be seen. This could cause very, very big problems. So always remember to turn warnings back on