Lotus Notes Document Import and Primary Keys.

[thompson905]

Hello everyone,
I am having some issues getting past primary keys. A little background on what I am doing:

I am a Lotus Notes Programmer by design. We have decided to move away from Lotus and Access seems to be the choice to replace some of our applications. I have created a DB in access 2003. I have my tables created. I have my forms created. I was able to export from notes about 70,000 documents. In notes I had one main form and from that one could create any number of children documents(the parent doc generates the key and its passed to all the children documents...so some records in a ccess in the same table will have the same key. I set to DUP's OK for those tables). The documents are link by their unique document ID's. Well I needed to maintain that relationship when importing the documents to access. Which is working without a hitch. BUT where I am stuck is on new records. I need the same field that is linking all of the imported documents to autogenerate a primary key for new records..and I need that key to be passed to all the other tables in which I am storing all of the other children documents(records). Not sure how to get this done...

I hope I explained enough if not please me know what other information you need to try and help me out.

Thanks,
Tom

 

[neileg]

It is customary to handle a parent child relationship using a form/subform setup. The subform wizard will link these fields for you and any new records in the subform will inheirit the key field from the main form.

 

[thompson905]

It is customary to handle a parent child relationship using a form/subform setup. The subform wizard will link these fields for you and any new records in the subform will inheirit the key field from the main form.

Thanks for the reply. Yes I am using subforms to link the data. Like I said all that is working fine for existing records..I can flip through no problem..edit no problem...its the new documents where the autoassign needs to pick up(for the main form...and hopefully it will pass the info to the child documents too). Since I imported the key with the Lotus Notes documents.

 

[neileg]

Just to be clear (and I don't mean to patronise):
The child table needs to have a primary key which will be indexed, no dups. It will hold the relevant primary key value from the parent table as a foreign key which should be set to dups, OK.
Providing you have used the wizard, and the PK in the parent and the FK in the child have the same name, you will have synchronised these two tables. When you add a new record in the subform, the FK will be populated with the PK from the main form.

You mentioned 'all of the other tables'. Hows that working?

 

[thompson905]

Just to be clear (and I don't mean to patronise):
The child table needs to have a primary key which will be indexed, no dups. It will hold the relevant primary key value from the parent table as a foreign key which should be set to dups, OK.
Providing you have used the wizard, and the PK in the parent and the FK in the child have the same name, you will have synchronised these two tables. When you add a new record in the subform, the FK will be populated with the PK from the main form.

You mentioned 'all of the other tables'. Hows that working?

Well the only problem with the child tables not allowing dups is there can be more than one child record per main form.. Example:

Main client form:
-Child Assistance form for work Boots
-Child Assistance form for another pair of work boots

Both of those child form will be in the same child table with the same Key as the parent.

When I say all the other tables: assistance would be one, perscriptions would be another, family members would be another and so on and so forth. I used tabs on the main form with subforms on each tab for the abpove listed tables. Total there are about 11 child tables that all will contain the same key (if there was a need to create the child form) for the main form.

I am not sure how to go about tying all of the new records togather based on these rules.

I know your not patronizing me..I am kind of a lunk head when it comes to access. I am still learning the termanology and the quirks with do things a certain way.

Any help is most appreciated. Can you expain a little more about foreign keys and how to use them with primary keys??

Tom

 

[Rabbie]

A foreign key in the child record is a copy of the relevant PK in the parent record. If the PK in the Parent is Autonumber then the FK in the child will be Long Integer otherwise they should be the same type. The FK should normally be indexed with duplicates allowed to reflect the 1 to Many relationship.

 

[neileg]

Well the only problem with the child tables not allowing dups is there can be more than one child record per main form..

I'll re-emphasise what Rabbie said. The child table has two keys. The primary key belongs to the child table only and serves to uniquely identify the record, hence no dups. The foreign key is the link with the parent table and therefore can have dups.

 

[thompson905]

OK I think I understand about the child keys. Another question if you dont mind....I already have a primary key on the main form..can I add another primary key to it for new records that auto generates keys? And of course this would need to link to the primary key on the child records.

 

[Rabbie]

OK I think I understand about the child keys. Another question if you dont mind....I already have a primary key on the main form..can I add another primary key to it for new records that auto generates keys? And of course this would need to link to the primary key on the child records.

Let Me repeat what I said earlier

Any link from the parent to the child must be to a Foreign Key in the child.

Tables have Primary Keys not Forms.

You can't have different fields as a Primary Key in different records in the same table. How would any query know which one to use. If you add an autonumber Key to the parent you will need to relink to the relevant Child records. This could be done using an update query.

 

[thompson905]

ugh I am so confused....

 

[neileg]

If the existing records already have a numeric primary key then there's an easy way forward. You need a new blank table that is exactly the same structure as your existing table except you need to make the primary key an autonumber. Then you need to append the data from the old table to the new one. The autonumber field will accept the existing numbers. Delete the old table and rename the new one to the old name (if needed). When you add a new record to the table, Access will allocate an autonumber as the PK.

How keys work:
Suppose your parent table looks like this
tblParent
ParentID primary key autonumber
ParentName
ParentData
etc

The the child table will look like this
tblChild
ChildID primary key autonumber
ParentID foreign key integer link to tblParent
ChildName
ChildData
etc

 

[thompson905]

Neil,
Thanks alot for the help. Sorry I didnt reply sooner. I tried the append Query and I am getting a type mismatch. The PK on the existing table is alphanumeric and by default the auto number on the new table to append to is Long Integer....If I switch this to Replication ID the query runs...but as long integer I get a type mismatch...Any ideas how to fix that?

 

[neileg]

Two choices, really.
1) Apply a numeric PK to the existing data
2) Use VBA to derive an alpha numeric key for your new data

To be honest, I don't know hif a replication ID would be OK in a non-replicated database. Never been there. I would use option 1 above but you'll need to update both tables to keep them in sync.

 

[thompson905]

Two choices, really.
1) Apply a numeric PK to the existing data
2) Use VBA to derive an alpha numeric key for your new data

To be honest, I don't know hif a replication ID would be OK in a non-replicated database. Never been there. I would use option 1 above but you'll need to update both tables to keep them in sync.

So do you mean add a new column to the existing data for a numeric key? and use this for all new records? and what do you mean by keep both tables in sync?

Sorry I hate to keep pushing on this issue..but I feel like a ignorant ass in access.....
I do appreciate all your time and patience in helping me with this.

 

[neileg]

Yess, add a new autonumber in the parent table and a new numeric foreign key field in the child.

At present, the relationship between the tables is maintained by holding the alfanumeric value of the parent primary key as a foreign key in the child table. If you switch to using an autonumber in the parent table you will need to populate the child table with the relevant numeric key value. You can use an update query to do this by using the current alpha numeric key to link the records.