Solved

Dynamic arrays in Access 97

Posted on 1997-09-19
7
342 Views
Last Modified: 2008-03-10
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!

0
Comment
Question by:Chrrris
7 Comments
 
LVL 1

Expert Comment

by:haas090897
ID: 1956552
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
 
LVL 1

Author Comment

by:Chrrris
ID: 1956553
Well, I need to define an array as a field member of a table!

Is that what your final two lines are concering?


0
 
LVL 1

Expert Comment

by:haas090897
ID: 1956554
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Expert Comment

by:rosaly
ID: 1956555

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
 
LVL 1

Author Comment

by:Chrrris
ID: 1956556
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
 
LVL 3

Accepted Solution

by:
chapie earned 50 total points
ID: 1956557
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
 
LVL 1

Author Comment

by:Chrrris
ID: 1956558
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now