Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2003
    Posts
    7

    Unanswered: Copying records from one table to another

    I'm creating a form to exctrat selected records from a table (TABLE 1) and to copy them with additionnal informations in a second table (TABLE 2).
    - the user choose the record he wants to extract via the list of primary key values of TABLE 1
    - he adds in text boxes the additionnal informations

    For the moment, when he clicks on the "Execute" button, it call the Query that extracts the selected record from TABLE 1

    But now, I'd like to copy that record in TABLE 2 with the additionnal information, but I don't know how to do : can you help me?

    To help you here is the Structure of my tables:
    TABLE 1: PrimaryKey, Field1,Field2,.....,Field10
    TABLE 2: AdditionnalInfo1, dditionnalInfo2, dditionnalInfo3, ..., Field1, Field2, ....., Field10

    Thank you very much
    Caroline

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    I take it that you want to copy from table 2 to a new table. Even if you want to copy from table 1 to table 2, the method would be the same. You will need to create an append query. The easiest way is to use the query grid. Create the query in the design grid and choose append query from the query toolbar.

    David

  3. #3
    Join Date
    Jan 2003
    Location
    Aberdeen, Scotland, UK
    Posts
    168

    there's several ways..

    You could use an update query or Use a recordset in Vb.

    I'd use the Vb as,,,well...I prefer it.

    (do all the dimming and setting)

    set rs1 = db.openrecordset("select * from [table1] where [id] = " & me.id)

    set rs2= db.openrecordset("select * from table2]")
    rs2![field1] = rs1![field1]
    rs2![field2] = rs2![field2]
    .
    .
    .etc to field10
    rs2![additionalinfo] = me.additionalinfo1
    etc.

    -----------------------------------------
    does this make sense?
    J.

  4. #4
    Join Date
    Jan 2003
    Posts
    46
    Caroline,

    I can think of a few reasons to copy data to a second table. (perhaps it is to be used as a temporary table with transient data)

    But on the whole, it isn't a good idea. Access is a relational database. Data should only be entered once and then related. Queries can be used to extract data as needed from various tables.

    Just a suggestion.

    Cheers,
    zambezibill

  5. #5
    Join Date
    Feb 2003
    Posts
    7

    Wink Re: there's several ways..

    Thank's very much.
    It's exactly what I wanted to do.
    If I understand well, my recordset will contain one "line" or record from table1 with my additionnal info.

    But how can I record the information the recordset contain in my new table, table2?? Just like that?
    With rs2
    .Update
    End with
    Can you exlain me ???

    Thank you


    Originally posted by johncameron
    You could use an update query or Use a recordset in Vb...

    I'd use the Vb as,,,well...I prefer it.

    (do all the dimming and setting)

    set rs1 = db.openrecordset("select * from [table1] where [id] = " & me.id)

    set rs2= db.openrecordset("select * from table2]")
    rs2![field1] = rs1![field1]
    rs2![field2] = rs2![field2]
    .
    .
    .etc to field10
    rs2![additionalinfo] = me.additionalinfo1
    etc.

    -----------------------------------------
    does this make sense?

  6. #6
    Join Date
    Feb 2003
    Posts
    7

    Smile

    In fact, I'm importing table 1 from an Excel file, and then I want to extract information with additionnal informations from that initial table in order to work on the new table, less "huge".
    Then, I will delete the imported table.

    Would you procede in a different way?


    Originally posted by zambezibill
    Caroline,

    I can think of a few reasons to copy data to a second table. (perhaps it is to be used as a temporary table with transient data)

    But on the whole, it isn't a good idea. Access is a relational database. Data should only be entered once and then related. Queries can be used to extract data as needed from various tables.

    Just a suggestion.

    Cheers,
    zambezibill

  7. #7
    Join Date
    Feb 2003
    Posts
    7
    That's what I tryed to do, but it's not simple. I don't know exactly how an append query works.
    I'm just a begginner in database, and don't know exactly how to do.
    But if you don't have time to explain it to me, don't worry, I will try like JohnCammeron told me.

    Thank you.


    Originally posted by DJN
    I take it that you want to copy from table 2 to a new table. Even if you want to copy from table 1 to table 2, the method would be the same. You will need to create an append query. The easiest way is to use the query grid. Create the query in the design grid and choose append query from the query toolbar.

    David

  8. #8
    Join Date
    Jan 2003
    Location
    Aberdeen, Scotland, UK
    Posts
    168

    VB code..

    Caroline,

    Have you tried the code? How did it go? Any errors or anything? Let me know if I can help.

    John
    J.

  9. #9
    Join Date
    Feb 2003
    Posts
    7

    Smile Re: VB code..

    Yes, i have defenitly chosen your code, it works now perfectly well.
    In fact, that's the first time I use VBA and recordset, and don't have any help here for my internship, so it's not so easy. Thank you very much !

    Now, if you have time can you have a look to my little new problem ?
    If, you don't, it doesn't matter.


    Here is my problem:

    Id like to find out dynamicaly the key of an object in the table OBJECTSTable, knowing its NAME and PARTICULARITY.

    I thought to use a SQL QueryDef like that :

    ************************************************** *******
    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.SQL = "SELECT [OBJECTSTable].Key FROM [OBJECTSTable] WHERE (([OBJECTSTable].[Name]=" & MyName & " AND " _
    & " [OBJECTSTable].[Particularity]=" & MyParticularity & "))"
    ************************************************** ******

    This works perfectly well.
    But when I want to put the query in a recordset to extract the first value, like that ..

    ************************************************** ******
    Set rst = qdf.OpenRecordset() (1)
    ConfReference = rstinter.Fields(0)
    ************************************************** ******

    Im said, for the first code line (1) that there is too few parameters, 2 are expected.
    BUT ALL THE EXEMPLES I SAW USE THAT SENTENCE (Set rst = qdf.OpenRecordset()) WITHOUT PROBLEM. SO I CANT UNDERSTAND WHAT IS THE MATTER ! ! ! ! !

    VBA and its mysteries ......



    Originally posted by johncameron
    Caroline,

    Have you tried the code? How did it go? Any errors or anything? Let me know if I can help.

    John

  10. #10
    Join Date
    Jan 2003
    Location
    Aberdeen, Scotland, UK
    Posts
    168
    set rst=db.openrecordset(qdf)

    that will make the sql be the recordset.

    Help?
    J.

  11. #11
    Join Date
    Feb 2003
    Posts
    7
    Very quick answer !!

    Thank you very much, i'm going to try this.
    Thanks again!

    Originally posted by johncameron
    set rst=db.openrecordset(qdf)

    that will make the sql be the recordset.

    Help?

  12. #12
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    just remember that you need to define db

    the easest method is the use currentdb not db
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •