# Inserting data from an array into an ms access 2000 table

**URL:** <https://www.ruby-forum.com/t/inserting-data-from-an-array-into-an-ms-access-2000-table/143588>\
**Category:** Ruby\
**Created:** [July 20, 2008, 11:05pm UTC](https://www.ruby-forum.com/t/inserting-data-from-an-array-into-an-ms-access-2000-table/143588 "2008-07-20T23:05:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![cdeford](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/8edcca/32.png) [@cdeford](https://www.ruby-forum.com/u/cdeford)\
**Post date:** [July 20, 2008, 11:05pm UTC](https://www.ruby-forum.com/t/inserting-data-from-an-array-into-an-ms-access-2000-table/143588/1 "2008-07-20T23:05:37Z")

</div>

Good Afternoon,

I am trying to figure out how to get data from an array into an access  
table.

On the rubyonwindows site I found the following code:

db = AccessDb.new(‘c:\Baseball\lahman54.mdb’)  
db.opendb.query(“SELECT \* FROM AllStar WHERE playerID =  
‘conceda01’;”)field\_names = db.fields  
rows = db.data  
db.execute(“INSERT INTO HallOfFame VALUES (‘Dave’, ‘Concepcion’);”)  
db.close

This code works fine if I break the array up into seperate elements but  
I think there must be a simpler way.

this is how the array is built:

```
class <<Array
      def multi(n, *args, &block)
              if args.empty?
                Array.new(n, &block)
                      else
                        Array.new(n) do
                        Array.multi(*args, &block)
                      end
              end
      end
end

```

card=Array.multi(24,24)

so the question is; How do i get the data loaded into this array into my  
access table called wps in a database wps.mdb?

I have been trying to do something like this:

db.execute(“INSERT INTO wps\_test VALUES(‘card [$h]’);”

Any help would be greatly appreciated.

---

<div class="post-metadata">

**Author:** ![cdeford](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/8edcca/32.png) [@cdeford](https://www.ruby-forum.com/u/cdeford)\
**Post date:** [July 22, 2008, 4:24am UTC](https://www.ruby-forum.com/t/inserting-data-from-an-array-into-an-ms-access-2000-table/143588/2 "2008-07-22T04:24:16Z")

</div>

Cooper Deford wrote:

> Good Afternoon,
> 
> I am trying to figure out how to get data from an array into an access  
> table.
> 
> On the rubyonwindows site I found the following code:
> 
> db = AccessDb.new(‘c:\Baseball\lahman54.mdb’)  
> db.opendb.query(“SELECT \* FROM AllStar WHERE playerID =  
> ‘conceda01’;”)field\_names = db.fields  
> rows = db.data  
> db.execute(“INSERT INTO HallOfFame VALUES (‘Dave’, ‘Concepcion’);”)  
> db.close
> 
> This code works fine if I break the array up into seperate elements but  
> I think there must be a simpler way.
> 
> this is how the array is built:
> 
> ```
> class <<Array
> def multi(n, *args, &block)
> if args.empty?
> Array.new(n, &block)
> else
> Array.new(n) do
> Array.multi(*args, &block)
> end
> end
> end
> end
> 
> ```
> 
> card=Array.multi(24,24)
> 
> so the question is; How do i get the data loaded into this array into my  
> access table called wps in a database wps.mdb?
> 
> I have been trying to do something like this:
> 
> db.execute(“INSERT INTO wps\_test VALUES(‘card [$h]’);”
> 
> Any help would be greatly appreciated.

Assuming you are working with something like the AccessDb class defined  
here…

> **[Using Ruby & ADO to Work with MS Access Databases](http://rubyonwindows.blogspot.com/2007/06/using-ruby-ado-to-work-with-ms-access.html)**
>
> A reader in the Ruby forum recently asked about using Ruby to get data from a Microsoft Access database. This can be done easily using the ...

…you could iterate over your array and generate/execute an SQL insert  
statement, like this:

my\_array.each do |a|  
b = a.collect{|x| x = “'” + x + “'”}  
c = b.join(“,”)  
sql = “INSERT INTO MyTable VALUES (#{c});”  
db.execute(sql)  
end

The above code can certainly be improved upon, but hopefully gives you  
an idea.

David

> **[Ruby on Windows](http://rubyonwindows.blogspot.com)**
>
> On using the Ruby programming language on the Microsoft Windows platform

---

<div class="post-metadata">

**Author:** ![cdeford](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/8edcca/32.png) [@cdeford](https://www.ruby-forum.com/u/cdeford)\
**Post date:** [July 22, 2008, 4:57am UTC](https://www.ruby-forum.com/t/inserting-data-from-an-array-into-an-ms-access-2000-table/143588/3 "2008-07-22T04:57:33Z")

</div>

David,

Thanks very much for your response. This works just fine as is. I fooled  
around quite a bit with join as part of the sql= statement but never  
thought to iterate through the array. This is great.

Thanks again,

Cooper