tag:blogger.com,1999:blog-284600789737488130.post5435977521020768370..comments2024-02-07T02:22:20.642-05:00Comments on Ruby on Windows: Using Ruby & ADO to Work with MS Access DatabasesDavid Mullethttp://www.blogger.com/profile/05597492688893461137noreply@blogger.comBlogger32125tag:blogger.com,1999:blog-284600789737488130.post-41956530873364803142016-12-14T08:37:30.699-05:002016-12-14T08:37:30.699-05:00David
To be clear about matters, here is the abst...David<br /><br />To be clear about matters, here is the abstracted test code, where I switch the comment between trying to open either the mdb or accdb file.<br /><br />(Note: I did try previously your suggestion of trying to open the .mdb file with the ACE engine). <br /><br />#===================================================================<br />require 'win32ole'<br /><br /> file1 = 'C:\Temp_Laptop\Test.mdb'<br /> file2 = 'C:\Temp_Laptop\Test.accdb'<br /> <br /> p 'not found file1' if !File.exist?(file1)<br /> p 'not found file2' if !File.exist?(file2) <br /> <br /> connection_string = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + file1 <br /># connection_string = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + file2 <br /> <br /> @connection = WIN32OLE.new('ADODB.Connection') <br /> @connection.Open(connection_string) <br /> <br /> p ' opened'<br />#===================================================================<br /><br />When run using Ruby 2.3 I get this for either run:<br /><br /><br />Error: (in OLE method `Open': )<br /> OLE error code:800A0E7A in ADODB.Connection<br /> Provider cannot be found. It may not be properly installed.<br /> HRESULT error code:0x80020009<br /> Exception occurred.<br /><br />If I run using Ruby 1.8 I get the expected message 'opened' for either option.Martin Bachnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-44508500780788076632016-12-14T08:19:08.416-05:002016-12-14T08:19:08.416-05:00David
Thanks for the suggestion, but precisely th...David<br /><br />Thanks for the suggestion, but precisely the same error as before.<br /><br />MartinMartin Bachnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-19976845088552812702016-12-13T21:19:21.079-05:002016-12-13T21:19:21.079-05:00@Martin:
Try replacing...
Provider=Microsoft.J...@Martin:<br /><br />Try replacing...<br /><br /> Provider=Microsoft.Jet.OLEDB.4.0<br /><br />...with...<br /><br /> Provider=Microsoft.ACE.OLEDB.12.0<br /><br />...and let me know if that resolves the issue.<br /><br />-David<br />David Mullethttps://www.blogger.com/profile/05597492688893461137noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-36082357882344214132016-12-12T08:51:55.007-05:002016-12-12T08:51:55.007-05:00Help!
I have some code (cribbed from the fine ori...Help!<br /><br />I have some code (cribbed from the fine original example from this blog) to open an MsAccess database; thus:<br /><br /> connection_string = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' <br /> connection_string << @mdb <br /> @connection = WIN32OLE.new('ADODB.Connection') <br /> @connection.Open(connection_string) <br /><br />This has worked perfectly using Ruby 1.8.<br /><br />I am now staring to use Ruby 2.3.0 (well - these things take time).<br /><br />Unfortunately, when I run this code, I get the following error message:<br /><br />Error (in OLE method 'Open':)<br />OLE error code 800A0E7a in ADODB.Connection<br />Provider cannot be found.It may not be properly installed <br /><br />I cannot find any useful clues anywhere as to what has changed/needs to be amended. Can anyone help?<br /><br />ThanksMartin Bachnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-21501872997489631452014-04-19T05:11:41.284-05:002014-04-19T05:11:41.284-05:00to prevent sql injections I ve tried the following...to prevent sql injections I ve tried the following code<br /><br />sql = "INSERT INTO tblTest ([myName], [integerNumber], [isRed]) VALUES(?, ?, ?);"<br /><br />ocmd = WIN32OLE.new('ADODB.Command')<br /> ocmd.CommandType = 1<br /> ocmd.ActiveConnection = @connection<br /> ocmd.CommandText = sql<br /><br />values.each_with_index do |value, index|<br /> ocmd.Parameters.Item(index).Value = value<br />end<br /><br />however if a value is nil execution fails... Does anybody know how I may pass a null value successfully using parameters ??<br />Volkerhttps://www.blogger.com/profile/02411458686065628445noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-56875240811579356172014-02-07T03:53:29.294-05:002014-02-07T03:53:29.294-05:00thank you for your valuable post.
I have a mac and...thank you for your valuable post.<br />I have a mac and an access db located on windows server PC which data is changing daily. <br />Both may mac and windows pc are in the same network and also I have permission to connect to server remotely by telnet.<br />How may I read data from my mac using ruby?Anonymoushttps://www.blogger.com/profile/12821783194265482838noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-3720636589878836132013-10-22T21:21:14.487-05:002013-10-22T21:21:14.487-05:00@Anonymous:
What error are you getting?
The GetR...@Anonymous:<br /><br />What error are you getting?<br /><br />The GetRows() and transpose() methods work for me, using:<br /><br />Windows 8.1<br />Ruby 2.0.0p247<br /><br />I'm running Office 2007 (not 2003 as when I wrote the article), so I did need to change the Provider in the connection string to:<br /><br /> Provider=Microsoft.ACE.OLEDB.12.0<br /><br />Otherwise, the class works for me exactly as defined---under Ruby 2.0 MRI. I have not had success with it on JRuby.<br /><br />DavidDavid Mullethttps://www.blogger.com/profile/05597492688893461137noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-36784881405208884842013-10-21T17:30:03.053-05:002013-10-21T17:30:03.053-05:00Hey great work guys,but i have a question that, ge...Hey great work guys,but i have a question that, getrows doesn't work with ruby 2.0 version ,need to use it for select query,getrows.transpose too not working,please helpAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-55224357319894985312011-08-19T08:23:34.689-05:002011-08-19T08:23:34.689-05:00I managed to help myself! My SQL statement should...I managed to help myself! My SQL statement should have been:<br /><br />db.query("SELECT * FROM table WHERE start_dttm = #2011-08-16 07:07:53#")<br /><br />Microsoft... Reinventing counter-intuitive.<br /><br />-RaumRaum Dellamortenoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-50020358445862385562011-08-17T18:41:56.585-05:002011-08-17T18:41:56.585-05:00I'm having trouble finding a record by a DateT...I'm having trouble finding a record by a DateTime value. Ruby stores DateTime as an integer and Access stores DateTime as a float.<br /><br />When I find a record that has a DateTime in it, Ruby displays it like this:<br />2011-08-16 07:07:53 -0500<br /><br />that same DateTime converted to an integer in ruby:<br />1313496473<br /><br />And through trial and error I discovered that Access stores that same DateTime like this:<br />40771.2971412037<br /><br />So the query "SELECT * FROM table WHERE start_dttm = 40771.2971412037" works but I am in serious need of being able to convert back and forth between that format and something human readable.<br /><br />Even knowing what to call that format might help. Or maybe I'm just doing it altogether wrong!<br /><br />Any help is appreciated :)<br /><br />- RaumRaum Dellamortenoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-56486804816046922092011-01-18T13:21:52.346-05:002011-01-18T13:21:52.346-05:00From a previous post (2007), the user was able to ...From a previous post (2007), the user was able to create a new Catalog.<br /><br /># SpaceRep.rb<br />require 'win32ole'<br />connection = WIN32OLE.new('ADODB.Connection')<br />'Data Source = is your Access file<br />connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=users.mdb')<br />cat = WIN32OLE.new("ADOX.Catalog")<br />cat.ActiveConnection = connection<br /><br />I have tried the same code in ruby 1.8.6 but it fails. Its an Unrecognized Database format.<br /><br />In addition I create a new file:<br />File.open(mdb_file, File::RDWR|File::CREAT)<br /><br />and then attempt to connect to it and write a Catalog:<br /><br />connection_string = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source='<br /> connection_string << @mdb<br /><br />#create connection<br /> @connection = WIN32OLE.new('ADODB.Connection')<br /> @connection.Open(connection_string)<br /><br />#Create new database<br /> @cat = WIN32OLE.new('ADOX.Catalog')<br />@cat.ActiveConnection = @connection<br /><br />and it fails on the last line.dejanhttps://www.blogger.com/profile/12201419104217272582noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-16351367481537427102010-12-01T15:38:28.118-05:002010-12-01T15:38:28.118-05:00Thanks for the info on how to start.
I have one s...Thanks for the info on how to start.<br /><br />I have one suggestion.<br />I needed to know the data type for the fields as wel.<br />What I did was inserting the following code just after the field.Name statement(after the end):<br />@types = []<br /> recordset.Fields.each do |field|<br />@types << field.Type<br />end<br /><br />and I added the types to the top of the class with the other attributes.Jurgenhttps://www.blogger.com/profile/09388423369761159442noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-19608056087145911472009-11-02T07:34:20.311-05:002009-11-02T07:34:20.311-05:00Hi,
Thanks !
Question:
How to see the updated ...Hi,<br /><br />Thanks !<br /><br />Question:<br /> How to see the updated records,<br />If you update something.<br /><br />say:<br /> db.execute("update table1 set name='myname' where id=1 ;")<br /><br />query after this still returns the old values:<br /><br />db.query("SELECT * FROM table1 ;")<br />rows.each{|x| p x}<br />============================<br />maybe some refresh method to the Accessdb Class.?<br /><br /><br />Thanks a lot.<br />RommelAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-63407712225245126452009-10-07T13:40:24.185-05:002009-10-07T13:40:24.185-05:00Easy enough. Should have looked at the ADODB API ...Easy enough. Should have looked at the ADODB API first.<br /><br />value = recordset.Fields("FieldName").ValueJoeynoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-31782699813030462032009-09-03T15:05:26.548-05:002009-09-03T15:05:26.548-05:00Thanks for the post!
However I'm a bit confus...Thanks for the post!<br /><br />However I'm a bit confused about this... I'm working on porting my code over from vbscript to ruby. In VB, I'm used to referring to a certain field within the recordset using<br /><br />value = recordset("fieldname")<br /><br />It seems that, using your class example, after running a query to return a recordset, I would have to refer to a certain field within a recordset by an index number (ex. recordset[0]) instead of a field name, as I do in vb. Is there an example that shows how to refer to a single value within a recordset by it's field name, instead of an index number?Joeynoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-31854194165378333002009-08-26T07:45:52.779-05:002009-08-26T07:45:52.779-05:00thank you very much, this helped me very much. Oth...thank you very much, this helped me very much. Otherwise i would have to do quite a bit of work in vb ...Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-62025023167644830982009-08-01T08:11:26.701-05:002009-08-01T08:11:26.701-05:00Can we have the something similar for conenction t...Can we have the something similar for conenction to Oracle dB.<br /><br />I want something other than DBI to connect to my Oracle 10g dB.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-14872900428186870492008-12-05T10:54:00.000-05:002008-12-05T10:54:00.000-05:00It just works. Thank you!It just works. Thank you!Unknownhttps://www.blogger.com/profile/02944512101855023022noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-65363805773375874972008-10-06T03:09:00.000-05:002008-10-06T03:09:00.000-05:00Wow thanks for this post! It is really helpful.. b...Wow thanks for this post! It is really helpful.. best regards, nikolaiAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-34940975887721751872008-08-01T04:38:00.000-05:002008-08-01T04:38:00.000-05:00Very nice posting. I got a software stellar phoeni...Very nice posting. I got a software stellar phoenix <A HREF="http://www.repair-access-file.com" REL="nofollow">access repair</A> which repair and recover corrupted mdb database and MS access files. It is a powerful and robust software and easy to use.<BR/><BR/>ThanksJohnhttps://www.blogger.com/profile/16412987574517716505noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-302307375072788152008-03-01T14:36:00.000-05:002008-03-01T14:36:00.000-05:00A Microsoft Access adapter for Rails has been made...A Microsoft Access adapter for Rails has been made (<A HREF="http://blog.behindlogic.com/2007/07/msaccess-for-rails-heres-your-rough.html" REL="nofollow">Rails MS Access Adapter</A>).<BR/>It probably won't work with the latest ActiveRecord, as it's not an adapter gem; though with older versions it works great.danielhttps://www.blogger.com/profile/01012067250242364513noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-46841142338915626692007-11-11T22:45:00.000-05:002007-11-11T22:45:00.000-05:00You're welcome, Patrick!"This post just saved me s...You're welcome, Patrick!<BR/><BR/>"This post just saved me several hours."<BR/><BR/>Another Ruby on Windows success story!<BR/><BR/>[grin]<BR/><BR/>DavidDavid Mullethttps://www.blogger.com/profile/05597492688893461137noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-29897577615189437672007-11-08T10:13:00.000-05:002007-11-08T10:13:00.000-05:00Thanks. This post just saved me several hours. I'm...Thanks. This post just saved me several hours. I'm a big fan of code which integrates between open source and 'closed' systems, and this particular wedge allows me to crack a closed system's data wide open.Patrick Wrighthttps://www.blogger.com/profile/00179085769897385696noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-87287356287053096202007-06-29T06:31:00.000-05:002007-06-29T06:31:00.000-05:00Sorry, please correct the above line to be a ruby ...Sorry, please correct the above line to be a ruby comment starting with #, as following:<BR/># Data Source = is your Access fileAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-86498535216233914902007-06-29T06:09:00.000-05:002007-06-29T06:09:00.000-05:00Thanks for the hint in using the powerfull 'win32o...Thanks for the hint in using the powerfull 'win32ole'.<BR/>For my needs I tried to use AdoX with Ruby to replace spaces in Access field names, and it worked fine. Here is the code:<BR/><BR/># SpaceRep.rb<BR/>require 'win32ole'<BR/>connection = WIN32OLE.new('ADODB.Connection')<BR/>'Data Source = is your Access file<BR/>connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=users.mdb')<BR/>cat = WIN32OLE.new("ADOX.Catalog")<BR/>cat.ActiveConnection = connection<BR/>print "Inform your table name: "<BR/>yourtable = gets.chomp<BR/>print yourtable, "\n"<BR/># loop through each field in the table replacing the Spaces by what you want - in my case "_"<BR/>for col in 0..cat.Tables(yourtable).Columns.Count - 1<BR/> cat.Tables(yourtable).Columns(col).Name = cat.Tables(yourtable).Columns(col).Name.gsub(" ", "_")<BR/>end<BR/>print "Task completed! Check your database."<BR/>getsAnonymousnoreply@blogger.com