Solved

Dynamic arrays in Access 97

Posted on 1997-09-19
7
346 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

914 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

14 Experts available now in Live!

Get 1:1 Help Now