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

    Appending Access tables prior to a parameterQuery (OFFICE 97 SR2)

    Hello again:
    I am doing a parameter query of an Access table that we can call Master. At an unpredictable time during each month, a monthly table, called mmmyyModComp, is deposited in the Access. I need to be able to detect when it is there and have it appended to the Master table before I issue the parameter query. I could accomplish the same thing with a union query using Msquery but the EXCEL97 version will not allow the use of parameters with a Union query. So, I would need to be able to save the result of the union query and then run the parameter query against it. I do not know how to do this using MSQUERY or DAO. Can any one give me a clue?
    Currently, I am manually appending the monthly table to the Master in ACCESS. I would like to be able to get out of the loop and completely hand over the programs to the people who want to use them.
    Thanks

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending Access tables prior to a parameterQuery (OFFICE 97 SR2)

    How is this access database used? Do you have a client application which you built which is the sole entry point, or are there other ways that people use the data? If the former, you could build something into your application which looks for the mmmyyModComp table when it initialises, then appends the table's data to Master, before deleting the mmmyyModComp table or renaming it for archiving. If there are other ways that people use the data, then I'd say you need an application running somewhere which polls the database for the appearance of the file, and then does the appending, etc. This kind of application would have to be running on a server somewhere, if you have access to such resources.

    Regards,

  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: Appending Access tables prior to a parameterQuery (OFFICE 97 SR2)

    You asked: "Do you have a client application which you built which is the sole entry point". Answer: YES

    You commented: "you could build something into your application which looks for the mmmyyModComp table when it initialises, then appends the table's data to Master, before deleting the mmmyyModComp table or renaming it for archiving."
    Yes, this is exactly what I need.
    My idea was to use a union query to append the 2 tables. However, my application consists of a parameter query that is launched out of EXCEL97 and MSQUERY (or DAO) will not allow use of parameters with a union query. My next idea was to use the union query to append the tables and the parameter query to get the data from the appended table but I do not know how to do this. I tried saving the EXCEL query and then using the parameter query to get data from it but found that this was not possible. Is there some way that the EXCEL query can save the appended table in ACCESS?
    Basically, I understand what I need to do but I don't know how to do it.
    Thanks for your help

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Appending Access tables prior to a parameterQuery (OFFICE 97 SR2)

    Hi,
    You can certainly do this with DAO. You would code your Excel spreadsheet to open the database, check the Tabledefs collection for the relevant tablename, if there, run an append query. You then have the option of either using your existing Excel query or simply opening a recordset since you have the database open anyway. Then you need to delete the table and close the database.
    You could equally run the whole thing from Access, automating Excel and copying the relevant recordset into it. Just depends on whether your users are comfortable using Access or want to do it all through Excel!
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Appending Access tables prior to a parameterQuery (OFFICE 97 SR2)

    The other alternative is to design a union query in Access that does the union you want, then a make-table query in Access that just takes the union query you just wrote in the query grid and the "*" for all fields and creates a new table - call this table "NewTable" and the query qryMkNewTable. You have to do this two-step approach since you can't write a "Union Make Table" query (or maybe YOU can - I can't <g>).

    In Excel you open the querydefs collection and execute qryMkNewTable, and then run the parameter query against NewTable

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending Access tables prior to a parameterQuery (OFFICE 97 SR2)

    I'd say your excel sheet should stay just as it is, for simplicity, with just the addition of a bit if VBA in an AutoOpen() routine, which uses DAO, as Rory suggests, to run the append query which will append the contents of mmmyyModComp to the Master table.

    Something like this should do the trick, placed in a module in your Workbook (the Workbook will have to be referenced to DAO - please ask if you don't know how to do this):-

    <pre>Sub AutoOpen()
    Dim db As Database
    Dim tbl As TableDef
    Dim sTablename As String

    'open the database
    Set db = OpenDatabase("c:mydatabase.mdb")

    'loop thru the tabledefs to see if this month's table is there
    sTablename = Format(Date, "mmmyy") & "ModComp"
    For Each tbl In db.TableDefs
    If tbl.Name = sTablename Then

    'rename the monthly table to the name that the append query is looking for
    db.TableDefs(sTablename).Name = "ModComp"

    'run the append query
    db.QueryDefs("myAppendQuery").Execute

    'rename the table back to the monthly name, _
    with a suffix indicating it has been appended to Master
    db.TableDefs("ModComp").Name = sTablename & "_appended"

    'exit the loop thru the tables
    Exit For

    End If
    Next tbl

    'close the database
    db.Close

    'garbage collection
    Set tbl = Nothing
    Set db = Nothing

    End Sub
    </pre>


Posting Permissions

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