Assigning Array to an OLE object field in access database

[Doicare]

Hi all

I am facing a problem in storing an array of integers to an OLE object field in the database, the used code for this action is listed below:

Sub Import_data()

 

Dim oAccesss As New ADODB.Connection
Dim oRecordset11 As New ADODB.Recordset
oAccesss.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\DBS.mdb;"
oRecordset11.Open "Select OLEdata From Table1", oAccesss, adOpenKeyset, adLockOptimistic

Dim SS(10) As integer

 

For N = 0 To 9

SS(N) = rnd
Next N

With oRecordset11
.Fields.Item(0) = SS

.update

end with

 

End sub

Kindly Advice.

[zmbd]

You're trying to place an array in a single field in a record source, yes?

What error are you getting...

-z

[Doicare]

Yes You are right, I am trying to place an array in a single field, the field data type is OLE Object.

The error I get is "Type Mismatch" and the error location at line 20 in the listed above code.

[zmbd]

I don't think you can do so in MS Access... I'll do a tad more checking in few reference books.

I guess I don't understand why you would want to store an array in an Access table. Can you explain a little more about what you hope to accomplish by doing so? Without any further information, this leads me to suspect that there may be some further normalization that the database could benifit from ( Database Normalization & Table Structures ) .

[Doicare]

Do you think it can be done in MS SQL?

[zmbd]

Arrays in a single field in RDMS - Not Advised.

There are workarounds to store the data from an array in both MS Access and I believe in MYSQL (you'll need to query in that forum...) However, I do not believe that you will be able to store the array as native even in MYSQL.

There is the multi-value field that is new to MSAccess-(2007/2010). http://office.microsoft.com/en-us/ac...001233722.aspx

Admittedly, I haven't used this field type; however from what I have read, and the examples I've seen, I would avoid these like the plague if I were you. Anything I’ve seen these used for can be readily done using the rules for normalization!

Simply Put, arrays are, IMHO, the for-runner of database tables… so why put a table within a table?

Once again, why are you trying to store an array in a single field of a table? In a RDMS this is normally not considered best practice.

IMHO: If you are trying to store an array, more than likely the data is missing a normalization step.
-z