Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32

    Unanswered: Combining Tables

    Have two databases; ract and ract1. Would like to mege these together. Ract1 was created because I could not import it into the ract database. The fields are identical in both databases.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    quickest (& dirtiest) cut & paste.

    If its a one off then the pain of writing and testing a VBA function or update queries will not be worth it.

    just my 2 1/2d

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    After the cut and paste (your quickest path to getting the job done here), lookup UNION QUERY.

    The syntax is just like two SELECT QUERYs with the word UNION between them. OF course, you'd have to create a MAKE TABLE QUERY that runs off the UNION QUERY.

    But, if you find you have to do this more than once .....
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  4. #4
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32
    I did a cut and paste, but I can not pull all records. I tried to create the union query, no luck. Used select all [ract] union select all [ract1]. Just want to be able to combine the two tables. All field names are the same.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is that the actual query you tried?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    come again
    table names are the same
    column names are the same
    column defintions are the same (guessing here)

    and cut and paste doesn't work, didn't import all records - what caused the records to be rejected - did looking at the paste errors suggest what the problem is.

    alterantive is to link RACT1 table in the RACT db
    do an insert query into RACT table from the RACt1 table

  7. #7
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32
    Got the tables combined, but could not query data from the added rows. I added approx 6700 rows.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Maybe your query is wrong.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32
    Thanks for the help. It's now working. The query was correct, but I had to change the relationship between the two tables

  10. #10
    Join Date
    Oct 2005
    Posts
    178
    Depending how bad you needed to merge the two data sources and how large are the database, you can write a small vb application which is safer than cut and paste because you know exactly in detail how you want it done in process. You can test on copied database as many times a you want to make sure it's perfectly alright then do it finally.

    This is the logic flow I would suggest

    Make sure the two tables are not linked with any field

    I presumed your term "database" are actually tables with common field names. If they are totally separate database, you need to open two connections, then open each recordset from different connections, otherwise just open two recordset like below..

    Ract.open "Select .......
    Ract1.open "Select ....
    Ract.movefirst
    While not Ract.eof
    Ract1.addnew
    Ract1.field1.value = Ract.field1.value
    Ract1.field2.value = Ract.field2.value
    so on...
    Ract1.Update
    Ract.movenext
    wend
    Ract1.close
    Ract.close
    Last edited by fredservillon; 11-24-05 at 23:29.

Posting Permissions

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