Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: Joining all tables in an Access DB

    Hi,

    I'm at the end of my thread with VBA etc!! I have a database with many tables (over 2000). I need to combine all of these tables into one single table ("Main"). They are all in the same format and layout.

    I tried to do a loop in VBA using the system objects table for a listing to loop through, but failed miserably.

    Can anyone help me with this, whether it be in VBA in Access or even in a SQL query in Access. I would love to see it done in SQL, but at the moment any solution will do!

    Thanks in anticipation

    Gwolfe

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Joining all tables in an Access DB

    Originally posted by Gwolfe28
    Hi,

    I'm at the end of my thread with VBA etc!! I have a database with many tables (over 2000). I need to combine all of these tables into one single table ("Main"). They are all in the same format and layout.

    I tried to do a loop in VBA using the system objects table for a listing to loop through, but failed miserably.

    Can anyone help me with this, whether it be in VBA in Access or even in a SQL query in Access. I would love to see it done in SQL, but at the moment any solution will do!

    Thanks in anticipation

    Gwolfe
    Try these steps
    * Copy any table of your 2000 tables, and call that table [Main]

    * Delete the content of that table i.e Table [Main] is empty

    * Make a For each Loop to go through the table of definintion making an Append Query [Insert INOT]

    now it should be in that form:



    For each ttable in yourTableDefinintion
       ff="INSERT INTO MAIN SELECT [" & ttable & "].* FROM [" & ttable & "];"
       docmd.RunSQL(ff)
    next


    P.S You should declare the variables lie ff and ttable etc.

Posting Permissions

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