Acces Notes Tables via OBDC

[calandrelli]

I have downloaded the Notes ODBC drivers and can access notes databases on my company's server by pointing to the nsf file and browsing the tables. I don't know a lot about how Notes' databases are set up, but I only seem able to access tables that represent "views". Isn't there one table that contains all the fields for a single record? I am trying to import the information into Access and the views are not linked. Can anyone help explain why I can't find one table with all the data fields in it?

 

[cavell]

Domino dbs are set up differently than other relational databases as there is no master table that contains all fields. The fields are stored in documents, or notes, and are completely individual. So you could have field1, field2, and field3 in one document or note. Then have field1, field4, and field6 in another document. It will vary depending on what form you use to create the document. And when creating or viewing the views, you choose what fields to show in the columns, so you can get different documents that have different fields, created by the same form or different.. I believe you can find the Yellowbooks online at

http://www.lotus.com

which can give you a better insight on how Domino is set up.

Cavell

 

[Klopper]

I have been trying view Notes tables from Access using NotesSQL ODBC driver, but it was very problematic:
- Access usually crashed when opening the query that points to the view,
- Some views had more records than others and there is no easy way to link individual records, and even
- Sometime re-opening the same view would cause a massive variation in records! 1297, 324, 267, 4003

I sympathise calandrelli!

 

[BruceBell]

I certainly would also love to learn how to directly access a Notes table from my Access application. I have a catering database in Access that imports its main table from Notes. I first have to export from Notes to a Lotus 123 file, then import the 123 file to a table in Access. We do it every day, but it's slow.

 

[yert33]

BruceBell - Why don't you write a LotusScript agent to export those records to Access?

 

[BruceBell]

Yert33, I'd love to write an agent to automate the task, but I can't get a developer's access level for Lotus Notes, which I understand is necessary to write an agent. My company's IT department said a couple of years ago that they couldn't give me that access level because it cost too much. Maybe I'll ask IT again. It seems like it would be an easy agent to write. Is LotusScript part of Notes?

 

[yert33]

If you're dealing with R5 there is a separate client for designing. You will need this. LotusScript is a part of Notes and very similar to Basic.

Maybe you should ask your IT dept to write the agent since they seem to be loathe to give you access....

 

[calandrelli]

I was able to hit the note table directly by setting up Notes SQL drivers on my computer and then linking or importing the external data into Access using the ODBC (Notes) source. Each view forms a table and trying to figure out which table you need to hit is not EZ. You can get the ODBC drivers here .....

http://www.lotus.com/home.nsf/welcome/notessql