Dynamic arrays in Access 97

How do I represent this datastructure in Acccess 97? I want Effects to be a dynamic array of indexes to another table called Effects.

    ID (primary key index)
    Unit A (integer reference to a table called Units)
    Unit B (integer reference to a table called Units)
    Operator (integer reference to a table called Operators)
    Effects (dynamic array of references to a tablecalled Effects)

What would be the best way to represent this structure?

I am reluctant to let the Effects field be a reference to an entire table (ex. there would be tables Effects1, Effects2, Effects3, ..., EffectsN)

Is there an alternative to this?

Perhaps it is possible to do something nifty with the use of OLE.

Another thing I have been considering is to let the Effects field contain a string of integers, each separated by a single space. But with this approach there would be no possibility of enforcing referential integrity between the Effects field and the Effects table.

I will raise the points to 200 if anyone has an answer!

LVL 1
ChrrrisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

haas090897Commented:
I havent realy figured out your question, but i think your question was how to use a dynamic array in a structure ( Type ).
I send some code to give you a clue how to accomplish a dynamic array in a Type.

Private Type udtDatastruct
    ID As Integer
    UnitA As Integer
    UnitB As Integer
    Operator As Integer
    Effect As Variant
End Type

Function Datastruct()
    Dim myDatastruct As udtDatastruct
    Dim myArr()
   
    Redim myArr(2)

    myArr(0) = "test"
    myArr(1) = "test2"
   
    myDatastruct.ID = 1
    myDatastruct.UnitA = 2
    myDatastruct.UnitB = 3
    myDatastruct.Operator = 1
    'myDatastruct.Effect = Array("test", "test2")
    myDatastruct.Effect = myArr
Debug.Print myDatastruct.Effect(1)

End function

There are two solutions on the part where you put the array in the effect member. One way is to use the array function, and the other way is to create a dynamic array to put in the effect member.
0
ChrrrisAuthor Commented:
Well, I need to define an array as a field member of a table!

Is that what your final two lines are concering?


0
haas090897Commented:
As i said, i havent figured out what you need, but you can put anything you like in the array, even objects.


In my code i only added some strings.
Can you reference the fields by name? Is it same table for all fields you need to put in the array? Or else you may need a multidimensional Array to put in effects member. What do you need the Type for? Another solution instead of Type, you may use a Class, which may contain methods and properties.
Do you need to store the effects in a table? In that case i am not sure how it works.

If you need to put objects in the array, just change myArr() to myArr() as object, and use set to put the object in the array like this:

   Dim myData As udtDatastruct
   Dim myObj() As Object
   Dim ws As Workspace
   Set ws = DBEngine(0)
   
   ReDim myObj(2)
   Set myObj(0) = ws.OpenDatabase("c:\finess\finess97.mdb" )
   Set myObj(1) = ws.OpenDatabase("g:\admin\admin2000sqlserver")
   
   myData.UnitA = 1
   myData.UnitB = 2
   myData.Effect = myObj 'This is the line that stores the array
   Debug.Print myData.Effect(1).Name 'Using this line to check if eeverything is okey.


0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rosalyCommented:

Well, I think I have the answer that will qualify for the
200 points! That is:

First, you CANNOT let the Effects fields be a reference to an
entire table!!! Sharing this responsibility between Effects 1,
2, 3 etc, sounds like a much more reasonable solution.

Second, the string of integers should have double space
between them, thus allowing the OLE solution to be niftier.

The dynamics of indexes will be greatly improved if you follow these simple solutions!!

Hope I have been helpful

:)

0
ChrrrisAuthor Commented:
I am sorry, dear Rosaly. I cannot award you the points, as I can't see that you have answered my question. But thank you for your trying to help!
0
chapieCommented:
why don't you have an effects table that look's up the main table that way you have a one-to-many relationship (1 main table to many effects), I use this structure a lot when the manufacturing company I work for asked for a system to track their jobs, they might have 1 job with many due dates, it doesn't have to be a dynamic array, you would simply drop the effects field completely from the main table, and have the effects table do the lookup so that way you can have many effects lookup 1 main record
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChrrrisAuthor Commented:
That is what I have done. And I bought a couple of books on SQL too. In fact, I have settled for several many-to-many relationships. With a bit of SQL querying it can be done without too much fuzz!




0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.