Results 1 to 8 of 8
  1. #1
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    Pulling db table and field names for inclusion in a table

    I’m posting this question in the Programming area because I figure it goes deeper than typical design, and only experts would know how to get to the guts of Access.

    In the attached data base are the tblCustomer and tblEmployee. Each has fields defined. There are no records and it’s not important for this problem.
    There is a third table, tblUserData that has fields defined as well as data.
    We want to replace that data (in tblUserData) with table and field names obtained from the active db.

    What I’m looking for is a way to get the tables and fields from Access itself instead of entering the data in each cell manually. For now, a combo box would serve. The combo box for TblNm gets only available tables in the db. The combo box for FldNm column must only show fields based on what is in the TblNm column.

    What is available to grab as data from access, only the names as text, or is there an object ID that can be stored?
    Is validation possible?
    What happens if a table is renamed or deleted?
    Thanks



    dtTableField1.accdb

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    do you just want the table and field name or do you need to know more such as if the table is linked and if so to what, details about the field such as datatype and constraints? And why one table when you have two different entities?

    best way would be to loop through the tabledefs containing a second loop to loop through the fields - something like

    Code:
    dim tdf as dao.tabledef
    dim fld as dao.field
    
    for each tdf in currentdb.tabledefs
    
        for each fld in tdf.fields
            currentdb.execute("INSERT INTO newTable (TName, FName) Values('" & tdf.name & "', '" & fld.name & "')")
        next fld
    
    
    next tdf

  3. #3
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Quote Originally Posted by CJ_London View Post
    do you just want the table and field name or do you need to know more such as if the table is linked and if so to what, details about the field such as datatype and constraints?
    For now, I'm happy to get the table description or ID and field description or ID.
    If we can get the ID, then other data should be available, of which the description is just one property.
    In any case, when it comes time to do the form, for both TblNm and FldNm a lookup of the ID or description would be saved in the cell via a combo box or lookup form.
    To understand why just one table, you can checkout this thread: Query for this simple join between two tables not displaying. (accessforums.net)
    The whole point is to create user customizable fields that don't require additional tables.
    And in the long-game, the application will be multi-language with a similar structure.

    I guess I should add that this table/form won't be used very often, only when creating support structures of descriptions.
    Is it best to create the query and have it sitting around hogging up space all the time?
    Or can I run a query at form open by passing parameters, as conditions and then use what I need for the combo box and toss the query after the form is closed?

    As you can guess, I'm new to all this.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Tables and fields do not have id’s. You can reference using an index number but that can change.

    I use something similar for metadata

  5. #5
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    So, do you just go with the actual file and field names as text?

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is an example of a form to select table and show its fields without much code. For more "intensive" needs you would need to look up DAO.TableDef and its fields collection.


    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    So, do you just go with the actual file and field names as text?
    table and field names - yes. If I need to store additional table properties for some reason then table names go in a separate table with these properties and a PK and the field names go in a separate table with a FK for the table. Typically I use meta data for imports/exports, particularly the former so I can check the data meets the the datatype and constraint requirements and give the user options as to what to do when they are not met. Also use it when I give a user the facility to generate their own reports - but those reports are not formatted as such - just a query view that can be exported to an excel or .csv file.

    I don't allow users to mess with the design - FE's are all .accde and they can only view and interact with data via forms and reports.

    Is it best to create the query and have it sitting around hogging up space all the time?
    what query? the code creates a table. Depends what the app does as to whether you need it after developing has completed
    Or can I run a query at form open by passing parameters, as conditions and then use what I need for the combo box and toss the query after the form is closed?
    of course. Some developers on use stored queries, other always build queries dynamically as and when required whilst other use a mixture of both

    As you can guess, I'm new to all this.
    I do think you are overthinking it a bit

    Are you aware of database normalisation?
    Have you tried some of the thousands of templates and examples out there?

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a routine to review current database and identify tables and fields and some properties.
    This is vba code for module datadictionary.

    Create a New module
    Copy this code to that module
    Save module as DataDictionary or a name of your choice.

    It's quite basic but has been helpful to me and others for many years.

    If you want a more in depth database analyzer see/download this from Crystal Long(strive4peace)
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Field names in new table
    By Josen in forum Queries
    Replies: 8
    Last Post: 10-17-2019, 03:19 PM
  2. Replies: 7
    Last Post: 03-21-2018, 04:58 AM
  3. Create Table With Field Names From Current Table
    By jo15765 in forum Programming
    Replies: 5
    Last Post: 05-22-2017, 03:33 PM
  4. Replies: 5
    Last Post: 04-17-2015, 11:04 AM
  5. pulling field names from table
    By murfeezlaw in forum Queries
    Replies: 6
    Last Post: 11-27-2012, 04:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums