5

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?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
John
  • 1,273
  • 3
  • 27
  • 61

1 Answers1

4

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.
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thanks Thompson. where i can menction the access database details in above code like ip address, user name and password. – John Jul 18 '16 at 14:33
  • oh sorry i did not read answer compleatly. you already said it cannot be done. – John Jul 18 '16 at 14:37
  • the database of my rails app is mysql. from access database, i want to pull the data from "check-in", "check-out" columns these are of datetime columns. similarly in my mysql database also same "check-in", "check-out" columns of datetime are present. i just want to pull the information from access database and want to save in my mysql table columns. is this possible? i heard that the internal format of saving the data in access db is different from mysql format as it is not a relational db. is that true ? if that is the case what type of conversion i have to do before saving into db? – John Jul 18 '16 at 15:05
  • I agree with Gord, don't waste time on that, the closest thing to an Access mdb is a SQLite database, easy to setup, no services or daemons running all the time and you can use ORM like activerecord, usable on both windows and linux, you could do a migration from access on the windows side, I do this on regular base but from Excel, no conversion problems, the standard Ruby types are used and in case of problem a simple .to_s of to_i wil do – peter Jul 18 '16 at 15:08
  • @John - *"i just want to pull the information from access database and ... save in my mysql table columns. is this possible?"* It certainly should be. You would have one connection to the Access database and another connection to the MySQL database. - *"i heard that the internal format of saving the data in access db is different from mysql format"* True, but irrelevant. You would just do a SELECT from Access into [J]Ruby variables that will contain date/time values, and then do an INSERT or UPDATE into MySQL using those variables. The database drivers should take of the conversions for you. – Gord Thompson Jul 18 '16 at 15:18
  • @thompson - "The UCanAccess JDBC driver has significant limitations regarding ..... (e.g., Microsoft SQL Server, MySQL, ...) as the back-end." currently my destination database is mysql. are u suggesting to migrate from access database(source database) to mysql database? in my case im not performing any alter command in access db. i want to perform huge read queries (select statements) on access database by using an employee array loop. and after getting the data in ruby variables those many update queries(select queries made to access). to update my mysql db (destination database.) – John Jul 18 '16 at 15:56
  • @John - *"are u suggesting to migrate from access database(source database) to mysql database?"* - Not if you only need to read from the Access database. – Gord Thompson Jul 18 '16 at 16:30
  • @thomson, the Ip Addresses of the computer will keeps on changing. in this case how can we make sure that connecting to a system where the access database is present without fail? – John Jul 22 '16 at 08:48
  • @John - You would use [DNS](https://en.wikipedia.org/wiki/Domain_Name_System) (specifically [Dynamic DNS](https://en.wikipedia.org/wiki/Dynamic_DNS)). – Gord Thompson Jul 22 '16 at 11:45