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

Topic

New Reply

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

Reply | Quote

Replies

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,

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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 ).

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

Reply | Quote

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):-

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

Reply | Quote