add new records to a table

[jamest85]

hi;
I have a recordset which contains some data, now the recordset is there, I want to add those data into a empty table.
I can use "while loop" to grap the data from recordset row by row and add to the table, until .EOF is true.

I am think if it is possible that I can copy the recordset and paste those data to the empty table using a single command?

Thanks.

JT

 

[WayneRyan]

jt,

Where does the recordset come from?

Wayne

 

[jamest85]

jt,

Where does the recordset come from?

Wayne

Here is the partial code:
====================
Dim rs As New ADODB.Recordset
Dim sqlGetCategoryData As String

sqlGetCategoryDate = "Select * from tblTest"
rs.Open sqlGetCategoryDate, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

With rs
If (.EOF) Then
MsgBox "No Record In The Table", vbInformation, "No Record Found"
Else
.MoveFirst
While (Not (.EOF))
' I am adding each row data by calling next function
Call addDataToTblNewHireAnalysis(!dateEntered, !Status, !numCounts)
.MoveNext
Wend
End If
End With
Set rs = Nothing

 

[WayneRyan]

jt,

Does tblTest reside on a SQL Server database?

Do you see it as a Linked Table?

If so, just issue --> Select * Into NewLocalTable From tblTest

Then, you'll have a local table with the data.
If the table exists already (but is empty) --> Insert Into NewLocalTable Select * From tblTest

Wayne

 

[jamest85]

jt,

Does tblTest reside on a SQL Server database?

Do you see it as a Linked Table?

If so, just issue --> Select * Into NewLocalTable From tblTest

Then, you'll have a local table with the data.
If the table exists already (but is empty) --> Insert Into NewLocalTable Select * From tblTest

Wayne

Hi; Wayne
Wow, that is exactly what I want.
1. The database is MSAccess .mdb, no link table, no sql server.
2. The table will be empty before insert.
3. the real sql is like this:
sqlGetCategoryDate = "SELECT tblVantive.[Date Entered] AS dateEntered, tblVantive.Status AS Status, Count(([Status])) AS numCounts FROM tblVantive GROUP BY tblVantive.[Date Entered], tblVantive.Status ORDER BY tblVantive.[Date Entered]"
4. Now how to handle the recordset then? Will be great that there is a complete example?

Thanks again.

JT

 

[WayneRyan]

JT,

You can use:

File --> Get External Data --> Import

Or,

File --> Get External Data --> Link Table

Or,

Insert Into YourLocalTable Select * From RemoteTable In 'C:\SomeDir\Some.MDB'

I still don't know exactly what we're trying to do.

One of those should work, depending on what you want.

Wayne

 

[WayneRyan]

JT,

Do the tables have the SAME structure?

Wayne

 

[jamest85]

JT,

Do the tables have the SAME structure?

Wayne

Same structure, all I want is: how to move a recordset data into a empty table (has same structure with the data in recordset) efficiently.

Thanks.

Jt

 

[WayneRyan]

JT,

DoCmd.RunSQL "Insert Into SomeTable Select * From OtherTable"

Wayne

 

[jamest85]

thanks, but why pop up a message window?

JT,

DoCmd.RunSQL "Insert Into SomeTable Select * From OtherTable"

Wayne

it works great!!! but why pops up a warning message window says" you are about to append 4 row(s). once you click yes, you can't user the undo .......", I didn't ask for the confirmation, how can I disable the popup window?

Thanks Wayne.

JT

 

[boblarson]

Change this:
DoCmd.RunSQL "Insert Into SomeTable Select * From OtherTable"

to this:

CurrentDb.Execute "Insert Into SomeTable Select * From OtherTable"

and that should get rid of the confirmation WITHOUT having to set warnings off.

 

[jamest85]

thanks.

Change this:
DoCmd.RunSQL "Insert Into SomeTable Select * From OtherTable"

to this:

CurrentDb.Execute "Insert Into SomeTable Select * From OtherTable"

and that should get rid of the confirmation WITHOUT having to set warnings off.

it works great!