View original (French)

[Access & SQL] column names of a table

Solved
Nicolas -  
 salem -
Hello,
I am developing a VBA application in Access XP and I would like to create a query that allows me to retrieve the column names of each table in my database.

For example: select lib_nomcol from table system where nom_table="dde"

I have already executed a similar query in SQL Server; however, (even when displaying system objects) I haven't found any tables in Access that group this data.
The only information I found relates to Access 97 and no longer works... (in that version, a syscolumns table was present)

If anyone has an idea,
Thank you.

4 answers

  1. Anonymous user
     
    Hello,

    In the absence of an SQL query, this small VBA code will allow you
    to know the column names!

    This code is inserted into a form that points to a
    global query of a table. 

     Private Sub ExecuteRoutine_Click() On Error GoTo Err_ExecuteRoutine_Click Dim NombreChamps, Boucle As Integer NombreChamps = Me.Recordset.Fields.Count For Boucle = 0 To (NombreChamps - 1) MsgBox Me.Recordset.Fields(Boucle).Name Next Boucle Exit_ExecuteRoutine_Click: Exit Sub Err_ExecuteRoutine_Click: MsgBox Err.Description Resume Exit_ExecuteRoutine_Click End Sub


    Lupin
    4
    1. salem
       
      Access 2003 VBA

      To list all fields of each table or all tables of an MDB in use

      Necessary and mandatory
      Main menu: Tools - References
      Checked: Microsoft DAO 3.6 Object Library

      Sub Tables_and_fields()

      'Variable declaration
      Dim dbd As DAO.Database
      Dim tbd As DAO.TableDef
      Dim fld As DAO.Field

      'Getting the current MDB
      Set dbd = CurrentDb

      'For each table in the current MDB
      For Each tbd In dbd.TableDefs

      'For each field in each table
      For Each fld In tbd.Fields

      'Display in the immediate window (Main menu: View)
      Debug.Print "Table: " & tbd.Name & " Column: " & fld.Name
      Next
      Next
      Set dbd = Nothing
      End Sub

      Salem
      0
  2. Nico
     
    The idea is good, thank you, I didn't know about the name property of the recordset!
    However, in order to obtain all the field names from the database, a select * from all tables query would be very memory-intensive in my opinion (the database is quite heavy).

    I found a procedure like this that I think would be more suitable:

    Public Sub test()
    Dim Db As DAO.Database
    Dim tbd As DAO.TableDef
    Dim fld As DAO.Field
    Set Db=CurrentDb
    For Each tbd In Db.TableDefs
    For Each fld In tbd.Fields
    MsgBox "Table : " & tbd.Name & " Column : " & fld.Name
    Next
    Next
    End Sub

    Thank you, lupin, true to your post you have almost answered each of my questions :)
    0
  3. random Posted messages 1612 Status Member 155
     
    ```html but normally we do
    sub mestablesmaschamps
    dim x as dao.database
    dim y as dao.tabledef
    dim z as dao.field
    set x=currentdb()
    for each y in x.tabledefs
    msgbox("the table " & y.name & "has the following fields:")
    for each z in y
    msgbox(z.name 'here we can check the other properties of the field object)
    next z
    next y
    end sub

    it is good to plan a test to exclude system tables

    --
    isn’t life beautiful? ```
    0
    1. jmjousse
       
      Hello random
      First of all, I'm ashamed to be so bad at VB!!
      I would like to implement this code but instead of it attacking all the tables in the database, I would like it to target just one!!
      Can you tell me how to modify your code?
      Thanks in advance
      jm
      0
  4. belmekki
     
    ```html

    I'd like to know how to create a query that allows displaying names (candidate names) given that the last letter is entered via the keyboard.

    ```
    0