在VBA中索引（Access 2003） - 字段关联 [英] Indexing in VBA (Access 2003) - field association

问题描述

基于本教程页面，Microsoft Access如何知道为索引字段集合创建的字段是否与TableDef字段集合中的等效字段相关联？

Based on this tutorial page, how does Microsoft Access know that a field created for an index fields collection associates to an equivalent field in the TableDef fields collection?

即使在此Microsoft支持页面，为索引创建字段，然后将其附加到索引的字段集合中：

Even in this Microsoft Support page, fields are created for the index and then appended to the index's fields collection:

...
'Copy Indexes
For I1 = 0 To SourceTableDef.Indexes.Count - 1
  Set SI = SourceTableDef.Indexes(I1)
  If Not SI.Foreign Then         ' Foreign indexes are added by relationships
    Set I = T.CreateIndex()
    ' Copy Jet Properties
      On Error Resume Next
      For P1 = 0 To I.Properties.Count - 1
        I.Properties(P1).Value = SI.Properties(P1).Value
      Next P1
      On Error GoTo 0
    ' Copy Fields
      For f1 = 0 To SI.Fields.Count - 1
        Set F = T.CreateField(SI.Fields(f1).Name, T.Fields(SI.Fields(f1).Name).Type)
        I.Fields.Append F
      Next f1
    T.Indexes.Append I
  End If
Next I1
...

我不能简单地添加TableDef的字段集合中的现有字段？这没什么意义，似乎很少有凝聚力。

Can't I simply add the existing field from the TableDef's fields collection? This makes little sense and seems to have very little in the way of cohesion.

我实际测试过代码这里基本上就是我想做的......但它失败了在这一行有一个未定义的对象错误：

I actually tested the code here which is basically what I want to do ... but it fails with an undefined object error on this line:

Set F = T.CreateField(SI.Fields(f1).Name, T.Fields(SI.Fields(f1).Name).Type)

...当我们改变这一点时，我们会有各种各样的乐趣。

...and we have all sorts of fun when we change this.

（2016年5月23日）此外，此脚本出现错了 - 第二个论点实际上不应该存在，这是不必要的。省略它会导致更多错误！哈！我的尾巴在哪里？我觉得我应该追逐它。

(23/05/2016) Further, this script appears to be wrong - the second argument shouldn't actually be there, it's unnecessary. Omitting it causes further errors! Ha! Where's my tail? I'm getting the feeling that I should be chasing it.

我决定遵循HansUp的主导并使用DDL - 比试图浏览与对象操作相关的问题要容易得多（尽管在最终的代码设计中存在一定程度的这一点）......

I decided to follow HansUp's lead and use DDL - far easier than attempting to trawl through the problems associated to the manipulation of objects (though there was some level of this in the final code design)...

Option Compare Database

Public Const cFname As String = "drm\drmData2016.accdb"
Public Const cPropNotFound As Integer = 3270
Public Const cNotSupported As Integer = 3251
Public Const cInvalidOp As Integer = 3219

Public Sub GenerateTables()

    OpenLog

    'Initalise...
    Dim db As Database
    Dim tdb As Database

    Dim ts As TableDef, tt As TableDef
    Dim p As Property
    Dim f As Field, ft As Field
    Dim i As Index
    Dim s As String, t As String
    Dim x As Boolean

    Set db = CurrentDb
    If Dir$(cFname) <> "" Then Kill cFname
    Set tdb = Application.DBEngine.CreateDatabase(cFname, dbLangGeneral, dbVersion140)
    WriteLog "Created database " & cFname & "."

    'Create the tables...
    WriteLog "Creating TableDefs...", 1
    For Each ts In db.TableDefs
        If Not StartsWith(ts.Name, "msys", "~", "$", "Name AutoCorrect") And Not EndsWith(ts.Name, "_xrep") Then
            s = "SELECT "
            For Each f In ts.Fields
                If Not StartsWith(f.Name, "s_", "S_") Then s = s & "[" & f.Name & "], "
            Next f
            s = Left$(s, Len(s) - 2) & " INTO [" & ts.Name & "] IN """ & cFname & """ FROM [" & ts.Name & "];"
            On Error Resume Next
                db.Execute s
                If Err.Number = 0 Then
                    WriteLog "Created [" & ts.Name & "] using " & s, 2
                Else
                    WriteLog "Failed to create [" & ts.Name & "].", 2
                    WriteLog "Error " & Err.Number & ": " & Err.Description, 3
                    WriteLog "SQL: " & s, 3
                    Err.Clear
                End If
                tdb.TableDefs.Refresh
            On Error GoTo 0
        End If
    Next ts

    'Copy the properties...
    WriteLog "Tables...", 1
    For Each ts In db.TableDefs

        If Not StartsWith(ts.Name, "msys", "~", "$", "Name Autocorrect") And Not EndsWith(ts.Name, "_xrep") Then

            Set tt = tdb.TableDefs(ts.Name)

            WriteLog ts.Name, 2

            WriteLog "Table Properties...", 3
            'Table properties...
            For Each p In ts.Properties
                On Error Resume Next
                    tt.Properties(p.Name) = p.value
                    If Err.Number = 0 Then
                        WriteLog p.Name & " = " & p.value, 3
                    Else
                        WriteLog "Error setting " & p.Name, 3
                        WriteLog Err.Number & ": " & Err.Description, 4
                        Err.Clear
                    End If
                On Error GoTo 0
            Next p

            'Field properties...
            WriteLog "Fields...", 3
            For Each f In ts.Fields
                If Not StartsWith(f.Name, "s_") Then
                    Set ft = tt.Fields(f.Name)
                    WriteLog f.Name, 3
                    WriteLog "Properties...", 3
                    For Each p In f.Properties
                        On Error Resume Next
                            ft.Properties(p.Name).value = p.value
                            Select Case Err.Number
                                Case 0
                                    'Normal...
                                    WriteLog p.Name & " = " & p.value, 4
                                Case cPropNotFound
                                    'Create the property...
                                    Dim np As Property
                                    Set np = ft.CreateProperty(p.Name, p.Type, p.value)
                                    ft.Properties.Append np
                                    ft.Properties.Refresh
                                    WriteLog "Created property " & p.Name & ", value of " & p.value, 4
                                Case cNotSupported, cInvalidOp
                                    'We're not worried about these values - simply skip over them...
                                Case Else
                                    WriteLog "Failed to create or change property " & p.Name & ".", 4
                                    WriteLog "Error " & Err.Number & ": " & Err.Description, 5
                                    Err.Clear
                            End Select
                        On Error GoTo 0
                    Next p
                End If
            Next f

            'Create the indexes...
            WriteLog "Table indexes...", 2
            For Each i In ts.Indexes
                x = False
                s = "CREATE "
                If i.Unique Then s = s & "UNIQUE "
                s = s & "INDEX [" & i.Name & "] ON [" & ts.Name & "] ("
                For Each f In i.Fields
                    s = s & "[" & f.Name & "], "
                    'Just make sure we're not dealing with replication fields...
                    x = StartsWith(f.Name, "s_")
                Next f
                'We only want
                If Not x Then
                    WriteLog i.Name, 3
                    s = Left$(s, Len(s) - 2) & ") "
                    If i.Primary Or i.IgnoreNulls Or i.Required Then
                        s = s & "WITH "
                        If i.Primary Then s = s & "PRIMARY "
                        If i.IgnoreNulls Then s = s & "IGNORE NULL "
                        If i.Required Then s = s & "DISALLOW NULL "
                    End If
                    s = s & ";"
                    On Error Resume Next
                        tdb.Execute s
                        Select Case Err.Number
                            'Note: used select case just in case I need to add extra error numbers...
                            Case 0
                                'Normal...
                                WriteLog "Created index [" & i.Name & "] using " & s, 4
                            Case Else
                                WriteLog "Failed to create index [" & ts.Name & "].", 4
                                WriteLog "Error " & Err.Number & ": " & Err.Description, 5
                                WriteLog "SQL: " & s, 3
                                Err.Clear
                        End Select
                    On Error GoTo 0
                End If
            Next i

        End If

    Next ts

    'Belt and braces tidy-up...
    Set p = Nothing
    Set f = Nothing
    Set ft = Nothing
    Set i = Nothing
    Set ts = Nothing
    Set tt = Nothing

    tdb.Close
    Set tdb = Nothing
    Set db = Nothing

    WriteLog "Closed database."

    WriteLog "Finished.", , False
    CloseLog

End Sub

推荐答案

Microsoft Access如何知道为索引
字段集创建的字段与等效字段关联在TableDef
字段集合中？

how does Microsoft Access know that a field created for an index fields collection associates to an equivalent field in the TableDef fields collection?

它根据名称进行检查。新索引字段的名称必须存在于 TableDef 中，并且该字段的数据类型必须是可索引的数据类型。如果不满足其中任何一个条件，您将收到错误消息。

It checks based on the name. The name of the new index field must exist in the TableDef and that field's datatype must be one which is indexable. If either of those conditions is not satisfied, you will get an error message.

这篇关于在VBA中索引（Access 2003） - 字段关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！