By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,459 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

Assigning Array to an OLE object field in access database

P: 3
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:

Expand|Select|Wrap|Line Numbers
  1. Sub Import_data()
  2.  
  3.  
  4.  
  5. Dim oAccesss As New ADODB.Connection
  6. Dim oRecordset11 As New ADODB.Recordset
  7. oAccesss.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\DBS.mdb;"
  8. oRecordset11.Open "Select OLEdata From Table1", oAccesss, adOpenKeyset, adLockOptimistic
  9.  
  10. Dim SS(10) As integer
  11.  
  12.  
  13.  
  14. For N = 0 To 9
  15.  
  16. SS(N) = rnd
  17. Next N
  18.  
  19. With oRecordset11
  20. .Fields.Item(0) = SS
  21.  
  22. .update
  23.  
  24. end with
  25.  
  26.  
  27.  
  28. End sub
  29.  

Kindly Advice.
Jul 15 '12 #1
Share this Question
Share on Google+
5 Replies


zmbd
Expert Mod 5K+
P: 5,397
You're trying to place an array in a single field in a record source, yes?

What error are you getting...

-z
Jul 15 '12 #2

P: 3
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.
Jul 15 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
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 ) .
Jul 15 '12 #4

P: 3
Do you think it can be done in MS SQL?
Jul 16 '12 #5

zmbd
Expert Mod 5K+
P: 5,397
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
Jul 16 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.