By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,263 Members | 2,640 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,263 IT Pros & Developers. It's quick & easy.

VBA - Automatically create relationships after importing Excel to Access

P: 3
Hi,

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
  1.  
  2. Private Sub createRelationshipAndIndex()
  3.  
  4. Dim directory As String
  5. Dim oRel As DAO.Relation
  6. Dim oDB As DAO.Database
  7. Dim oTable1 As DAO.TableDef
  8. Dim oTable2 As DAO.TableDef
  9. Dim oTable3 As DAO.TableDef
  10. Dim oIndex As DAO.Index
  11. Dim Team As Field
  12.  
  13. directory = RetrievePathFile & "\"
  14. Set oDB = Application.CurrentDb
  15.  
  16. 'Create an index on TableName
  17. Set oTable1 = oDB.TableDefs("TableName")
  18. Set oIndex = oTable1.CreateIndex
  19. With oIndex
  20. .Name = "Field1Index"
  21. .Fields.Append .CreateField("Key field")
  22. .Primary = True
  23.  
  24. End With
  25. oTable1.Indexes.Append oIndex
  26.  
  27.  
------------------------------------
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
  1. Public Function CreateRelationship(table1 As String, table2 As String, Key As String, Link As String) As Boolean
  2.     Dim db As DAO.Database
  3.     Dim tdf1 As DAO.TableDef
  4.     Dim tdf2 As DAO.TableDef
  5.     Dim rels As DAO.Relations
  6.     Dim rel As DAO.Relation
  7.  
  8.     Set db = CurrentDb
  9.     Set tdf1 = db.TableDefs(table1)
  10.     Set tdf2 = db.TableDefs(table2)
  11.     Set rels = db.Relations
  12.  
  13.     For Each rel In rels
  14.         If rel.Name = "Relationship" & table1 & table2 Then
  15.             rels.Delete ("Relationship" & table1 & table2)
  16.         End If
  17.     Next
  18.  
  19.     Set rel = db.CreateRelation("Relationship" & table1 & table2, tdf1.Name, tdf2.Name, dbRelationUpdateCascade)
  20.  
  21.     rel.Fields.Append rel.CreateField(Key)
  22.     rel.Fields(Key).ForeignName = Link
  23.     rels.Append rel
  24.  
  25.     Set rels = Nothing
  26.     Set tdf = Nothing
  27.     Set tdf2 = Nothing
  28.  
  29. End Function
  30.  
Feb 19 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.