Copy entire contents of a recordset to a table

[AndrewDotto]

Hi Guys,

I've got the below code

Private Sub WorkOrder_AfterUpdate()

Dim strsql As String
Dim rst As ADODB.Recordset
Dim taskid As String
Dim JobDesc As String
Dim sql As String

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection

strsql = "SELECT CMD_SOR_REF,CMD_CURRENT_COMPLETED,CMD_ORIGINAL_QUANTITY,CMD_DESCRIPTION FROM TASKDBA_WM_COMPLETION_DETAIL WHERE CMD_JOB_REF =" & Chr(34) & taskid & Chr(34) & ";"

rst.Open (strsql)
sql = "INSERT INTO TblJobDetails (ID,JobDetails,SOR,Description,ordered,completed) VALUES ('  " & [Forms]![frmresponsivepc]![ID]  & "','" & JobDesc & "','" & rst!cmd_sor_ref & "','" & rst!cmd_description & "'," & Chr(34) & rst!cmd_original_quantity & Chr(34) & "," & Chr(34) & rst!cmd_current_completed & Chr(34) & ");"

rst.MoveFirst

Do Until rst.EOF
     DoCmd.RunSQL (sql)
     Debug.Print rst.RecordCount
     Debug.Print sql
     rst.MoveNext
     Debug.Print sql
Loop
End

rst.Close

I've edited it down for your ease... its a biiiig function. Anyway, the end result i'm wanting is to copy all the records in the recordset into the selected table. When i run it, it only enters the first entry into the table, but enters it times into the table, depending on how many records are in the recordset.

Any Ideas?


Thanks,

Andrew

 

[jzwp22]

You have to create the query each time you step through a record in the recordset. In other words, the following line of code has to be embedded with the recordset loop

sql = "INSERT INTO TblJobDetails (ID,JobDetails,SOR,Description,ordered,completed) VALUES (' " & [Forms]![frmresponsivepc]![ID] & "','" & JobDesc & "','" & rst!cmd_sor_ref & "','" & rst!cmd_description & "'," & Chr(34) & rst!cmd_original_quantity & Chr(34) & "," & Chr(34) & rst!cmd_current_completed & Chr(34) & ");"


I'm not sure why you would need to copy data that is already in a table into another table...

 

[Galaxiom]

Another way is to open a recordset on the destination table and add records to it as you step through the source recordset.

 

[MarkK]

I bet you could just do something like this too ...

Sub Test123()
   Dim qdf As DAO.QueryDef
   
   Set qdf = CurrentDb.CreateQueryDef("", _
      "INSERT INTO tblJobDetails " & _
         "( ID, JobDetails, SOR, Description, ordered, completed ) " & _
      "SELECT " & _
         "[prm0] As ID, [prm1] As JobDetails, " & _
         "cmd_sor_ref, cmd_description, cmd_original_quantity, cmd_current_completed " & _
      "FROM TASKDBA_WM_COMPLETION_DETAIL " & _
      "WHERE CMD_JOB_REF = [prm2];")
   With qdf
      .Parameters(0) = [Forms]![frmresponsivepc]![ID]
      .Parameters(1) = JobDsec
      .Parameters(2) = taskid
      .Execute
      .Close
   End With
      
End Sub

... which would be way faster because you don't need the loop. It's mostly just a table to table transfer of data anyway.
I'd look into that a little,
Cheers,
Mark

 

[Galaxiom]

It's mostly just a table to table transfer of data anyway.

I had assumed that the OP was doing a lot of manipulation in the recordset that they have not shown in the post. Otherwise a query would certainly be the way to go.

 

[MarkK]

And you could well be right Galaxiom. I just sort of took the posted code at face value.
Cheers,

 

[AndrewDotto]

I'm not really manipulating this recordset much at all to be honest, most of it gets done in the form once it's displayed.

I did think about running a straight insert into query but wasn't sure how that would fly with multiple records, some of the sets can be a few hundred lines long.

And as to the above query as to why i would want to post this to another table, It's for offline reporting... the data is being sneaked out of a backend of another system that needs to be available if they are not on the network.

I'll try that querydef and let you know, thanks for the help guys

 

[pdesnoye]

You have to create the query each time you step through a record in the recordset. In other words, the following line of code has to be embedded with the recordset loop

sql = "INSERT INTO TblJobDetails (ID,JobDetails,SOR,Description,ordered,completed) VALUES (' " & [Forms]![frmresponsivepc]![ID] & "','" & JobDesc & "','" & rst!cmd_sor_ref & "','" & rst!cmd_description & "'," & Chr(34) & rst!cmd_original_quantity & Chr(34) & "," & Chr(34) & rst!cmd_current_completed & Chr(34) & ");"


I'm not sure why you would need to copy data that is already in a table into another table...

greetings you posted a solution for this problem, Could you help me understand?
the sql you posted looks the same. Does that mean that there is a specific location to run the sql?
I think this could work for my case as well.
My intention is to add records from a recordset to another table while adding a new invoice number field to each record. Adding 1 to the last invoice number and incrementing ticket numbers on new records
Recordset from a Temporary table:
Customer - Details

Customer a - By Air
Cuetomer b - By Air
Custiner c - by Sea

I need to add to Invoice table
Invoice - Customer - Details

1120 Customer a - By Air
1121 Cuetomer b - By Air
1122 Custiner c - by Sea

 

[The_Doc_Man]

Hello, pdesnoye. The person you named (jzwp22) has not been on the forum since mid-2019 and might not answer this question. While you might get a hit, it might be better for you if you posted an original question describing what you wanted to do in a new thread, perhaps in the Query section. You will get more attention that way.

Since you are new to the forum you might not have realized yet that you are better off asking your own question rather than tagging in on someone else's abandoned question. And you did nothing wrong. I'm just telling you what works better.