UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Copy Records Several Times From A Table To Another Table Access, Any Version    
 
   
Fayad
post Jun 5 2019, 03:52 PM
Post#1



Posts: 7
Joined: 11-December 17



Hello,

Forgive my bad English, shrug.gif


I have two tables (Table1 and Table2) with the following fields: Id (autonumerical) and Name.Table 2: Id, Attachment and date.Table2 has 365 records in the date field (the days of a year), while the other fields Id and Attachment are emptyTo copy the IDs and names (there are always 8 records in total) from Table1 to Table2, this I get with the following code:


CODE
Dim db As Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from tabla1")
Set rs2 = db.OpenRecordset("tabla2", dbOpenDynaset)
rs.MoveFirst
Do Until rs.EOF
    rs2.Edit
    rs2.Fields(1).Value = rs("id")
    rs2.Fields(2).Value = rs("Nombre")
    rs2.Update
    rs.MoveNext
    rs2.MoveNext
Loop
rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing



In this way I can copy or edit only the first 8 records in table 2, and I need to repeat them until the end of the records.

Like this


Ids Id Nombre Date

1 1 Fariña 01/01/2019
2 2 Herrera 02/01/2019
3 3 Gutierrez 03/01/2019
4 4 Arias 04/01/2019
5 5 García 05/01/2019
6 6 Mtz. 06/01/2019
7 7 Rdrquez. 07/01/2019
8 8 Glez. 08/01/2019
9 1 Fariña 09/01/2019
10 2 Herrera 10/01/2019
11 3 Gutierrez 11/01/2019
12 4 Arias 12/01/2019
13 5 García 13/01/2019
14 6 Mtz. 14/01/2019
15 7 Rdrquez. 15/01/2019
16 8 Glez. 16/01/2019
17 17/01/2019
18 18/01/2019
19 19/01/2019
20 20/01/2019
21 21/01/2019
22 22/01/2019
23 23/01/2019
24 24/01/2019
25 25/01/2019
26 26/01/2019


Thanks
This post has been edited by Fayad: Jun 5 2019, 04:01 PM
Go to the top of the page
 
theDBguy
post Jun 5 2019, 04:00 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,699
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

I'm sorry but I am having a hard time figuring out what you're asking or wanting to do. I wonder if you could post some sample data from both tables and then show us how you want to combine them. I am hoping it will help me figure out what you mean.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Fayad
post Jun 5 2019, 04:11 PM
Post#3



Posts: 7
Joined: 11-December 17



Hello,

First, thank you very much for answering,

I need to scan or insert the 8 records that are in Table1 in Table2 until the end of the records that are there.


I have problems to attach the file, I leave a link to load the database:

<Link Removed>


Edit: Removed external link to the file. The database can be downloaded to the post if needed. Doug
This post has been edited by DougY: Jun 6 2019, 01:26 PM
Go to the top of the page
 
Phil_cattivocara...
post Jun 6 2019, 02:09 AM
Post#4



Posts: 301
Joined: 2-April 18



QUOTE (Fayad)
I need to scan or insert the 8 records that are in Table1 in Table2 until the end of the records that are there.
Perhaps the situation is more complex than we see here but if you need to insert records from one table to another one the most efficient way is to use and INSERT query, not looping through recordset.
But let's remain on your need.
In your file:

Tabla1 has 8 records. Fields: ID (pk), Nombre (text)
Tabla2 - Fields: ID_REA (pk), Id (long), Adjunto (text), fecha_REA (Date/time)

and you have this code:
CODE
Private Sub Comando0_Click()
Dim db As Database
Dim rs As DAO.Recordset  
Dim rs2 As DAO.Recordset    
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from tabla1")
Set rs2 = db.OpenRecordset("tabla2", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF
      rs2.Edit
      rs2.Fields(1).Value = rs("id")
      rs2.Fields(2).Value = rs("Nombre")
      rs2.Update
      rs.MoveNext
      rs2.MoveNext
Loop
rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing
  
End Sub

Add Option Explicit just under Option Compare Database, very important to prevent errors.
Open the first recordset in ReadOnly mode, it is faster and this prevents you from accidentaly modifing it (you only need to move in it, not to modify).
You do not need to move to last record and go back to first if you do not need to use .RecourdCount property. To check if the recordset is not empty ... see next line.
You should alse check if the recordset is empy before looping it.
Prepare rs2 for adding, not editing. Once you have ended insert operations, commit with update but you must not move to another record in rs2 (the target recordset), next loop will make an AddNew and this is what you need.
CODE
Set rs = db.OpenRecordset("Tabla1", dbOpenDynaset, dbReadOnly) 'Set rs = db.OpenRecordset("select * from tabla1")
Set rs2 = db.OpenRecordset("tabla2", dbOpenDynaset)    
'rs.MoveLast
'rs.MoveFirst
If Not rs.EOF Then
    Do Until rs.EOF
        rs2.AddNew 'rs2.Edit
            rs2.Fields("Id").Value = rs("Id").Value
            rs2.Fields("Adjunto").Value = rs("Nombre").Value
        rs2.Update
        rs.MoveNext
        rs2.MoveNext
    Loop
End If

I have modified the way you refer to fields in rs2 only to make it easier to read for UtterAccess users.
Obviously Tabla1 has 8 records and code adds only 8 records. Try this code in your real db and let us know.
Everything should (read: MUST) be inside a transaction with error handling to rollback if necessary.
This post has been edited by Phil_cattivocarattere: Jun 6 2019, 02:25 AM

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
Fayad
post Jun 6 2019, 06:15 AM
Post#5



Posts: 7
Joined: 11-December 17



Hello,
I am very grateful for your answer, but I still only insert 8 records when I want to fill the table to the 365 register. That is, from 1 to 8, from 9 to 16, from 17 to 24 and so on until the end, they are always the same from 1 to 8. I have tried several ways to go around the code but I do not give with the solution. My real database is the same as the one I posted in the forum.
Thank you very much.
Go to the top of the page
 
Phil_cattivocara...
post Jun 6 2019, 06:36 AM
Post#6



Posts: 301
Joined: 2-April 18



QUOTE (Fayad)
when I want to fill the table to the 365 register. That is, from 1 to 8, from 9 to 16, from 17 to 24 and so on until the end, they are always the same from 1 to 8.
Ok, now it is clear. I do not know why you need it but... let's do it!
Before proposing code another question: tabla2 is always empty (no records) when you start inserting? or does it have 365 records present and you only need to update them with records from tabla1?

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
Fayad
post Jun 6 2019, 07:20 AM
Post#7



Posts: 7
Joined: 11-December 17



Hello!
Thanks again for answering.
You see, I need it to automate the annual allocation to 8 people, so that each person has to do the same task every 8 days. Table 2 always has 365 records (the days you have a year).

regards
Go to the top of the page
 
RJD
post Jun 6 2019, 08:36 AM
Post#8


UtterAccess VIP
Posts: 9,722
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

Hi: If you make a simple query to equate the IDs to the ID, then link on the ID in each table, the correct values will be available in a query.

SELECT IDs, (([IDs]-1) Mod 8)+1 AS ID
FROM Tabla2;

... as qrySequences...

SELECT qrySequences.IDs, qrySequences.ID, Tabla1.Nombre, qrySequences.TheDate
FROM Tabla1 INNER JOIN qrySequences ON Tabla1.ID = qrySequences.ID;

Note that you will have to add TheDate to qrySequences ... I actually created it in the demo using a calculation rather than storing it.

...or you can combine the queries using the subquery approach.

See the demo attached (in A2010). It does not have all 365 days, but this should apply to your existing tables. Incidentally, do not use Date as a field name. Date is a reserved word and can cause problems in some cases.

HTH
Joe
Attached File(s)
Attached File  RepeatingSequenceMatch.zip ( 20.02K )Number of downloads: 6
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Fayad
post Jun 6 2019, 10:30 AM
Post#9



Posts: 7
Joined: 11-December 17



Hello!
Starting from the brilliant idea of the master RJD, I have created in Table2 365 records that correspond to a given year, and applied its method and it has gone perfectly.

P. D: I would have liked to know why with RecordSet it has not worked for me. I will continue investigating and I will let you know if I find another way.

Thank you very much master
This post has been edited by Fayad: Jun 6 2019, 10:38 AM
Attached File(s)
Attached File  MFAFA.zip ( 135.99K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Jun 6 2019, 01:22 PM
Post#10


UtterAccess VIP
Posts: 9,722
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad you could get that to work. It seems to work well.

As to using the RecordSet approach in VBA, Phil had some suggestions. Have to tried what he suggested? Something like that should work, and I am sure if you try what Phil posted and ask him, you could resolve your issue that way. Meanwhile, you have a solution using just queries.

Continued success with your project.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Phil_cattivocara...
post Jun 7 2019, 11:15 PM
Post#11



Posts: 301
Joined: 2-April 18



QUOTE (Fayad)
I would have liked to know why with RecordSet it has not worked for me
Here I am, again.
Once I have understood your real need I had to try different ways and this works. This is not the only one using recordset but it works.
CODE
Dim db As DAO.Database
   Dim rs1 As DAO.Recordset
   Dim rs2 As DAO.Recordset
   Set db = CurrentDb
   Set rs1 = db.OpenRecordset("SELECT * FROM Tabla1 ORDER BY Id", dbOpenDynaset, dbReadOnly)
   Set rs2 = db.OpenRecordset("SELECT * FROM Tabla2 ORDER BY Id_REA", dbOpenDynaset)
   If Not rs1.EOF Then
       Do Until rs2.EOF
           'If rs2.AbsolutePosition = 360 Then Stop
           rs2.Edit
               rs2.Fields("Id").Value = rs1("Id").Value
               rs2.Fields("Adjunto").Value = rs1("Nombre").Value
           rs2.Update
           rs2.MoveNext
           rs1.MoveNext
           If rs1.EOF Then rs1.MoveFirst
       Loop
   End If
   rs1.Close
   rs2.Close
   Set rs1 = Nothing
   Set rs2 = Nothing
Note
1) I have modified recordset object names: rs1 for Tabla1, rs2 for Tabla2;
2) I added an "ORDER BY" to open the recordsets, to be sure we have records in the right sequence, to prevent any risk.
3) You see a commented line. If you uncomment it and run the whole code it stops when it arrives to record number 360 in Tabla2 (or better: in recordset based on a select from Tabla2 ordered by Id_REA). Continuing execution step by step (F8) you can see what happens when it arrives at the end of rs2 and that everything works fine.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
Fayad
post Jun 9 2019, 03:25 PM
Post#12



Posts: 7
Joined: 11-December 17



Hello!

The truth had already lost hope in getting it with my initial idea, but no, thanks to the master Phil_cattivocarattere.So many thanks master.

I wanted to comment that I found another solution and wanted to share with you, I leave the file at the end.

Pleased to be part of this wonderful community. regards
Attached File(s)
Attached File  MFAFA_1__1_.zip ( 41.59K )Number of downloads: 2
 
Go to the top of the page
 
Phil_cattivocara...
post Jun 10 2019, 02:15 AM
Post#13



Posts: 301
Joined: 2-April 18



QUOTE (Fayad)
So many thanks master.
Master? No, I am only a student with Access. Perhaps not a "first year" student but always a student.

The most important thing is you understood the importance of this line
CODE
If rs1.EOF Then rs1.MoveFirst

Did you?

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
Fayad
post Jun 10 2019, 04:20 PM
Post#14



Posts: 7
Joined: 11-December 17



Hi!


Yes now!! notworthy.gif

thank you very much again.

Best regards
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2019 - 09:39 AM