请问是否有可能在MS Access表中列出所有字段名称?
我经常使用微软Access。
我所知道的唯一方法是使用VBA,例如定义一个记录集并循环遍历字段。
示例:
Sub ListFields()
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
Debug.Print ss
End Sub
字符串变量ss
将包含名为"SomeTable"的表中所有列名以逗号分隔的列表。
通过对逻辑进行一些重新格式化,您应该能够将此数据插入到另一个表中,然后查询出来。
这有帮助吗?
这个版本易于运行,并且可以直接粘贴到Access中。将此函数添加到模块中,按F5运行,然后从输入框中复制结果:
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
替代方案:
用户user1003916提供了一种替代InputBox的方法,以克服1024字符限制(我尚未测试):
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
数据库工具 > 数据库文档生成器
,选择表并按下“确定”即可。这个SQL语句适用于Access 2016中的查询,而不是表格,但可能会很有用。
SELECT MSysObjects.Name AS QueryName,
IIf(IsNull([Name1]),
Right([Expression],Len([Expression])-InStr(1,[Expression],".")),[name1])
AS FieldName
FROM MSysQueries INNER JOIN MSysObjects
ON MSysQueries.ObjectId = MSysObjects.Id
WHERE MSysQueries.Attribute=6;
已经有一些好的答案了,但我决定加入自己的想法。希望它们是不言自明的。
我使用这个来构建一个字段名数组:
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
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
然后添加一个宏RunCode ListTdfFields()并运行它。输出将被发送到模块的VBA设计视图的Immediate窗口。
String query="SELECT TOP 1 * FROM mytable";
ResultSet rs=connectionSQL.createStatement().executeQuery(query);
ResultSetMetaData rsm=rs.getMetaData();
int colcount=rsm.getColumnCount();
for(int f=1; f<=colcount; f++) {
String coltype=rsm.getColumnTypeName(f);
String colname=rsm.getColumnName(f);
.... (whatever)
}
所以这就是你的列名和数据类型。 在MSACCESS中运行得很好。
我来到这里寻找同样的需求,参考了这个帖子后,为我的需求起草了下面的代码。源表中的字段名将被添加到一个数组中,然后这些字段名将被分配给第二个表。在这里分享一下,这可能会帮助以后的某个人。
Public Sub FieldName_Change()
Dim intNumberOfFields, intX As Integer
Dim txtTableName,txttmpTableName txtFieldName() As String
intNumberOfFields = GetFieldNames(txtTableName, txtFieldName())
For intX = 1 To intNumberOfFields
CurrentDb.TableDefs(txttmpTableName).Fields("F" & intX).Name = txtFieldName(intX)
Next intX
End Sub
Public Function GetFieldNames(ByVal txtTableName As String, ByRef txtFiledName() As String) As Integer
Dim rs As DAO.Recordset
Dim n As Long
Dim sResult As String
Set rs = CurrentDb.OpenRecordset(txtTableName)
ReDim txtFiledName(rs.Fields.Count)
With rs
For n = 0 To .Fields.Count - 1
txtFiledName(n + 1) = .Fields(n).Name
Next n
.Close
GetFieldNames = n
End With
Set rs = Nothing
End Function
Select object_Name(object_ID),definition from Pastries.SQL_Modules where object_Name(object_ID)='custard'
。我相信Access没有提供这种对象可见性。 - Smandoli