How can I connect to Lotus thru ODBC using VBA?

Question

I'm interested in setting up an Access db to run a report automatically. To save myself the trouble of going to each client computer and setting up the appropriate DSNs, I'd like to set up the ODBC connections in the VB script itself if possible.

I've googled and checked this site and found some good starter code, but not enough to make all the error messages go away. Can someone complete the code below?

Sub SetupODBC(Str_Server as string, Str_Db as string)  
 'Str_Server=Name of Server
 'Str_db=Name of Database
 Dim C as ADODB.Connection  
 Set C = new ADODB.Connection  
 C.ConnectionString = ??  
 C.Open  
 Debug.print C.State  
Exit Sub

Answer 1

This site is your friend: http://www.connectionstrings.com/access

I didn't follow your question correctly at first. I see you want to create a link from Access to Lotus to report on Lotus Notes data. Well there are a few ways to do so.

I frequently use a method of exposing Lotus Notes data as XML, then accessing that XML from the remote system. You can easily create a Notes Page with the XML start tag, root element, and then insert an embedded view in between the root element. That embedded view then needs to display as HTML and contain columns that resolve to xml tags. For instance, each row of the view would look similar to this:

<Person><FirstName>Ken</FirstName><LastName>Pespisa</LastName></Person>

and your column formulas would be:

"<Person><FirstName>" + FirstName + "</FirstName>"

for the first name column, and for the last name column it would be this:

"<LastName> + LastName + </LastName></Person>"

Note that this assumes that your Notes server has the HTTP service turned on and you can reach the database via a browser.

However as mentioned by other answers, you can use other methods such as NotesSQL and COM. It sounds like you are putting this solution on many workstations, though, and NotesSQL would require you to install the driver on each workstation. The COM method would work without requiring any extra work at the users' desks so I'd favor that solution in this case.

Answer 2

Looks like a great site for my needs, even if it hasn't been updated in a year. But still no cigar. Now, I'm getting "Data source name not found and default driver not specified"

(Obviously, ServerNameGoesHere and DatabaseNameGoesHere are subsitutions)

Sub dbX()  
     Dim C As adodb.Connection  
     Set C = New adodb.Connection  
     C.Open _  
        "Driver={Lotus NotesSQL 3.01 (32-bit) ODBC DRIVER (*.nsf)};" & _  
        " Server=ServerNameGoesHere;" & _  
        " Database=DatabaseNameGoesHere.nsf;"  
     C.Close
End Sub

Answer 3

Hi SA, Welcome to the board. ConnectionStrings is indeed your friend, but the issue you are having is that you don't have the driver:) Lotus Notes is not a relational database it is a document oriented database. Historically there has not been a way to Access it like it is a relational database for that reason. However IBM eventually got around to writing a sort of translator in the form of NotesSQL. If you follow the link and get the driver you should be able to use ODBC. It is worth noting that Notes exposes itself to COM. So if push comes to shove you can automate the client.