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

    Need guidance autolinking tables (Office 97 SR2 win98)

    Hello Fellow Loungers:
    Our system generates monthly single-table Quality Control databases which are named mmmyyProj.mdb, where the first 5 characters identify the month and year and the 'proj' characters are replaced with the name of the project. I need to somehow bring the data from these tables into a single table (in another database) for each project for purposes of generating statistics and trending performance. If I can solve the problem for one project, I will be able to use the same solution for the other projects so the project factor can be ignored for now.

    I am planning to proceed by setting up 12 empty mmmyyProj.mdb databases (one for each month of the year) so I can establish the linkages at the beginning of each year. Then, as the databases for each month are generated they would be saved over the empty one for that month. I would then have a single table in a yyProj.mdb to which I would append each of the monthly tables, which I would bring in by Linking to the monthly databases. What I need help with is to create a macro in my yyproj.mdb database that would append each of the monthly tables to a totals table that would be used, in turn, to generate monthly trends. I could have a user supply the 'mmm' for the new month. But I would like to do it without having a user involved to eliminate the possibility of user error that could cause one month to be appended multiple times or something like that.

    I have an idea but I do not know how to proceed. My idea is to have the appending process triggered each time the database is queried. To prevent data from being appended multiple times I would need the macro to delete the monthly table that had non null rows immediately after the appending process has finished running. This would not jeopardize data integrity because the data in the monthly databases do not change after they are generated.
    I will be using EXCEL to generate the Trend charts and statistics and would like to trigger the ACCESS code from EXCEL, if possible. I program VBA out of EXCEL but it seems to entirely different in ACCESS.

    I would appreciate any help I can get. I have been messing with this problem for months and I need to put a solution into effect soon. I am confidant that I have the best approach, given our set up here. I am not looking for someone to write all the code for me. Rather, I need someone who can give me some hints and be there if I run into problems executing the instructions.
    Thanks in advance.
    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need guidance autolinking tables (Office 97 SR2 win98)

    Here's a possibility to get you started:

    In your yyProj.mdb database (which has link tables to the table(s) of interest in each of your mmmyyProj.mdb databases, right?) create a UNION query that "glues" all these tables together. The syntax would be something like:

    SELECT * FROM [jan02Proj]
    UNION SELECT * FROM [feb02Proj]
    UNION ...
    ...
    UNION SELECT * FROM [dec02Proj];

    where jan02Proj, etc. are the linked table names (not necessarily the mdb file names.

    As far as I know, UNION queries can't be created using the design grid; you have to type the SQL code directly into the SQL window.

    The result of this query can be exported to Excel using Office Links or via VBA code.

    Yes you can do all of this from Excel VBA. I'll follow up with some guidance for this in a later post (don't have time now), if somebody doesn't beat me to it.

    By the way, I had some "flakeness" problems with Excel 97 - Access 97 automation (e.g., running Access from Excel and vise versa). Generally it worked okay but occasionally I got "automation errors" seemingly out of the blue. Just a forewarning. Office 2000 seems to behave much better in this regard.

    Good luck.

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need guidance autolinking tables (Office 97 SR2 win98)

    To my understanding you have already established the 12 mmmyyProj dbs initialy having empty tables and in addition you have established a yyProj dbs.
    Assuming you have established links to the 12 mmmyyProj dbs tables in the yyProj dbs, all you have to do is to create a UNION query in the yyProj dbs in order to combine your monthly results. Then you do not have to worry about who will append data and when. The result will always be uptodate (uptomonth).
    As for Excel: bring your data into a sheet using a query. Create a query in Excel referencing the above mentioned query in the yyProj dbs.
    As I see it, you don't have to do any VB programming in Excel or Access to do this job.

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

    Re: Need guidance autolinking tables (Office 97 SR2 win98)

    Thank you both for your quick responses. I really should have known about the union query bit. I think this will work fine. I use MSQUERY (dao) to bring data from ACCESS into EXCEL all the time but I have never queried a query. I do it within ACCESS so I guess there should not be a problem doing it from EXCEL. I will try this first thing Monday.
    Tousand Tak

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need guidance autolinking tables (Office 97 SR2 win98)

    Yes, when you run MSQuery from Excel (Tools...Get External Data) and the Wizard asks for the source database and you select your Access mdb file (the one with the UNION query), that query will appear in the list of "tables" you can retrieve.

    When you use Tools...Get External Data to run MSQuery and import the data the first time, Excel creates a QueryTable object that maintains the linkage to the Access database, so that when your Access data changes, you just need to select Data...Refresh Data to update your Excel table.

    You can also set a refresh interval for the QueryTable object so that the refresh occurs automatically and periodically. Select Data...Get External Data...Data Range Properties for the various options (these menus are from Excel 2000; Excel 97 may be slightly different?). Of course, you may already be familiar with these options since you've queried from Excel before...

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

    Re: Need guidance autolinking tables (Office 97 SR2 win98)

    Hi Again:
    OK, I set up the Union queries but I do not think that this will be the best answer because it takes too long to run them. Here is what the Union query looks like (each mmmyyANS table is merely a shell until replaced by the actual one from the current month).

    SELECT *
    FROM ANSALL_01 ' this first table has results of appending each month's table from 2001.
    Union
    SELECT *
    FROM jan02ANS
    Union
    SELECT *
    FROM Feb02ANS
    Union
    SELECT *
    FROM Mar02ANS
    Union
    SELECT *
    FROM APR02ANS
    Union
    SELECT *
    FROM May02ANS
    Etc ...

    My purpose was to avoid having to have someone go into the database each month, after the new table is received, to do an Append query. I was going to trigger the Union query from EXCEL where the charting application is located. The first time I tried it, it ran and ran.

    I think that a better plan would be to have some code that appends each new month's table to ANSALL_01 (which name I would change later to ANSALL_01_02). This is working quite well when I do it manually. But, I need some code to automate the process where the "trigger" could be the event of receiving the new Table in ACCESS or, probably easier, the query that I run from EXCEL where the user interface and trend charts are located.

    In summary, getting the new Table into this database each month is not a problem; the problem is automating the append query (e.g., appending jan02ANS to ANSALL_01_02 and then deleting the jan02ANS and the next month appending feb02ANS; etc,).

    I know that this process of importing into or exporting to an ACESS Database must sound antiquated but that is how things got set up here. Eventually, the tables that we need each month will be made available through the parent database that processes the data. But, for now, it all I have to work with.

    I appreciate whatever help you can give.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need guidance autolinking tables (Office 97 SR2 win98)

    I think you can get close to where you want to go by including something like the following in your VBA code:
    <font face="Georgia">
    strNewDataTable = "jan02ANS"
    CurrentDb.CreateQueryDef("", "INSERT INTO ANSALL_01 " & _
    "SELECT " & strNewTableName & ".* " & _
    "FROM " & strNewTableName & ";").Execute
    </font face=georgia>
    You could "automate" this a little more by assigning <font face="Georgia">strNewDataTable</font face=georgia> based on the current date:

    <font face="Georgia">strNewDataTable = Format(Date,"mmmyy") & "ANS"</font face=georgia>

    or something similar. You may want to include some error-trapping to check if the new data table actually exists. Also the routine that contains this query could be run on startup or, with a little embellishment, could be run from Excel directly (let me know if you need some help with that "embellishment."

    Hope this helps.

Posting Permissions

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