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

Hello, I am using win32ole module/library to gain access to an Access database. But I can't find as I can in Rails the created_at or updated_at columns in any of the tables in the database. I was wondering how does one finds rows that are updated, then?

So I have require 'win32ole' connection = WIN32OLE.new('ADODB.Conneciton') conneciton.Open('Provider = Microsoft.ACE.OlEDB.12.0; Data Source = c:\data.accdb')

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

fields = [] recordset.Fields.each do |field| fields << field.name end

data = recordset.GetRows.transpose

so data = [ ['john', 'doe', 'author'], ['mick', 'jagger', 'singer'], ['woody', 'allen', 'direct'], ['pablo', 'picasso', 'painter'], ['homer', 'simpson', 'loser'] ]

fields= ['first', 'last', 'occupation']

But if someone changed Homer's job to 'Winner', what kind of SQL query do I use to find out about this. Presumably, there's a last-checked timestamp to make sense of this. Let's just say it's provided, how does one go about it then?

Thank You Very Much

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).

Yeah, I remember reading about the created_at/updated_at as a feature of rails 2(?) in early 2007. But I wasn't sure if there's much lower-level built-in's in Access that handled that transparently. Looks like there's isn't such a thing in Access. And I didn't design that Access database so there's no created_at/updated_at in ANY tables. Am I completely stuck then? --

Nik 2010-02-08 23:44:45

I was just looking around for software solution and this DBSync app seems to be able to 'synchronize' between Access and MySQL. Now that kind of tells me that unless the app itself keeps a record of each row in the database when the last update is, how can it 'know' which ones to update in the synchronization? i.e., there must a way to find that information don't you think?

Nik 2010-02-08 23:55:00

There are a number of strategies for synchronizing two databases that don't have timestamps on the records. I've written about it several times -- after you've synchronized the INSERTS and DELETES (the easy part), the strategy for synchronizing updates is to dynamically write a SQL UPDATE statement for each updatable column of each table. The WHERE clause has to handle Nulls and has to be coded with a SELECT CASE that formats it with appropriate delimiters (in Access, e.g., "WHERE Nz(Table1.NumericField, 0) <> Nz(Table2.NumericField, 0)").

David-W-Fenton 2010-02-09 01:49:52

All that said, most of my Access apps have Created, Updated and UpdatedBy fields in the tables at or near the top of the relational hierarchy. The Created and Updated fields are populated with a default value of Date(), and Updated and UpdatedBy are altered in my Access application's forms' BeforeUpdate events. In your case, your app (whatever is updating the Jet/ACE database) would have to take care of this.

David-W-Fenton 2010-02-09 01:52:39

Last of all, looking forward, in the upcoming A2010 version of the ACE, new table-level data macros are introduced and these execute at the database engine level, and can be used to do many of the same things that triggers do. So, you could implement these fields and have them automatically updated if you were using the A2010 version of the ACCDB format.

David-W-Fenton 2010-02-09 01:54:00

David, thank you very much for your insight - I only plan to get data from the Access db, never to modify it. I am running a simple Ruby script to fetch newly created and updated data from Access to a MySQL db. And unfortunately, I am not allowed to make changes to the Access db, I can't possibly, then, write that specialized update query that you said above.

Nik 2010-02-09 04:47:07

@Nik - I think you are probably stuck as far as what you'd _like_ to do, but the end-result can probably be achieved if you can affford the time and effort involved. I think your "best" approach may be to pull everything across and do some godawful reconciliation at the MySQL end. Not fun.

Mike Woodhouse 2010-02-09 08:26:12

@Nik: you only need write access to the Jet/ACE database if your task is to synchronize it with the MySQL database. Since you lack write access, you're obviously synching only one-way, and my approach would still do the trick even without the date/time stamps. You don't need to use Access to write the update statements to do it. You just need a data interface to Jet/ACE that you can control from whatever programming language you happen to be using.

David-W-Fenton 2010-02-09 22:05:47

ansaurus

tags:

views:

answers:

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

related questions