# DBI and MSAccess

**URL:** <https://rubytalk.org/t/dbi-and-msaccess/5132>\
**Category:** ruby-talk\
**Created:** [12 March 2003 20:16 UTC](https://rubytalk.org/t/dbi-and-msaccess/5132 "2003-03-12T20:16:04Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Rasmus\_Debitsch](https://avatars.discourse-cdn.com/v4/letter/r/90ced4/32.png) [@Rasmus\_Debitsch](https://rubytalk.org/u/Rasmus_Debitsch)\
**Post date:** [12 March 2003 20:16 UTC](https://rubytalk.org/t/dbi-and-msaccess/5132/1 "2003-03-12T20:16:04Z")

</div>

I want to access a MSAccess database with Ruby. I’m new to databases and  
Ruby, so my question is quite basic.

When I try to connect to the database with DBI a error message shows up. I  
have no idea if there is something wrong with my code or my system  
configuration. Perhaps someone can provide a working code fragment?

Environment:  
Windows ME, MSAccess not installed  
Ruby 173-7  
A mbd file

Code:  
require ‘dbi’

dbq = "dbq=C:/Benutzer/Test.mdb"  
dsn = “driver=Microsoft Access Driver(\*.mdb);#{dbq}”

DBI.connect(“DBI:ODBC:#{dsn}”,"","")

Error:  
C:/PROGRAMME/RUBY/lib/ruby/site\_ruby/1.7/DBD/ODBC/ODBC.rb:71:in `connect': S1090 (0) [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge. (DBI::DatabaseError) from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:496:in`connect’  
from C:/PROGRAMME/RUBY/lib/ruby/site\_ruby/1.7/dbi/dbi.rb:322:in `connect’

> **···**
>
> - 
> 
> Rasmus

---

<div class="post-metadata">

**Author:** ![HAL\_9000](https://avatars.discourse-cdn.com/v4/letter/h/90ced4/32.png) [@HAL\_9000](https://rubytalk.org/u/HAL_9000)\
**Post date:** [12 March 2003 20:23 UTC](https://rubytalk.org/t/dbi-and-msaccess/5132/2 "2003-03-12T20:23:51Z")

</div>

> I want to access a MSAccess database with Ruby. I’m new to databases and  
> Ruby, so my question is quite basic.
> 
> When I try to connect to the database with DBI a error message shows up. I  
> have no idea if there is something wrong with my code or my system  
> configuration. Perhaps someone can provide a working code fragment?
> 
> Environment:  
> Windows ME, MSAccess not installed  
> Ruby 173-7  
> A mbd file

[snip]

I’ve never used DBI (though I plan to!), so I may  
be wrong here. If so, someone will correct me.

My impression is that DBI only interfaces with  
the existing database software, i.e., you would  
have to have Access installed in order for this  
to work.

Hal

> **···**
>
> ----- Original Message -----  
> From: “Rasmus Debitsch” [Debitsch@T-Online.de](mailto:Debitsch@T-Online.de)  
> Newsgroups: comp.lang.ruby  
> To: “ruby-talk ML” [ruby-talk@ruby-lang.org](mailto:ruby-talk@ruby-lang.org)  
> Sent: Wednesday, March 12, 2003 2:16 PM  
> Subject: DBI and MSAccess

---

<div class="post-metadata">

**Author:** ![Greg\_Brondo](https://avatars.discourse-cdn.com/v4/letter/g/eada6e/32.png) [@Greg\_Brondo](https://rubytalk.org/u/Greg_Brondo)\
**Post date:** [12 March 2003 20:56 UTC](https://rubytalk.org/t/dbi-and-msaccess/5132/3 "2003-03-12T20:56:11Z")

</div>

Rasmus Debitsch wrote:

> I want to access a MSAccess database with Ruby. I’m new to databases and  
> Ruby, so my question is quite basic.
> 
> When I try to connect to the database with DBI a error message shows up. I  
> have no idea if there is something wrong with my code or my system  
> configuration. Perhaps someone can provide a working code fragment?
> 
> Environment:  
> Windows ME, MSAccess not installed  
> Ruby 173-7  
> A mbd file
> 
> Code:  
> require ‘dbi’
> 
> dbq = “dbq=C:/Benutzer/Test.mdb”  
> dsn = “driver=Microsoft Access Driver(\*.mdb);#{dbq}”
> 
> DBI.connect(“DBI:ODBC:#{dsn}”,“”,“”)
> 
> Error:  
> C:/PROGRAMME/RUBY/lib/ruby/site\_ruby/1.7/DBD/ODBC/ODBC.rb:71:in `connect': S1090 (0) [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge. (DBI::DatabaseError) from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:496:in `connect’  
> from C:/PROGRAMME/RUBY/lib/ruby/site\_ruby/1.7/dbi/dbi.rb:322:in `connect’
> 
> - 
> 
> Rasmus

You can use the rubyinstaller @ [rubyinstaller.sf.net](http://rubyinstaller.sf.net) (version 1.6.8) is  
good. It contains the ODBC DBD/DBI drivers. Then all you need is to  
install the MDAC package from microsoft (although you might already have

and ODBC driver). On Windows 98 go into the control panel and look for  
ODBC datasources. There should be a tab that says ‘drivers’. Check for  
a MS Access driver. You can then select the System tab, hit new, choose  
MS Access, then select the MDB file you have on your drive. Next create  
a test script to use the DBI:ODBC. Here’s some code I swiped myself:

require ‘dbi’

begin  
# connect to the MySQL server  
dbh = DBI.connect(“DBI:ODBC:lli”, “root”, “”)  
# get server version string and display it  
row = dbh.select\_one(“SELECT VERSION()”)  
puts "Server version: " + row[0]  
dbh.select\_all(“select \* from types”) do |row|  
puts row[0].to\_s + " " + row[1]  
end  
rescue DBI::DatabaseError =\> e  
puts “An error occurred”  
print "Error code: ", e.err, “\n”  
print "Error message: ", e.errstr, “\n”  
ensure  
# disconnect from server  
dbh.disconnect if dbh  
end

Replace ‘lli’ with whatever you named your ODBC datasource. Replace  
‘root’ with a userid (with MS Access you probably won’t need one).

You can find more info on the ruby DBI @ [http://ruby-dbi.sourceforge.net](http://ruby-dbi.sourceforge.net)

Good skill to you!

Greg Brondo

---

<div class="post-metadata">

**Author:** ![Chris\_Morris](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@Chris\_Morris](https://rubytalk.org/u/Chris_Morris)\
**Post date:** [12 March 2003 20:58 UTC](https://rubytalk.org/t/dbi-and-msaccess/5132/4 "2003-03-12T20:58:33Z")

</div>

> I want to access a MSAccess database with Ruby. I’m new to databases and  
> Ruby, so my question is quite basic.
> 
> When I try to connect to the database with DBI a error message shows up. I  
> have no idea if there is something wrong with my code or my system  
> configuration. Perhaps someone can provide a working code fragment?
> 
> Environment:  
> Windows ME, MSAccess not installed

You do not have to have Access installed, just the MDAC that includes  
ODBC/ADO, depending on how you connect.

I access Access (ha) through win32ole → ADO – so I can’t help you with the  
DBI specifics.

> Error:  
> C:/PROGRAMME/RUBY/lib/ruby/site\_ruby/1.7/DBD/ODBC/ODBC.rb:71:in `connect': S1090 (0) [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge. (DBI::DatabaseError) from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:496:in `connect’  
> from C:/PROGRAMME/RUBY/lib/ruby/site\_ruby/1.7/dbi/dbi.rb:322:in `connect’

Also, if you have the ability to post an English ODBC error msg, I might be  
able to help more.

Chris  
[http://clabs.org](http://clabs.org)

---

<div class="post-metadata">

**Author:** ![Rasmus\_Debitsch](https://avatars.discourse-cdn.com/v4/letter/r/90ced4/32.png) [@Rasmus\_Debitsch](https://rubytalk.org/u/Rasmus_Debitsch)\
**Post date:** [20 March 2003 16:37 UTC](https://rubytalk.org/t/dbi-and-msaccess/5132/5 "2003-03-20T16:37:49Z")

</div>

Thanks to all trying to help me.

Here is what I figured out:

- The ODBC module of DBD can’t handle DSN-less connection.
- I solved the problen by two methods:  
(1) Create a DSN and using the DSN in the connection  
(2) Changing the connect method in ODBC.rb to

def connect(dbname, user, auth, attr)  
if dbname.split(‘;’).length \> 1  
# DSNless connection  
drv = ::ODBC::Driver.new;  
drv.name = ‘DSNless’  
dbname.split(‘;’).each do |param|  
pv = param.split(‘=’);  
if pv.length \< 2  
next  
end  
drv.attrs[pv[0]] = pv[1]  
end  
db = ::ODBC::Database.new  
handle = db.drvconnect(drv)  
return Database.new(handle, attr)  
else  
# DSN given  
handle = ::ODBC.connect(dbname, user, auth)  
return Database.new(handle, attr)  
end  
rescue ODBCErr =\> err  
raise DBI::DatabaseError.new(err.message)  
end  
end

and using the DSN-less connection

db=DBI.connect(‘DBI:ODBC:driver=Microsoft Access Driver (\*.mdb);  
dbq=path\_of\_my\_database.mdb’)

in my program.

Rasmus

“Rasmus Debitsch” [Debitsch@T-Online.de](mailto:Debitsch@T-Online.de) wrote in message news:[b4o3eg$kpn$04$1@news.t-online.com](mailto:b4o3eg$kpn$04$1@news.t-online.com)…

> **···**
>
> > I want to access a MSAccess database with Ruby. I’m new to databases and  
> > Ruby, so my question is quite basic.
> > 
> > When I try to connect to the database with DBI a error message shows up. I  
> > have no idea if there is something wrong with my code or my system  
> > configuration. Perhaps someone can provide a working code fragment?
> > 
> > Environment:  
> > Windows ME, MSAccess not installed  
> > Ruby 173-7  
> > A mbd file
> > 
> > Code:  
> > require ‘dbi’
> > 
> > dbq = “dbq=C:/Benutzer/Test.mdb”  
> > dsn = “driver=Microsoft Access Driver(\*.mdb);#{dbq}”
> > 
> > DBI.connect(“DBI:ODBC:#{dsn}”,“”,“”)
> > 
> > Error:  
> > C:/PROGRAMME/RUBY/lib/ruby/site\_ruby/1.7/DBD/ODBC/ODBC.rb:71:in `connect': S1090 (0) [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge. (DBI::DatabaseError) from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:496:in `connect’  
> > from C:/PROGRAMME/RUBY/lib/ruby/site\_ruby/1.7/dbi/dbi.rb:322:in `connect’
> > 
> > - 
> > 
> > Rasmus

---

<div class="post-metadata">

**Author:** ![Michael\_Neumann2](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@Michael\_Neumann2](https://rubytalk.org/u/Michael_Neumann2)\
**Post date:** [13 March 2003 08:59 UTC](https://rubytalk.org/t/dbi-and-msaccess/5132/6 "2003-03-13T08:59:53Z")

</div>

Chris Morris wrote:

> > I want to access a MSAccess database with Ruby. I’m new to databases and  
> > Ruby, so my question is quite basic.
> > 
> > When I try to connect to the database with DBI a error message shows up. I  
> > have no idea if there is something wrong with my code or my system  
> > configuration. Perhaps someone can provide a working code fragment?
> > 
> > Environment:  
> > Windows ME, MSAccess not installed
> 
> You do not have to have Access installed, just the MDAC that includes  
> ODBC/ADO, depending on how you connect.
> 
> I access Access (ha) through win32ole → ADO – so I can’t help you with the  
> DBI specifics.
> 
> > Error:  
> > C:/PROGRAMME/RUBY/lib/ruby/site\_ruby/1.7/DBD/ODBC/ODBC.rb:71:in `connect': S1090 (0) [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge. (DBI::DatabaseError) from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:496:in `connect’  
> > from C:/PROGRAMME/RUBY/lib/ruby/site\_ruby/1.7/dbi/dbi.rb:322:in `connect’
> 
> Also, if you have the ability to post an English ODBC error msg, I might be  
> able to help more.

In English:

“Invalid character string or buffer length”

Seems like an syntax error in the DSN or something alike (perhaps the path name?).

Regards,

Michael

---

<div class="post-metadata">

**Author:** ![Rasmus\_Debitsch](https://avatars.discourse-cdn.com/v4/letter/r/90ced4/32.png) [@Rasmus\_Debitsch](https://rubytalk.org/u/Rasmus_Debitsch)\
**Post date:** [13 March 2003 19:19 UTC](https://rubytalk.org/t/dbi-and-msaccess/5132/7 "2003-03-13T19:19:21Z")

</div>

Thank you for the answer.

The version with system DSN works. Now I have a base for my experiments.

I found some hints in the net. Some people point out, that the string of a  
DSN less connection must not longer than 32 characters for some odbc  
implemetation. Other problems may be the right escaping of quotes or  
slashes.  
I will try the perl or tcl version and go back to ruby than.

> **···**
>
> –  
> Rasmus

---

<div class="post-metadata">

**Author:** ![Peter\_Frey](https://avatars.discourse-cdn.com/v4/letter/p/4da419/32.png) [@Peter\_Frey](https://rubytalk.org/u/Peter_Frey)\
**Post date:** [14 March 2003 20:42 UTC](https://rubytalk.org/t/dbi-and-msaccess/5132/8 "2003-03-14T20:42:23Z")

</div>

“Hal E. Fulton” [hal9000@hypermetrics.com](mailto:hal9000@hypermetrics.com) wrote in message  
[news:08e901c2e8d5$ca49afa0$0300a8c0@austin.rr.com](mailto:news:08e901c2e8d5$ca49afa0$0300a8c0@austin.rr.com)…

> From: “Rasmus Debitsch” [Debitsch@T-Online.de](mailto:Debitsch@T-Online.de)  
> Newsgroups: comp.lang.ruby  
> To: “ruby-talk ML” [ruby-talk@ruby-lang.org](mailto:ruby-talk@ruby-lang.org)  
> Sent: Wednesday, March 12, 2003 2:16 PM  
> Subject: DBI and MSAccess
> 
> > I want to access a MSAccess database with Ruby. I’m new to databases and  
> > Ruby, so my question is quite basic.
> > 
> > When I try to connect to the database with DBI a error message shows up.  
> > I  
> > have no idea if there is something wrong with my code or my system  
> > configuration. Perhaps someone can provide a working code fragment?
> > 
> > Environment:  
> > Windows ME, MSAccess not installed  
> > Ruby 173-7  
> > A mbd file
> 
> [snip]
> 
> I’ve never used DBI (though I plan to!), so I may  
> be wrong here. If so, someone will correct me.
> 
> My impression is that DBI only interfaces with  
> the existing database software, i.e., you would  
> have to have Access installed in order for this  
> to work.
> 
> Hal

No, Access does not need to be installed. You can access a \*.mdb database  
through ODBC directly. For example Word and Excell can access such  
databases. VB or Delphi and other tools can, too.  
ODBC is much more than just an API; if you use Microsoft’s MDAC you can even  
do SQL joins of two databases that are created by very different engines  
like FoxPro( _.dbf ) and Access (_.mdb). This means ODBC itself PARSES SQL!  
(That’s why there are options for ‘direct’ SQL, i.e.: give it to the  
database without parsing)  
No wonder ODBC can be such a temperamental thing…

Peter

> **···**
>
> > ----- Original Message -----

---

<div class="post-metadata">

**Author:** ![Arthur](https://avatars.discourse-cdn.com/v4/letter/a/958977/32.png) [@Arthur](https://rubytalk.org/u/Arthur)\
**Post date:** [14 April 2003 02:16 UTC](https://rubytalk.org/t/dbi-and-msaccess/5132/9 "2003-04-14T02:16:03Z")

</div>

A quick follow-up to this ealier question, which I am now facing:

Since Access is not required to be install in order to use DBI to  
access the database, can I then:

(1) Move the Access .mdb file to a Linux box  
(2) Use DBI to access the .mdb file on Linux

I am not very familiar with ODBC. It certainly appears to be required  
to access MS databases on Windows, but how about on Linux? Does the  
Ruby DBI module include an ODBC driver for Linux?

Thanks,

Arthur

[debitsch@t-online.de](mailto:debitsch@t-online.de) (Rasmus) wrote in message news:[1289054f.0303200823.31d51e5@posting.google.com](mailto:1289054f.0303200823.31d51e5@posting.google.com)…

> **···**
>
> > Thanks to all trying to help me.
> > 
> > Here is what I figured out:
> > 
> > - The ODBC module of DBD can’t handle DSN-less connection.
> > - I solved the problen by two methods:  
> > (1) Create a DSN and using the DSN in the connection  
> > (2) Changing the connect method in ODBC.rb to
> > 
> > def connect(dbname, user, auth, attr)  
> > if dbname.split(‘;’).length \> 1  
> > # DSNless connection  
> > drv = ::ODBC::Driver.new;  
> > drv.name = ‘DSNless’  
> > dbname.split(‘;’).each do |param|  
> > pv = param.split(‘=’);  
> > if pv.length \< 2  
> > next  
> > end  
> > drv.attrs[pv[0]] = pv[1]  
> > end  
> > db = ::ODBC::Database.new  
> > handle = db.drvconnect(drv)  
> > return Database.new(handle, attr)  
> > else  
> > # DSN given  
> > handle = ::ODBC.connect(dbname, user, auth)  
> > return Database.new(handle, attr)  
> > end  
> > rescue ODBCErr =\> err  
> > raise DBI::DatabaseError.new(err.message)  
> > end  
> > end
> > 
> > and using the DSN-less connection
> > 
> > db=DBI.connect(‘DBI:ODBC:driver=Microsoft Access Driver (\*.mdb);  
> > dbq=path\_of\_my\_database.mdb’)
> > 
> > in my program.
> > 
> > Rasmus
> > 
> > “Rasmus Debitsch” [Debitsch@T-Online.de](mailto:Debitsch@T-Online.de) wrote in message news:[b4o3eg$kpn$04$1@news.t-online.com](mailto:b4o3eg$kpn$04$1@news.t-online.com)…
> > 
> > > I want to access a MSAccess database with Ruby. I’m new to databases and  
> > > Ruby, so my question is quite basic.

---

<div class="post-metadata">

**Author:** ![MikkelFJ1](https://avatars.discourse-cdn.com/v4/letter/m/ce7236/32.png) [@MikkelFJ1](https://rubytalk.org/u/MikkelFJ1)\
**Post date:** [13 March 2003 10:18 UTC](https://rubytalk.org/t/dbi-and-msaccess/5132/10 "2003-03-13T10:18:11Z")

</div>

“Michael Neumann” [uu9r@stud.uni-karlsruhe.de](mailto:uu9r@stud.uni-karlsruhe.de) wrote in message  
[news:20030313085950.GA4441@rz.uni-karlsruhe.de](mailto:news:20030313085950.GA4441@rz.uni-karlsruhe.de)…

> “Invalid character string or buffer length”
> 
> Seems like an syntax error in the DSN or something alike (perhaps the path  
> name?).

I just wonder what ODBC is doing in the connection string. I haven’t worked  
with DBI, so this might be the standard way. But I would have thought you’d  
connect to Access directly i.e. such that DBI uses ADO internally, or better  
yet, DAO.

Mikkel

---

<div class="post-metadata">

**Author:** ![HAL\_9000](https://avatars.discourse-cdn.com/v4/letter/h/90ced4/32.png) [@HAL\_9000](https://rubytalk.org/u/HAL_9000)\
**Post date:** [15 March 2003 00:42 UTC](https://rubytalk.org/t/dbi-and-msaccess/5132/11 "2003-03-15T00:42:35Z")

</div>

> > My impression is that DBI only interfaces with  
> > the existing database software, i.e., you would  
> > have to have Access installed in order for this  
> > to work.
> > 
> > Hal
> 
> No, Access does not need to be installed. You can access a \*.mdb database  
> through ODBC directly. For example Word and Excell can access such  
> databases. VB or Delphi and other tools can, too.  
> ODBC is much more than just an API; if you use Microsoft’s MDAC you can  
> even  
> do SQL joins of two databases that are created by very different engines  
> like FoxPro( _.dbf ) and Access (_.mdb). This means ODBC itself PARSES  
> SQL!  
> (That’s why there are options for ‘direct’ SQL, i.e.: give it to the  
> database without parsing)  
> No wonder ODBC can be such a temperamental thing…

That’s nice to know. Thanks…

Hal

> **···**
>
> ----- Original Message -----  
> From: “Peter Frey” [peterfrey@sympatico.ca](mailto:peterfrey@sympatico.ca)  
> Newsgroups: comp.lang.ruby  
> To: “ruby-talk ML” [ruby-talk@ruby-lang.org](mailto:ruby-talk@ruby-lang.org)  
> Sent: Friday, March 14, 2003 2:42 PM  
> Subject: Re: DBI and MSAccess
