Error : "in `require': cannot load such file -- win32ole (LoadError)" while trying to connect from ruby program to ms access database

Question

From my Ruby program running under Ubuntu I am trying to connect to an Access database residing on another computer running Windows. I am getting the error

'require': cannot load such file -- win32ole (LoadError)

Another problem I am facing is that the Access file that is not present on my system, so here I want to use the ip address and port number and user name and password of the system the file resides in, so that by using an Internet connection I can connect and get the data. Unfortunately I do not know where I can specify these details in my piece of code below.

connect_to_access_db.rb

require 'win32ole'

connection = WIN32OLE.new('ADODB.Connection')
connection.Open('Provider=Microsoft.ACE.OLEDB.12.0;
             Data Source=c:\path\filename.accdb')


SQLstatement = "SELECT * FROM TABLE"
recordset = WIN32OLE.new('ADODB.Recordset')
res = recordset.Open(SQLstatement, connection)
p res

How can I make the connection to the Access database?

Answer 1

Your problems are threefold:

1. Trying to use win32ole/OLEDB on a non-Windows machine

Chances are extremely good that you simply cannot use win32ole in a Ruby app running on a non-Windows machine. And even if you could, your code would require an OLEDB provider for Access on your Linux box, and I doubt that such a thing exists.

2. Connecting to the database file

here i want to use the ip address and port number

You can't do that. In order to connect to an Access database on a remote machine you need to connect to a Windows shared folder, not a TCP port. So, on the Windows server you would have a shared folder and on the Linux client you would mount that folder.

3. Database access

If win32ole is not available then you need to use some other database access technology. ODBC is a common choice, but unfortunately ODBC support for Access databases on Linux (via "mdb tools" and "unixODBC") is quite unreliable, at least in my experience.

Solution

In your case, I would be inclined to try using JRuby and the UCanAccess JDBC driver. I just successfully tested it on my Ubuntu 14.04 LTS box with the JRuby code ...

connUrl = "jdbc:ucanaccess:///mnt/weezerpublic/uca301demo.accdb"
conn = java.sql.DriverManager.get_connection(connUrl)
stmt = conn.create_statement
rs = stmt.execute_query("SELECT TextField FROM myTableInAccess WHERE ID=1")
while (rs.next) do
    puts rs.getString("TextField")
end
conn.close

... invoked via the shell script ...

#!/bin/bash

export CLASSPATH=.:/home/gord/Downloads/JDBC/UCanAccess/loader/ucanload.jar

jruby jrubyTest.rb

... with the following caveats:

1. Windows file sharing won't work over a plain Internet connection. You would require a VPN connection to access such a shared folder over the Internet.
2. The UCanAccess JDBC driver has significant limitations regarding concurrent write access to a database from multiple processes. If your Ruby app must support multiple concurrent users who need to update – not just read – the database then you'd be better off using some other database (e.g., Microsoft SQL Server, MySQL, ...) as the back-end.