• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

Converting Access Database To Lotus Notes

Hi all,

I am about to start conversion of a MS Access database into Lotus Notes.
Has anyone had any prior experience in doing this, and what are the recommendations.

I know that Access is a relational database, Notes is hierarchical etc.
What I am after is how people have deisgned solutions to overcome the differences in the way these two different types of database work.

i.e. should I be using subforms, embedded views, lookups etc.

There are 16 tables in the access database. Most relationships are 1 to many. I know I can not relate documents in Notes, so how is this problem overcome? I am thinking I could use a lookup on one form to lookup the key values from a view?
However, in Access if this key value changes then all records are updated. Is there any way to recreate this in Notes?

Thanks for all your help as always!
0
shuboarder
Asked:
shuboarder
  • 7
  • 7
  • 6
4 Solutions
 
Sjef BosmanGroupware ConsultantCommented:
That's a serious question... and there's no 1-2-3 answer. It depends on quite a lot, how things are converted the best way possible:
- is it a complex structure?
- is the database used frequently?
- how many users?
- what's its purpose?
- is it transaction-oriented?
- are there many queries you should re-create?

There are means to maintain key-values the same, but you'd have to develop (or borrow) the necessary tools for it.

Depending on the complexity of the Access database, it's a piece of cake or a block of concrete...
0
 
shuboarderAuthor Commented:
I'm not doing it in Lotus 1-2-3 :) - Just kidding!

I understand this isn't going to be easy!

- Structure is fairly complex
  - 16 tables / 15 relationships - maximum of 2 relationships deep from main table
- Database used daily
- 5+ users approx.
- Quote prices for customers, so it's manually input historical data
- Not transaction oriented
- 15+ queries
0
 
Sjef BosmanGroupware ConsultantCommented:
Ah, you understood :-D

The complexity seems not too bad, I think. Important steps:
- try to convert the current table structure into a more or less hierarchical structure.
- try to figure out if a document-response hierarchy is possible, practical and/or required
- try to determine the lookup tables and the real work horses, so you know where to put the focus
- one-dimensional lookup tables (e.g. countries) can be a multi-value field in a profile document, or in a hidden @DbColumn-view over existing data

Once you've got the target database structure, you can name the forms, the fields, the views and the agents you need. Key-value modifications I wouldn't allow for the time being (if ever it happens, they should have thought twice before entering the data ;).
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
marilyngCommented:
Basically I created the forms and responses, and comperable views.  Then declared all in Notes SQL odbc, linked to Notes in Access and a few queries later I was done.
0
 
Sjef BosmanGroupware ConsultantCommented:
Hm... Aren't you talking the other way round: how to use a Notes database from Access?? It's still early here, so I may be terribly wrong here...
0
 
shuboarderAuthor Commented:
I want to make an Access database disappear off the face of the planet
and put a lovely Notes database in its place :)
0
 
marilyngCommented:
You can push as well as pull from access, just link the forms or tables and write queries.  Really, I've done this both ways.  In a pinch it's quick and painless.  It's also the easiest way to convert excel to Notes.

Every table should be a form.  And the only thing you'll have to resolve is the autonumber for fields.  IN this case, you want to create a unique identifier that you can use, like a concatenation of fields that make the record unique.

To make sure you have things set up, be sure  you have data in the forms.. at least one record, so when you link in Access, you can see the relationships.
0
 
Sjef BosmanGroupware ConsultantCommented:
I suppose "off the face" means "offer" than that...  Hihi
0
 
shuboarderAuthor Commented:
Marilyng - this sounds good.

>>Every table should be a form

This is how I was tackling it I'm glad you backed this up.
So far I have been creating the forms with all the fields and populating them via Lotus Notes agents.
My main concern is that a relational database is going to be made non-relational.
How can I still try to enforce the relationships? more agents?

There appears to be no autonumbering in the access database so I don't know if I need that?

How can I link this in Access? I'm not quite with you on this....
You make it sound very easy. Am I missing something?
0
 
Sjef BosmanGroupware ConsultantCommented:
Enforcing relationships is a painful process, even when you use the appropriate tools. I once created several LotusScript-classes to do what you want, but only to maintain the fields in a hierarchy of documents (responses). If a certain value was changed in the main document, and it was a "special" field, its value would cascade down to all descending documents. I never got so far as to include views and things like that. Whenever possible, try to avoid that key-values are changed. For the rest, agents can do a lot for you.

In some other application, we created one agent that inspects (overnight) the integrity of the database, reporting or correcting what was wrong. Interesting work...
0
 
marilyngCommented:
You enforce the relationships using parent-child, but first block this out...

If you have a header table (ORder Header) and many details (order lines)  each order detail is a new document based on the order number.  

In the order HEADer, there is one UNID for the customer#, and the DBlookup that you provide for this custnumber is related to the customer form.  So, the ORDER doesn't copy the customer information, just the one linking field.  You would need to provide or add the dblookup to fill in the customer name, address, etc.   But you can do this after you import everything.

I would just draw out the relationships in Visio, and then figure out in Notes how to maintain the fields.  For instance, the customerID field is a saved field.  But every other customer related field can be computed for display.  What I do with this is grab the unid of the customer document and the customer number.  Everytime the form opens the address field is, @GetDocField(docID, "ThisField")

Or you can concatenate all the lookup values into a single string in a view, and grab the entire string, then each computed for display does: @Word(ConcatenatedField;"|";2) etc.

It takes more work to enforce refential integrity in Notes than in Access.
0
 
shuboarderAuthor Commented:
Thanks again Marilyng - some more good ideas there.

Just found this link as well which looks quite useful....

http://www-128.ibm.com/developerworks/lotus/library/hierarchy-objects/index.html
0
 
shuboarderAuthor Commented:
Using the MS Access relationships I can create the following hierarchy:


----------------------------------------Invoice Customers----------------------------------------
|                                       |                             |                          |                         |
Invoice Addresses     Account Manager     Bill to customers     Customer Line     Delivery Customers
                                         |                                                         |                        |
                              Account Managers                          ----------------------------   Addresses Delivery
                                                                                  |       |       |       |       |
                                                                               Part   Cost   Del.  Price  Payment

It looks somewhat more simple in this format.
0
 
marilyngCommented:
Good link.. the synchronize children is a good tip to follow.
0
 
marilyngCommented:
The only thing that becomes difficult is if you want to save the "linked" stuff so that it shows in a view..

ie.  in Access, when you link to a customer record, Access holds the cust ID in the table, but shows the cust name.  When you do a report, you have to bring in the table to show the cust name, address, etc.

In Notes, you can display the information when the form is opened, or if you need to have it show in views, collect it and save it with the form.  But if the main document changes, then you have to create the code that updates all the records, or decide if you want to update all the records.  i.e. if the customer phone changes, do you go through all invoices and change in it every invoice?

0
 
shuboarderAuthor Commented:
Exactly!

- I want to only have to update this kind of stuff once in the new Notes database.
I had a feeling this was going to be difficult!
0
 
Sjef BosmanGroupware ConsultantCommented:
Try to store information in only one document, to be referenced whenever necessary using @DbLookup from a CfD-field.
0
 
marilyngCommented:
again, it depends on what you're saving on a form.. if it's just an ID that collects and displays the rest of the record, then you're fine.
0
 
shuboarderAuthor Commented:
Looks like this one's been exhausted - Thanks for all your help!
0
 
marilyngCommented:
Glad we were able to help!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now