有些明白了,看看这个 如果要例子,来mail:[email protected] Option Explicit' database Private mdb As Database' table Private mstrTableDefName As String ' foreign table Private mstrForeignTableDefName As String' relation name Private mstrRelationName As String ' field name Private mstrFieldName As String ' foreign name Private mstrForeignName As String' control array constants Private Const optOneToOne = 0 Private Const optOneToMany = 1Private Const chkRefCascadeUpdates = 0 Private Const chkRefCascadeDeletes = 1Private Const cmdOK = 0 Private Const cmdCancel = 1' Public PropertiesPublic Property Set Database(db As DAO.Database) ' set database object and setup form ' assign the database object Set mdb = db
' populate the table combo boxes GetTables cboTableDefName GetTables cboForeignTableDefNameEnd Property' Private ProceduresPrivate Sub EnableOK() ' to create a relation, you need the following ' a table name ' a foreign table name ' a relation name ' a field name ' a foreign name for the field ' additionally, CreateRelation will fail if the ' field data types do not match correctly If mstrTableDefName = "" Or _ mstrForeignTableDefName = "" Or _ mstrRelationName = "" Or _ mstrFieldName = "" Or _ mstrForeignName = "" Then cmd(cmdOK).Enabled = False Else cmd(cmdOK).Enabled = True End IfEnd Sub Private Sub EnableRelation() ' enable/disable the relation frame If _ mstrTableDefName = "" Or _ mstrForeignTableDefName = "" _ Then fraRelation.Enabled = False Else fraRelation.Enabled = True End IfEnd Sub Private Sub GetTables(cbo As ComboBox) ' fill the table list combo Dim td As TableDef
With cbo ' clear what (if anything) is there .Clear For Each td In mdb.TableDefs ' check for system table If (td.Attributes And dbSystemObject) = 0 Then ' not a system table, add it .AddItem td.Name End If Next ' TableDef End WithEnd Sub Private Sub GetFields(cbo As ComboBox, strTableDefName As String) ' fill the field list combo Dim fld As Field
With cbo ' clear it .Clear For Each fld In mdb.TableDefs(strTableDefName).Fields ' add it .AddItem fld.Name Next ' Field End WithEnd Sub' Event ProceduresPrivate Sub Form_Load() On Error GoTo ProcError ' disable the relations frame fraRelation.Enabled = False
' disable the OK button cmd(cmdOK).Enabled = FalseProcExit: Exit Sub
ProcError: MsgBox "Error: " & Err.Number & vbCrLf & Err.Description Resume ProcExitEnd SubPrivate Sub cboForeignName_Click() On Error GoTo ProcError
mstrForeignName = cboForeignName.Text
EnableOKProcExit: Exit Sub
ProcError: MsgBox "Error: " & Err.Number & vbCrLf & Err.Description Resume ProcExitEnd SubPrivate Sub txtRelationName_Change() On Error GoTo ProcError mstrRelationName = txtRelationName EnableOKProcExit: Exit Sub
ProcError: MsgBox "Error: " & Err.Number & vbCrLf & Err.Description Resume ProcExitEnd SubPrivate Sub cmd_Click(Index As Integer) ' create the relation or unload On Error GoTo ProcError Screen.MousePointer = vbHourglass Select Case Index Case cmdOK ' create relation and unload CreateRelation Unload Me Case cmdCancel ' just unload Unload Me End SelectProcExit: Screen.MousePointer = vbDefault Exit Sub
ProcError: MsgBox "Error: " & Err.Number & vbCrLf & Err.Description Resume ProcExitEnd Sub Private Sub CreateRelation() ' create the relation ' called only from cmd(cmdOK) click event Dim rel As Relation Dim fld As Field Dim lngAttributes As Long
' setup attributes If optOneTo(optOneToOne) Then lngAttributes = dbRelationUnique End If If chkRef(chkRefCascadeUpdates) Then lngAttributes = lngAttributes Or dbRelationUpdateCascade End If If chkRef(chkRefCascadeDeletes) Then lngAttributes = lngAttributes Or dbRelationDeleteCascade End If ' create the relation Set rel = mdb.CreateRelation( _ mstrRelationName, _ mstrTableDefName, _ mstrForeignTableDefName, _ lngAttributes) Set fld = rel.CreateField(mstrFieldName) ' set the foreign name fld.ForeignName = mstrForeignName ' append the field to the relation rel.Fields.Append fld ' append the relation to the database mdb.Relations.Append relEnd Sub
如果要例子,来mail:[email protected]
Option Explicit' database
Private mdb As Database' table
Private mstrTableDefName As String
' foreign table
Private mstrForeignTableDefName As String' relation name
Private mstrRelationName As String
' field name
Private mstrFieldName As String
' foreign name
Private mstrForeignName As String' control array constants
Private Const optOneToOne = 0
Private Const optOneToMany = 1Private Const chkRefCascadeUpdates = 0
Private Const chkRefCascadeDeletes = 1Private Const cmdOK = 0
Private Const cmdCancel = 1' Public PropertiesPublic Property Set Database(db As DAO.Database)
' set database object and setup form ' assign the database object
Set mdb = db
' populate the table combo boxes
GetTables cboTableDefName
GetTables cboForeignTableDefNameEnd Property' Private ProceduresPrivate Sub EnableOK()
' to create a relation, you need the following
' a table name
' a foreign table name
' a relation name
' a field name
' a foreign name for the field
' additionally, CreateRelation will fail if the
' field data types do not match correctly If mstrTableDefName = "" Or _
mstrForeignTableDefName = "" Or _
mstrRelationName = "" Or _
mstrFieldName = "" Or _
mstrForeignName = "" Then
cmd(cmdOK).Enabled = False
Else
cmd(cmdOK).Enabled = True
End IfEnd Sub
Private Sub EnableRelation()
' enable/disable the relation frame If _
mstrTableDefName = "" Or _
mstrForeignTableDefName = "" _
Then
fraRelation.Enabled = False
Else
fraRelation.Enabled = True
End IfEnd Sub
Private Sub GetTables(cbo As ComboBox)
' fill the table list combo Dim td As TableDef
With cbo
' clear what (if anything) is there
.Clear
For Each td In mdb.TableDefs
' check for system table
If (td.Attributes And dbSystemObject) = 0 Then
' not a system table, add it
.AddItem td.Name
End If
Next ' TableDef
End WithEnd Sub
Private Sub GetFields(cbo As ComboBox, strTableDefName As String)
' fill the field list combo Dim fld As Field
With cbo
' clear it
.Clear
For Each fld In mdb.TableDefs(strTableDefName).Fields
' add it
.AddItem fld.Name
Next ' Field
End WithEnd Sub' Event ProceduresPrivate Sub Form_Load()
On Error GoTo ProcError ' disable the relations frame
fraRelation.Enabled = False
' disable the OK button
cmd(cmdOK).Enabled = FalseProcExit:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
Resume ProcExitEnd SubPrivate Sub cboTableDefName_Click()
On Error GoTo ProcError Screen.MousePointer = vbHourglass mstrTableDefName = cboTableDefName.Text
If mstrTableDefName <> "" Then
GetFields cboFieldName, mstrTableDefName
End If
EnableOK
EnableRelationProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
Resume ProcExitEnd SubPrivate Sub cboForeignTableDefName_Click()
On Error GoTo ProcError Screen.MousePointer = vbHourglass mstrForeignTableDefName = cboForeignTableDefName.Text
If mstrForeignTableDefName <> "" Then
GetFields cboForeignName, mstrForeignTableDefName
End If
EnableOK
EnableRelationProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
Resume ProcExitEnd SubPrivate Sub cboFieldName_Click()
On Error GoTo ProcError mstrFieldName = cboFieldName.Text
EnableOKProcExit:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
Resume ProcExitEnd SubPrivate Sub cboForeignName_Click()
On Error GoTo ProcError
mstrForeignName = cboForeignName.Text
EnableOKProcExit:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
Resume ProcExitEnd SubPrivate Sub txtRelationName_Change()
On Error GoTo ProcError mstrRelationName = txtRelationName EnableOKProcExit:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
Resume ProcExitEnd SubPrivate Sub cmd_Click(Index As Integer)
' create the relation or unload
On Error GoTo ProcError Screen.MousePointer = vbHourglass Select Case Index
Case cmdOK
' create relation and unload
CreateRelation
Unload Me
Case cmdCancel
' just unload
Unload Me
End SelectProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
Resume ProcExitEnd Sub
Private Sub CreateRelation()
' create the relation
' called only from cmd(cmdOK) click event Dim rel As Relation
Dim fld As Field
Dim lngAttributes As Long
' setup attributes
If optOneTo(optOneToOne) Then
lngAttributes = dbRelationUnique
End If
If chkRef(chkRefCascadeUpdates) Then
lngAttributes = lngAttributes Or dbRelationUpdateCascade
End If
If chkRef(chkRefCascadeDeletes) Then
lngAttributes = lngAttributes Or dbRelationDeleteCascade
End If
' create the relation
Set rel = mdb.CreateRelation( _
mstrRelationName, _
mstrTableDefName, _
mstrForeignTableDefName, _
lngAttributes)
Set fld = rel.CreateField(mstrFieldName)
' set the foreign name
fld.ForeignName = mstrForeignName
' append the field to the relation
rel.Fields.Append fld
' append the relation to the database
mdb.Relations.Append relEnd Sub
然后创建并将field对象添加到fields集合中,最后调用append方法将relation添加到relations集合中
information 字段为 ,FileID, IndexID(索引)
IndexStructure 字段为,ID(主键),parentID,
建立关系如下:
Set relNew = dbinfo.CreateRelation("asdf"(关系名), "Information", "IndexStructure", dbRelationUpdateCascade)
Set fldField = relNew.CreateField("ID", dbInteger)
fldField.ForeignName = "IndexID"
relNew.Fields.Append fldField
dbinfo.Relations.Append relNew错误:invalid field definition "IndexID" in definition of index or relationship
请解释原因 。谢谢