2

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?

Andrew Grimm
  • 78,473
  • 57
  • 200
  • 338
Nik So
  • 16,683
  • 21
  • 74
  • 108

1 Answers1

0

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

Mike Woodhouse
  • 51,832
  • 12
  • 88
  • 127
  • 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 So Feb 08 '10 at 23:44
  • 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 So Feb 08 '10 at 23:55
  • 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 Feb 09 '10 at 01:49
  • 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 Feb 09 '10 at 01:52
  • 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 Feb 09 '10 at 01:54
  • 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 So Feb 09 '10 at 04:47
  • @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 Feb 09 '10 at 08:26
  • @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 Feb 09 '10 at 22:05