Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Append Monthly Tables to a Master (Office97 sr2)

    Hello again:
    I have a table deposited in this database each month that I want to have automatically appended to a Master
    table in that same database. The monthly tables are called mmmyyModComp and, of course, they have the
    same fields as Master. I would like to have the append done automatically, as soon as the new monthly table
    is added, or I have it triggered by the query that I run out of EXCEL (version 97).
    Thanks for the help. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Append Monthly Tables to a Master (Office97 sr2)

    What do you mean "deposited", and what does it have to do with Excel? You can easily build a query that will append the table to the Master table by simply aliasing the new table, creating and saving the query, and then reusing the same table alias each month. However, it isn't clear if these tables are imported into the database, attached to it, or what. Are you actually storing these tables in your database?
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Append Monthly Tables to a Master (Office97 sr2)

    Hi Charlotte:
    By "deposited", I meant that that people from another office would be simply exporting these tables to my database each month (currently I import the new tables, manually append them to the Master, and then delete them). Yes, I am "actually storing these tables in my database" at least until they are appended.
    Question: How do I set up the aliasing so that the query finds the new month's table? Is this done in the From clause? Can you show me how to do that?
    Thanks

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Append Monthly Tables to a Master (Office97 sr2)

    Here some code to append the table.
    Up to you to remove the imputbox line and provide the name of the table to be imported by another way.
    You'll have also to find a way to call the sub when the table is imported as there is no event for trapping this.
    You'll have to find it out in your application.
    <pre>Sub TableImportInMaster()
    Dim strFile As String
    Dim strSQL As String

    strFile = InputBox("Enter table name", "File Import")

    strSQL = "INSERT INTO MasterTable SELECT * FROM " & strFile
    CurrentDb.Execute strSQL
    End Sub</pre>

    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Append Monthly Tables to a Master (Office97 sr2)

    If you aren't keeping them after you append the data, just import them to the same table name each month. Then you can set up queries to append records from that table name to your Master table and it will work as long as it has a table with that name to append from. You would have to do it one table at a time, of course.
    Charlotte

Posting Permissions

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