Solved

Dynamic arrays in Access 97

Posted on 1997-09-19
7
354 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

749 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