?
Solved

Dynamic arrays in Access 97

Posted on 1997-09-19
7
Medium Priority
?
361 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 200 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

771 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