Microsoft Access pull Notes data via ODBC

HCL Domino Domino Forum
1

I have a Notes database and someone would like to get that data to an Access database.

I’m not much of an Access user but I did get the ODBC Connection set up enough to know I can import the data. But there must be a way to automate this in Access via a macro or module or something.

I’ve been looking for a forum similar to this one that I can search for Access info but not coming across anything real helpful. Does anyone know where one would be (or have example of the import automation?)?

TIA,

Tammy

2

Subject: Microsoft Access pull Notes data via ODBC

You can pull/push data to Access using LEI or DECS. There are also several thirt party products which can perform same operation.

If you want to do it only one time and not on scheduled basis, then you can probably use Excel for export from Notes and then import (by manually pasting data?) Excel file to Access.

I think there are examples in Sandbox about how to write to Access databases using LotusScript.

3

Subject: RE: Microsoft Access pull Notes data via ODBC

Yes, I could explore doing this from Notes, but I believe it’s already an option in Access to pull from other data sources. So I wanted to put it back in their court.

But it seems that there would be a way to automate the manual process of clicking File - Get External Data Source - selecting the sever & file, etc., so they wouldn’t have to do that every time.

4

Subject: RE: Microsoft Access pull Notes data via ODBC

NotesSQL is an ODBC Driver for Notes from Lotus, and best of all, it’s free. Make sure to read the documentation.

Also, if you have questions search on this forum for NotesSQL AND access.

http://www.lotus.com/notessql

(you may not be able to get to download till Monday as there’s been some issues with the d/l site).

Ron

5

Subject: RE: Microsoft Access pull Notes data via ODBC

You can create a linked table which connects to Domino through the NotesSQL driver, and you can also programmatically connect through ADO.

Here’s a good resource for connecting Notes and M$: e-promag.com

Also, here’s a near-identical thread in this forum a few hours ago:

6

Subject: Microsoft Access pull Notes data via ODBC

If you have SQL Server or Oracle or some equivalent thereof consider using the Lotus Connectors and the LCLSX to push the Notes data to a relational table, and then link to that table from Access. This would take NotesSQL and ODBC in general out of the picture.

If you have Notrix or LEI it’s easier still as you don’t have to script the transfer from Notes to SQL Server|Oracle.