Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    213

    Unanswered: Database - dynamic tables - use dynamic - sql and loop, querying a column

    Hello,

    Let me begin. I have a database that is used to import a file, run queries and output them into a spreadsheet template. Sound easy? its not!

    The only 2 static fields of the table are Time & Seconds. After that several other fields are added, each of these extra fields represents a node. I need to run 7 queries on each node and export them to a spreadsheet template and save it. My database is not normalised but im not sure if its going to need to be.

    Does anyone know if it would be possible to count the amount of fields that are imported, save them to some kind of array and loop, querying a column at a time probabally using somekind of dynamic sql.

    If this sounds possible and anyone could help me, please do. Im really stuck with this and the sooner i can fix it the better.

    Thanks for your replys in advance,
    Marley.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could use DAO tabledefs/querydefs to get metadata such as field counts/names etc. I'm not sure exactly how this applies as you were a bit vague, but that's one option.

    ADO catalogues could do it too...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2006
    Posts
    213
    I have the database here http://www.dbforums.com/showthread.php?t=1213217&page=5

    and abit more on the problem can be seen on the last page of that post

  4. #4
    Join Date
    Feb 2006
    Posts
    213
    any chance you could give me a hand with it as I dont really know this method.

  5. #5
    Join Date
    Feb 2006
    Posts
    213
    Ok, i went and did a bit of research and came back with this.. i think it looks at the imported field names and adds a name to each of the columns. I added the count function to this but will it now only adds names to the fields imported and not the rest of the fields? That is what im aiming for.

    Code:
    Public Sub addtablenames()
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
     
        Set db = CurrentDb
        Set tdf = db.TableDefs("tblImportTableTest")
     
        For Each fld In tdf.Fields
            Debug.Print fld.Name
        Next
    
        Debug.Print tdf.Fields.Count
     
        Set tdf = Nothing
        Set db = Nothing
     
    End Sub
    Last edited by marleyuk; 03-06-06 at 10:06.

  6. #6
    Join Date
    Feb 2006
    Posts
    213
    Any forum love?

Posting Permissions

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