Results 1 to 5 of 5
  1. #1
    Join Date
    May 2005
    Location
    Georgia
    Posts
    3

    Question Unanswered: Query Multiple Tables in DB with NO Relationships

    I have a database, not created by me, that has multiple tables. There is a main table called Configure that stores data for each location and has an AutoID field. When a record is added to the Configure table, it creates 3 new tables based on the AutoID field (if ID = 1000 then the tables created are called AddDate1000, JournalData1000 and Parameters1000). Each of these tables created contain 1 row and multiple columns.

    What I need to do is query each of the Parameters tables (about 1000). There are 3 columns (Store Message, Memdir1 and FreeMem1) that I need to pull data, if available. I am having problems making a query that will do what I want. Below is a sample of querying 1 table, but I need to do this for 1000.

    Code:
    SELECT PARAMETERS1.STOREMESS, PARAMETERS1.MEMDIR1, PARAMETERS1.FREEMEM1
    FROM PARAMETERS1
    WHERE (((PARAMETERS1.STOREMESS) Like "Loaf") AND ((PARAMETERS1.MEMDIR1) Is Not Null) AND ((PARAMETERS1.FREEMEM1)>0));

  2. #2
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    The only way I can think of to handle such a bizarre situation dynamically (expanding to cope with any new tables) would be to generate an SQL statement in VBA and then run it with DoCmd.runSQL. The way you might construct it would be to read the Configure table to get the numbers, then create the file names and then the queries using the file name. The separate queries are concatenated into a grand query using the UNION clause. The records can then be read in sequence as though they were all in the same table.
    I have not tried it myself, but I have seen instances where VBA functions were embedded in queries, but only in the situation where a complex string was being generated dynamically to put in a field. I don't know whether you could construct a query with a VBA reference that would populate multiple fields simultaneously.

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    You probably should also do everything in your power to find out who designed that database and make sure they are fired from whatever current position they are in, and ensure that they are never employed again in any position that requires that they know any english language sentences other than "do you want fries with that?"
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Sep 2008
    Posts
    7
    Sounds like you'll have to do this through a scripting language such as VBA as suggested by Jim Wright

    generate an SQL statement in VBA and then run it with DoCmd.runSQL
    You will most likely need to setup a "For loop" in VBA code going through each of the table names and running the SQL to retrieve the column data. You can then output to a CSV or excel etc...

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'm with TC. What a completely hopeless design. I'd suggest that a total rebuild might be in order.

    I wonder what the limit is for the number of tables allowed in an Access database... something like 2048 objects in the whole database I think.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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