Ruby and Accdb (Ms Access)

ruby and accdb (ms access)

Something along these lines should get you started. Of course, you'll need to modify some of the values like; path, filename, SQLstatement, etc.

MDB file (Access 2003 format and older) using the Jet engine

require 'win32ole'
connection = WIN32OLE.new('ADODB.Connection')
connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;
                 Data Source=c:\path\filename.mdb')

ACCDB file (Access 2007 format and newer) using the ACE engine

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

To execute a SQL query that doesn't return data use:

connection.Execute("INSERT INTO Table VALUES ('Data1', 'Data2');")

To perform a query that returns a recordset:

recordset = WIN32OLE.new('ADODB.Recordset')
recordset.Open(SQLstatement, connection)

Read/Write Microsoft Access (.accdb) synced to Sharepoint with Ruby

I may be misunderstanding your situation, so I apologize in advance if that is the case.

When you sync an Access DB with SharePoint, Access creates a SharePoint list for each table. It then links the list to the local Access database. As you edit data through your forms, you are actually editing the data in the SharePoint list. Access serves as a proxy, and each list in SharePoint is surfaced as a linked table in Access, along with a connection string which caches the credentials. You could continue to work with the Access table (which is actually now a SharePoint list), or bypass Access altogether and work with the list in SharePoint directly. SharePoint has an API and other techniques that allow you to work with the list programmatically. If that is your scenario, you no longer need to use Access. Ruby can just work with SharePoint.

Let me know if this is off track, and perhaps I can still help.

Ruby win32ole MS Access: How to find all the records updated since last export?

The created_at and updated_at columns in a Rails (actually ActiveRecord) model are automatically added by default in a migration script generated from a script/generate model. Further, they're maintained automatically by ActiveRecord when it detexts their presence. Even then, should you bypass AR and go straight to the database, the columns probably won't be maintained for you.

Access will not automatically do any of that for you, I'm afraid.While I'm sure it msut be possible to some extent to duplicate the functionality, it's probably not trivial. Typically, you're either going to need to handle the changes with triggers (which aren't implemented in Access) or in the code that updates the table (which is what ActiveRecord does).

---

Related Topics