I have succeeded in adding automated relationships with refference tables after importing an excel file created from a query to an Access database.
However I have the following problem:
When there are values in the excel file which was imported that do not appear in the refference tables because they are not entered yet, or in case they are misspelled, the code blocks and gives me an error message. As I am not that much of a coding engineer, I am not into working with error messages.
I would want that either the new values are added to the refference table OR that the entries in the transferred excel file are deleted/put into another (log)table.
I would really appreciate help !
Thx,
Steven
---------------------------------
The code for creating the index in the inserted table, repeated for each table that needs a key:
Expand|Select|Wrap|Line Numbers
- Private Sub createRelationshipAndIndex()
- Dim directory As String
- Dim oRel As DAO.Relation
- Dim oDB As DAO.Database
- Dim oTable1 As DAO.TableDef
- Dim oTable2 As DAO.TableDef
- Dim oTable3 As DAO.TableDef
- Dim oIndex As DAO.Index
- Dim Team As Field
- directory = RetrievePathFile & "\"
- Set oDB = Application.CurrentDb
- 'Create an index on TableName
- Set oTable1 = oDB.TableDefs("TableName")
- Set oIndex = oTable1.CreateIndex
- With oIndex
- .Name = "Field1Index"
- .Fields.Append .CreateField("Key field")
- .Primary = True
- End With
- oTable1.Indexes.Append oIndex
The code for creating the Relationships (I first delete the old ones, as the tables are each time deleted as well when I do the import of the Excel file):
Expand|Select|Wrap|Line Numbers
- Public Function CreateRelationship(table1 As String, table2 As String, Key As String, Link As String) As Boolean
- Dim db As DAO.Database
- Dim tdf1 As DAO.TableDef
- Dim tdf2 As DAO.TableDef
- Dim rels As DAO.Relations
- Dim rel As DAO.Relation
- Set db = CurrentDb
- Set tdf1 = db.TableDefs(table1)
- Set tdf2 = db.TableDefs(table2)
- Set rels = db.Relations
- For Each rel In rels
- If rel.Name = "Relationship" & table1 & table2 Then
- rels.Delete ("Relationship" & table1 & table2)
- End If
- Next
- Set rel = db.CreateRelation("Relationship" & table1 & table2, tdf1.Name, tdf2.Name, dbRelationUpdateCascade)
- rel.Fields.Append rel.CreateField(Key)
- rel.Fields(Key).ForeignName = Link
- rels.Append rel
- Set rels = Nothing
- Set tdf = Nothing
- Set tdf2 = Nothing
- End Function