How to specify a field name in a table in Access using SQL

Question

Tell me, please, is it possible to list all the field names in the MS Access table?

Answer 1

I work too much in ms-access.

The only way I know this is to use vba and define, for example, a set of records, and scroll through the fields.

For example:

dim rst as new adodb.recordset rst.open "SELECT * FROM SomeTable", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly ' Note: adOpenForwardOnly and adLockReadOnly are the default values ' ' for the CursorType and LockType arguments, so they are optional here ' ' and are shown only for completeness ' dim ii as integer dim ss as string for ii = 0 to rst.fields.count - 1 ss = ss & "," & rst.fields(ii).name next ii 

The ss string variable will contain a comma-separated list of all column names in the table named "SomeTable".

With a little reformatting of the logic, you will need to paste this data into another table, if you want, then query it.

Does it help?

Answer 2

This version is easy to launch and insert directly into Access. Add this function to the module, start with F5 and copy the result from the input window:

 Public Function FieldNames() As String Dim sTable As String Dim rs As DAO.Recordset Dim n As Long Dim sResult As String sTable = InputBox("Name of table?") If sTable = "" Then Exit Function End If Set rs = CurrentDb.OpenRecordset(sTable) With rs For n = 0 To .Fields.Count - 1 sResult = sResult & .Fields(n).Name & vbCrLf Next 'n .Close End With Set rs = Nothing InputBox "Result:" & vbCrLf & vbCrLf _ & "Copy this text (it looks jumbled, but it has one field on each line)", _ "FieldNames()", sResult End Function 

Alternative output:

User user1003916 provided an alternative to InputBox to overcome the 1024 character limit (I haven't tested this yet):

 Sub CopyText(Text As String) 'VBA Macro using late binding to copy text to clipboard. 'By Justin Kay, 8/15/2014 Dim MSForms_DataObject As Object Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") MSForms_DataObject.SetText Text MSForms_DataObject.PutInClipboard Set MSForms_DataObject = Nothing End Sub 

Answer 3

UPDATE: USE THIS SQL QUERY, YOU SHOULD USE THE TOOL AS A DRIVER. ACCESS CLIENT DOES NOT ALLOW YOU TO ASK FOR THIS HIDDEN DESIGNS.

YIKES! IMO: I can’t imagine that I want to dive into the dark underbelly of VBA.

How to get columns of an access table in SQL

 SELECT * FROM information_schema.columns WHERE TABLE_NAME="YOUR_TABLE_NAME" AND TABLE_SCHEMA="PUBLIC" 

PS I noticed that Access called my "PUBLIC" scheme

Access 2016 was used above and was tested through ODBC and jdbc: ucanaccess and works like a charm.

Output example

Answer 4

You can just use the Documenter tool. Go to Database Tools > Database Documenter , select a table and click OK.

Answer 5

This task seems to have been easier in older times. Probably this answer is highly version dependent. It works for me in quick testing with Access 2007 DB:

 select Specs.SpecName AS TableName, Columns.FieldName from MSysIMEXColumns Columns inner join MSysIMEXSpecs Specs on Specs.SpecID = Columns.SpecID order by Specs.SpecName, Columns.FieldName 

Answer 6

The quick and dirty method includes Excel. Follow these steps:

• Open the table in the Datasheet view.
• Export to an Excel file using the specific procedure for your version of Access.
• Open the Excel file (if it is not already open).
• Select and copy the first row containing the headers.
• Add another sheet to the book (if one does not exist).
• Press A1.
• Special insert> Transpose

Fields will be inserted into one column. To find out the number of your Field Index, in cell B1, enter "0", then the series will be filled to the last line of field numbers.

In addition, you can sort the column in alphabetical order, especially for old flat files containing dozens of fields. It really saves a lot of time when I try to convert a flatfile to a relational model.

Answer 7

Give it back ...

  private void Button_OldDDLDump_Click(object sender, EventArgs e) { string _cnstr = "connectionstringhere"; OleDbConnection _cn = new OleDbConnection(_cnstr); try { _cn.Open(); System.Data.DataTable _dt = null; try { _dt = _cn.GetSchema("tables"); m_msghelper.AppendArray( DataTableHelper.DataTableToString(_dt) ); } catch (Exception _ex) { System.Diagnostics.Debug.WriteLine(_ex.ToString()); } finally { _dt.Dispose(); } } catch (Exception _ex) { System.Diagnostics.Debug.WriteLine(_ex.ToString()); } finally { _cn.Close(); } } 

A helper method for outputting the database structure to an array of strings is here.

 public static class DataTableHelper { public static string[] DataTableToString( System.Data.DataTable dt ) { List<string> _retval = new List<string>(); foreach (System.Data.DataRow row in dt.Rows) { foreach (System.Data.DataColumn col in dt.Columns) { _retval.Add( string.Format("{0} = {1}", col.ColumnName, row[col]) ); } _retval.Add( "============================"); } return _retval.ToArray(); } } 

Answer 8

I would like to share this VBA solution, which I did not write, only slightly modified (the table name has changed to use "SourceTable"). After starting, you can query the created table. It uses hidden system tables.

 Sub GetField2Description() '************************************************* ********* 'Purpose: 1) Deletes and recreates a table (tblFields) ' 2) Queries table MSysObjects to return names of ' all tables in the database ' 3) Populates tblFields 'Coded by: raskew 'Inputs: From debug window: ' Call GetField2Description 'Output: See tblFields '************************************************* ********* Dim db As DAO.Database, td As TableDef Dim rs As Recordset, rs2 As Recordset Dim Test As String, NameHold As String Dim typehold As String, SizeHold As String Dim fielddescription As String, tName As String Dim n As Long, i As Long Dim fld As Field, strSQL As String n = 0 Set db = CurrentDb ' Trap for any errors. On Error Resume Next tName = "tblFields" 'Does table "tblFields" exist? If true, delete it; DoCmd.SetWarnings False DoCmd.DeleteObject acTable, "tblFields" DoCmd.SetWarnings True 'End If 'Create new tblTable db.Execute "CREATE TABLE tblFields(Object TEXT (55), FieldName TEXT (55), FieldType TEXT (20), FieldSize Long, FieldAttributes Long, FldDescription TEXT (20));" strSQL = "SELECT MSysObjects.Name, MSysObjects.Type From MsysObjects WHERE" strSQL = strSQL + "((MSysObjects.Type)=1)" strSQL = strSQL + "ORDER BY MSysObjects.Name;" Set rs = db.OpenRecordset(strSQL) If Not rs.BOF Then ' Get number of records in recordset rs.MoveLast n = rs.RecordCount rs.MoveFirst End If Set rs2 = db.OpenRecordset("tblFields") For i = 0 To n - 1 fielddescription = " " Set td = db.TableDefs(i) 'Skip over any MSys objects If Left(rs!Name, 4) <> "MSys" And Left(rs!Name, 1) <> "~" Then NameHold = rs!Name On Error Resume Next For Each fld In td.Fields tableName = fld.SourceTable fielddescription = fld.Name typehold = FieldType(fld.Type) SizeHold = fld.Size rs2.AddNew rs2!Object = tableName rs2!FieldName = fielddescription rs2!FieldType = typehold rs2!FieldSize = SizeHold rs2!FieldAttributes = fld.Attributes rs2!FldDescription = fld.Properties("description") rs2.Update Next fld Resume Next End If rs.MoveNext Next i rs.Close rs2.Close db.Close End Sub Function FieldType(intType As Integer) As String Select Case intType Case dbBoolean FieldType = "dbBoolean" '1 Case dbByte FieldType = "dbByte" '2 Case dbInteger FieldType = "dbInteger" '3 Case dbLong FieldType = "dbLong" '4 Case dbCurrency FieldType = "dbCurrency" '5 Case dbSingle FieldType = "dbSingle" '6 Case dbDouble FieldType = "dbDouble" '7 Case dbDate FieldType = "dbDate" '8 Case dbBinary FieldType = "dbBinary" '9 Case dbText FieldType = "dbText" '10 Case dbLongBinary FieldType = "dbLongBinary" '11 Case dbMemo FieldType = "dbMemo" '12 Case dbGUID FieldType = "dbGUID" '15 End Select End Function 

Answer 9

This is not SQL, but this question is Google’s best result for someone like me who just needs to list the field names needed to select a query, since Access does not support "* except foo, bar" to get 99% of the table.

The answer is adapted from Patrick Wood's answer social.msdn.com, Access MVP https://social.msdn.microsoft.com/Forums/office/en-US/1fe5546b-db3f-4e17-9bf8-04f4dee233b7/how-to-list- all-the-field-names-in-a-specified-table? forum = accessdev

Change the tab name to your name in the module. This function must be at the global level:

 Function ListTdfFields() ' NOT doing DIMs, since then you must enable/attach ADODB or DAO ' Dim db As ADO.Database Set db = CurrentDb tablename = "tblProductLicense" ' <=== YOUR TABLE NAME HERE Set tdf = db.TableDefs(tablename) For Each fld In tdf.Fields Debug.Print tablename; ".["; fld.Name; "]," ; ' remove ending ; for 1 line per field Next Debug.Print "" Set tdf = Nothing Set db = Nothing End Function 

Then add the RunCode ListTdfFields () macro and run it. The output will be sent to the Immediate window for the VBA design view for the module.

Answer 10

Assembly Request:

 SELECT Table_Name.* FROM Table_Name WHERE False; 

Export to Excel You will have each field name in one line without any data. If you select a row and copy, you can paste special> transpose and put them all in one column.

Answer 11

There are already some good answers, but I decided to add my own twist. I hope they speak for themselves.

Using:

• getFieldNames (TableName: = "Table1", IncludeBrackets: = True, Separator = vbNewLine, CopyToClipboard: = True)
• getFieldNames (TableName: = "Table1", IncludeBrackets: = True, CopyToClipboard: = True)
• getFieldNames (TableName: = "Table1", IncludeBrackets: = True)
• getFieldNames (TableName: = "Table1")

I use this to create an array of field names:

• Chr (34) & getFieldNames (TableName: = "Table1", IncludeBrackets: = False, Separator: = Chr (34) & "," & Chr (34)) & Chr (34)

---

 Function getFieldNames(ByVal TableName As String, Optional ByVal IncludeBrackets As Boolean, Optional ByVal Delimiter As String = ", ", Optional ByVal CopyToClipboard As Boolean) As String Dim rs As DAO.Recordset On Error Resume Next Set rs = CurrentDb.OpenRecordset(TableName) On Error GoTo 0 If rs Is Nothing Then Exit Function Dim results() As String ReDim results(rs.Fields.Count - 1) Dim n As Long For n = 0 To rs.Fields.Count - 1 results(n) = rs.Fields(n).Name Next rs.Close Dim result As String If IncludeBrackets Then result = "[" & Join(results, "]" & Delimiter & "[") & "]" Else result = Join(results, Delimiter) End If If CopyToClipboard Then With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText result .PutInClipboard End With End If getFieldNames = result End Function 

Answer 12

You can use Adox as a metadata library.

Answer 13

select column_name from information_schema.columns where table_name = 'table'