Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-15-2016, 01:20 AM   #1
smbrr
Newly Registered User
 
Join Date: Jun 2014
Posts: 61
Thanks: 3
Thanked 0 Times in 0 Posts
smbrr is on a distinguished road
Very strange behaviour in collection of arrays

Hi there,

I'm in the need of some help. I open a text file and store it in a collection of array:

Code:
Open filepath For Input As #filenumber
While Not EOF(filenumber)
    Line Input #filenumber, lineread
    file.Add Split("" & SEP & lineread, SEP) 'index starts at 1 for simplicity
Wend
Close #filenumber
Then I'm going to make a bunch of changes to this collection but it just... doesn't do it. The code works fine, but the arrays are literally unchanged:

Code:
For i = 1 To UBound(file(1))
    If file(1)(i) = "Sales Doc. Type" Then file(1)(i) = "Type"
    If file(1)(i) = "Name 1" Then file(1)(i) = "List Name"
    If file(1)(i) = "Main WorkCtr" Then file(1)(i) = "Mnwkctr"
Next i
I tried it in the immediate debug window, and sure enough I can execute all sort of code without error but none of the data inside my collection is changed.

Code:
      : Item 1(0) : "" : String
      : Item 1(1) : "Order" : String
      : Item 1(2) : "Sales Doc. Type" : String
      : Item 1(3) : "System Status" : String
      : Item 1(4) : "User Status" : String
      : Item 1(5) : "City" : String
      : Item 1(6) : "Name 1" : String
      : Item 1(7) : "Main WorkCtr" : String
      : Item 1(8) : "Country" : String
What the hell is this?

Thank you

smbrr is offline   Reply With Quote
Old 09-15-2016, 03:34 AM   #2
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 194 Times in 180 Posts
static will become famous soon enough static will become famous soon enough
Re: Very strange behaviour in collection of arrays

A Collection's items are read only.
Copy the item, change it, delete the original, add the copy.
static is offline   Reply With Quote
Old 09-15-2016, 03:41 AM   #3
smbrr
Newly Registered User
 
Join Date: Jun 2014
Posts: 61
Thanks: 3
Thanked 0 Times in 0 Posts
smbrr is on a distinguished road
Re: Very strange behaviour in collection of arrays

Quote:
Originally Posted by static View Post
A Collection's items are read only.
Copy the item, change it, delete the original, add the copy.
But what I'm putting in the collection should only be references to arrays, the arrays themselves should be be fully accessible, shouldn't they?

Like, as a workaround, I'm making a collection of collection of object containing 1 single parameter "value", so I can do:
Code:
file(i)(c).value = "whatever"
The pointer to that object is read only, but I can access the object and modify it, why is that not happening for my Arrays and is there a way I can make it happen?

If not I suppose I'll keep my work-around but that creates a lot of unnecessary code and overhead and irks me.

smbrr is offline   Reply With Quote
Old 09-15-2016, 07:21 AM   #4
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 194 Times in 180 Posts
static will become famous soon enough static will become famous soon enough
Re: Very strange behaviour in collection of arrays

Normal values, including arrays are read only and not pointers. Objects are ... special.

You could try using a dictionary object instead. AFAIK they're updateable.


msdn.microsoft.com/en-us/library/x4k5wbx4(v=vs.84).aspx
static is offline   Reply With Quote
Old 09-15-2016, 07:50 AM   #5
rpeare
Newly Registered User
 
Join Date: Sep 2016
Posts: 18
Thanks: 0
Thanked 3 Times in 2 Posts
rpeare is on a distinguished road
Re: Very strange behaviour in collection of arrays

If all you're interested in is changing the column headers can't you just artificially create the first row of the array then add all the subsequent records from your file skipping over the first line?
rpeare is offline   Reply With Quote
Old 09-15-2016, 08:30 AM   #6
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
You can write to an array of arrays . . .
Code:
Dim var
    
Open filepath For Input As #filenumber
While Not EOF(filenumber)
    If IsEmpty(var) Then
        ReDim var(0)
    Else
        ReDim Preserve var(UBound(var) + 1)
    End If
    Line Input #filenumber, lineread
    var(UBound(var)) = Split("" & SEP & lineread, SEP) 'index starts at 1 for simplicity
Wend
Close #filenumber
Or you can wrap your array in a class, so create a class module with code like...
Code:
Option Compare Database
Option Explicit

Public vLineArray as Variant
...and maybe you call it cYourClass.
Then add New instances of that class to the collection, and assign the array to the Public variable in the class....
Code:
Dim tmp As cYourClass
    
Open filepath For Input As #filenumber
While Not EOF(filenumber)
    Line Input #filenumber, lineread
    Set tmp = New cYourClass
    tmp.vLineArray = Split("" & SEP & lineread, SEP)
    File.Add tmp
Wend
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 09-15-2016, 10:53 PM   #7
smbrr
Newly Registered User
 
Join Date: Jun 2014
Posts: 61
Thanks: 3
Thanked 0 Times in 0 Posts
smbrr is on a distinguished road
Re: Very strange behaviour in collection of arrays

Quote:
Originally Posted by rpeare View Post
If all you're interested in is changing the column headers can't you just artificially create the first row of the array then add all the subsequent records from your file skipping over the first line?
No, I make a lot of changes to the file itself, the headers are just the tip of the iceberg.

I suppose doing an array of array is a good idea, it's just that I use collections almost everytime I need to store stuff in a list, and everytime I tried to use arrays, I've hit roadblocks like dimensions that can't be redim'd, arrays can't be class objects and so on, so I rarely use them at all.

I might do that then.

Performance wise, if I'm just looking through them with an index and not a string key, are array better than collections? Because my big collection of collection of objects gets really slow to access when I start hitting indexes over 30 000.

Thank you for the explanation of arrays and pointers. At the very least, trying to change the value in there should throw an error like it does for simple data types like a string in a collection.

smbrr is offline   Reply With Quote
Old 09-15-2016, 11:48 PM   #8
smbrr
Newly Registered User
 
Join Date: Jun 2014
Posts: 61
Thanks: 3
Thanked 0 Times in 0 Posts
smbrr is on a distinguished road
Re: Very strange behaviour in collection of arrays

Quote:
Originally Posted by smbrr View Post
Performance wise, if I'm just looking through them with an index and not a string key, are array better than collections?
To answer my own question, yes it's much faster. The speed is a perfect constant whether I access item #3 or item #56103, whereas the collection really crawled to 10 times slower toward the end.
smbrr is offline   Reply With Quote
Old 09-16-2016, 12:33 AM   #9
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 194 Times in 180 Posts
static will become famous soon enough static will become famous soon enough
Re: Very strange behaviour in collection of arrays

Quote:
I use collections almost everytime I need to store stuff in a list
Quote:
hitting indexes over 30 000
The obvious question (since this is a forum about Access, which is designed to store and manipulate large sets of data) is why?
static is offline   Reply With Quote
Old 09-16-2016, 01:30 AM   #10
smbrr
Newly Registered User
 
Join Date: Jun 2014
Posts: 61
Thanks: 3
Thanked 0 Times in 0 Posts
smbrr is on a distinguished road
Re: Very strange behaviour in collection of arrays

50k row isn't what I would call a large data set considering my databases have over 100 million rows.

As for Access being designed to store an manipulate data, for human-speed interaction, sure, not for program-speed interaction. Using tables and recordsets in VBA for every operation that needs a list of some kind would cause the code to run at a thousandth of the speed I'm used to (not to mention the pain of having to use Access objects). Much like my Excel VBA macros often read a table at the beginning and store it in collections of objects that are used for the whole macro instead of constantly reading/writing/rearranging ranges in the workbook.

It's often just text and we have like 2 GB of RAM to play with, so why bother?
smbrr is offline   Reply With Quote
Old 09-16-2016, 01:41 AM   #11
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 194 Times in 180 Posts
static will become famous soon enough static will become famous soon enough
Re: Very strange behaviour in collection of arrays

All good points dude. Thanks for the info.

static is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Strange behaviour odrap Forms 0 02-19-2009 05:21 AM
Strange Behaviour! Seb Forms 3 01-16-2007 06:07 PM
Strange behaviour help intrep11 Modules & VBA 1 02-08-2005 04:25 PM
strange Tab behaviour livvie Forms 3 10-08-2004 04:58 AM
Strange Behaviour? aziz rasul Modules & VBA 4 07-15-2003 05:12 AM




All times are GMT -8. The time now is 01:41 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World