473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

VBA - Automatically create relationships after importing Excel to Access

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
0 1601

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

Similar topics

5
by: Alan | last post by:
Hi there, Are there Excel charting gurus here?? If so then please read on... Sorry for the cross-post but I'm not familiar with the Excel groups. I've posted to asp.general because if I have...
1
by: poohnie08 | last post by:
i have a excel spreadsheet showing staff name, date,work hour, ot hour, slot1, slot2, slot3, slot4 and others). The "()" will keep repeating from day 1 until end of month. eg in excel spreadsheet,...
5
by: hharriel | last post by:
Hi, I am hoping someone can help me with an issue I am having with excel and ms access. I have collected data (which are in individual excel files) from 49 different school districts. All...
4
by: knix | last post by:
I have this access project consisting of multiple tables that are linked together in a relationship. I would like to migrate the consolidated information through appending in a datasheet form or...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.