Importing relationship

G

Guest

I am using the following code to import relationships from one db to another.
It works create as long as enforce referential integrity is not chosen.
When it is chosen, I get Error 3284, Index Already Exists. Any ideas on a
better way to do this import?


Thanks,

Kim

relationcount = 0

For i = 0 To dbMaster.Relations.Count - 1
Set MasterRel = dbMaster.Relations(i)


Set SlaveRel = dbSlave.CreateRelation(MasterRel.Name, _
MasterRel.Table, MasterRel.ForeignTable, MasterRel.Attributes)

' Set bad field flag to false.
ErrBadField = False

' Loop through all fields in that relation.
For r = 0 To MasterRel.Fields.Count - 1
Set MasterField = MasterRel.Fields(r)


Set SlaveField = SlaveRel.CreateField(MasterField.Name)
SlaveField.ForeignName = MasterField.ForeignName

' Check for bad fields.
'On Error Resume Next
SlaveRel.Fields.Append SlaveField
If Err <> False Then ErrBadField = True
'On Error GoTo 0
Next r


If ErrBadField = True Then
Else
'On Error Resume Next
dbSlave.Relations.Append SlaveRel
If Err <> False Then

Else
'Relationship Count
relationcount = relationcount + 1

End If
'On Error GoTo 0
End If
Next i
 
A

Allen Browne

When you create a relationship with referential integrity enforced, Access
creates a hidden index so it can enforce the relation. You can see these
hidden indexes if you loop through the indexes collection.

This means that the average Access database has so many duplicated indexes
defined as to be ridiculous. Unless you remove the entries from:
Tools | Options | Tables/Queries | AutoIndex ...
it automaticaly gives you an index on any field name that ends with id,
code, num, etc. Then if you manually index your foreign keys, you have a
duplicated index. Then when you create the relation, you get a 3rd one! So
if the database you are importing from or the target database already has
all these indexes, the process can fail.
 
S

Steve Huff

Wow - It's amazing how you can think you know everything about Access after
working with it for about 8 years but then you hear something that would
seem obvious and your like HUH? - Access auto creates indexs on certain
field names - that is wild. I never knew that. OH well, just thought I'd
share.

--Steve Huff
 
A

Allen Browne

Yes, Steve, and sharing info with each is how most of us learn about these
things, so the newsgroups are invaluable.
 
G

Guest

Ok. If I loop thru the indexes and delete the hidden indexes, would this
solve the problem?

Kim
 
A

Allen Browne

Hi Kim

I'm not clear here on what you need to achieve. To delete the hidden
indexes, you would need to delete the relationships that depend on those
indexes first. Doesn't that defeat the purpose of what you are trying to
copy?

A few years ago, I wrote a quick'n'dirty utility to examine the indexes and
suggest which ones to delete. It recommended retaining the primary key
index, and indexes involved in relationships, and removing others that were
based on exactly the same field(s). Now if the index name Access would like
to use for the index (typically "Table1Table2") is already in use, the
hidden index is named with a GUID. The feedback I received was that
developers did not like losing the well named index and retaining the GUID
name, so I never finished/released the utiility.
 
G

Guest

Allen,

This is what I am trying to achieve. I have 3 dbs. 2 are considered child
dbs. The child dbs update the master db if certain criteria exists. When
this update is complete, the master db tables are exported to the child dbs.
I need to export the relationships also. These 3 dbs in the end are carbon
copies of each other. The module that is running this update, is not in the
master or the child db, so I cannot use transferdatabase.
 
A

Allen Browne

Are we talking about random structural changes here, or just data, or both?

If it's just data, I don't see the need to fiddle with the relations at all.
You should be able to choose the correct order for importing data, so the
lookup tables get their records first, and then the related tables will
accept their updates. Even if you did need to redo the relations afterwards,
that should be easy enough if the relations to recreate are predefined.

If you want to propagate random structural changes, there is a fair bit
involved, so you might consider buying a utility such as:
http://www.ssw.com.au/ssw/DataPRO/

Presumably the built-in replication is not satisfactory, as there is not
really one "master" here. And presumably the 3 copies are separate from each
other so they cannot share a common back end on a LAN or via Terminal
Services.
 
G

Guest

It is just data that changes. So instead of blowing away the table and
reimporting it, I should use the approach of deleting the data from the child
and appending the data from the master to the child dbs? I was also thinking
about deleting the child dbs after the the updates are made and recreating
the dbs from the master. However, I am not sure how to import the forms,
queries, modules without using Docmd.Transferdatabase. The forms, queries
and modules would be imported from an archive child db. The master db only
has tables.

I appreciate all your help on this one.
 
A

Allen Browne

Some possibilities:
- TransferDatabase
- CopyObject
- "SELECT * INTO [" & strTable & "] IN """ & dbBackup.Name & """ FROM [" &
strTable & "];"
 
T

Tony Toews

Allen Browne said:
Yes, Steve, and sharing info with each is how most of us learn about these
things, so the newsgroups are invaluable.
Click to expand...

Agreed. Joan Wild mentioned something last week that I never knew.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
S

sugulusubi

Kim said:
I am using the following code to import relationships from one db to another.
It works create as long as enforce referential integrity is not chosen.
When it is chosen, I get Error 3284, Index Already Exists. Any ideas on a
better way to do this import?


Thanks,

Kim

relationcount = 0

For i = 0 To dbMaster.Relations.Count - 1
Set MasterRel = dbMaster.Relations(i)


Set SlaveRel = dbSlave.CreateRelation(MasterRel.Name, _
MasterRel.Table, MasterRel.ForeignTable, MasterRel.Attributes)

' Set bad field flag to false.
ErrBadField = False

' Loop through all fields in that relation.
For r = 0 To MasterRel.Fields.Count - 1
Set MasterField = MasterRel.Fields(r)


Set SlaveField = SlaveRel.CreateField(MasterField.Name)
SlaveField.ForeignName = MasterField.ForeignName

' Check for bad fields.
'On Error Resume Next
SlaveRel.Fields.Append SlaveField
If Err <> False Then ErrBadField = True
'On Error GoTo 0
Next r


If ErrBadField = True Then
Else
'On Error Resume Next
dbSlave.Relations.Append SlaveRel
If Err <> False Then

Else
'Relationship Count
relationcount = relationcount + 1

End If
'On Error GoTo 0
End If
Next i
Click to expand...
 
Top