[Access & SQL] column names of a table
Solved
Nicolas
-
salem -
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.
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
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.
Lupin
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
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 :)
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 :)
```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? ```
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? ```
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