How to append a record of the recordset to existing table?

[dz2k7]

Hi everybody,

I've got a new under the gun challenge on Friday.
So I need your help again.

I have one table having data and one table structured the same way like the first one, but empty.

Using the VBA recordset i analize records of the first table.
I use VBA because the logic needs to analize not only the record where the recordset is on, but the previos record as well.

So when all conditions are fine and i know that the record is needed on the resulting table i need to append current line of the recordset to the second table.

Does anybody know the name of this recordset append method or whatever i should use?

Thanks.

 

[smig]

I don't like writing all the SQL in code and prefer using built querys.
I'm sure you can use all what you've done in VBA to check for the correct records using query and maybe the help of some functions.
with querys you can simply use the append query.

 

[jdraw]

Hi everybody,

I've got a new under the gun challenge on Friday.
So I need your help again.

I have one table having data and one table structured the same way like the first one, but empty.

Using the VBA recordset i analize records of the first table.
I use VBA because the logic needs to analize not only the record where the recordset is on, but the previos record as well.

So when all conditions are fine and i know that the record is needed on the resulting table i need to append current line of the recordset to the second table.

Does anybody know the name of this recordset append method or whatever i should use?

Thanks.

I realize it is now Saturday, so maybe this is too late, but ....
If you are using DAO, here is a snippet of syntax

  Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("YourFirstTable")
    Set rs2 = db.OpenRecordset("YourSecondTable") 

... logic here

AddARecordtoSecondTable:
       [B]rs2.AddNew[/B]                     '<-----add a record structure
       rs2!Field1 = rs1!Field1
       rs2!Field2 = rs1!Field2
       ......
       [B]rs2.Update[/B]                      '<----save/commit the record to the table

 

[dz2k7]

rs2.Update '<----save/commit the record to the table
[/code]

How do I specify the table I want to update?

 

[smig]

here you declare the recordsets and set what table/query each one is using:

  Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("YourFirstTable")
    Set rs2 = db.OpenRecordset("YourSecondTable")

rs1 will be using YourFirstTable table
rs2 will be using YourSecondTable table

now you can work on the recordsets you declared and set before.
example here - Add A Record to SecondTable, copy data from table1 to table2, and update the record.

       rs2.AddNew                     '<-----add a record structure
       rs2!Field1 = rs1!Field1
       rs2!Field2 = rs1!Field2
       ......
       rs2.Update                      '<----save/commit the record to the table

in this example you are in the new record on table2 and in the first ne in table1 (since you only opened it)
you can move to another record using rs1.MoveFirst, rs1.MoveNext, rs1.MoveLast, rs1.MovePrevious, or using find....

don't forget to close the recordsets after you finish

rs1.clse
rs2.close

another option that might be even easier:
add a "flag" field to the old table, run your code and update this field for each record with 0 or 1 to know if it's OK and need to be copied to the other table.
next use an Append query to move the OK records from table1 to table2.

 

[dz2k7]

O I see,
Just missed that on the top.

And this 1-0 thing is smart and simple!

Thank you guys a lot.

 

[Thales750]

I realize it is now Saturday, so maybe this is too late, but ....
If you are using DAO, here is a snippet of syntax

  Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("YourFirstTable")
    Set rs2 = db.OpenRecordset("YourSecondTable") 
 
... logic here
 
AddARecordtoSecondTable:
       [B]rs2.AddNew[/B]                     '<-----add a record structure
       rs2!Field1 = rs1!Field1
       rs2!Field2 = rs1!Field2
       ......
       [B]rs2.Update[/B]                      '<----save/commit the record to the table

I never knew you could add a second DAO.Recordset

Thanks

 

[Thales750]

And Now I found out that you can run multiple numbers of DAO recordsets. Loops within loops.

 

[anishkgt]

Was having a similar problem in my post (http://www.access-programmers.co.uk/forums/showthread.php?p=1398708&posted=1#post1398708) how can i add to existing record.

 

[Gasman]

PMFJI, but is there not a way to just make one recordset equal to another if the structure is the same, as if you had a lot of fields in the recordset, it would be tedious to code.

I realise that there is at least another way as suggested, but curious if you can manipulate the whole record. What if you needed to write it to a text file for exaample?