Array - Collection - Dictionaries? (2 Viewers)

[Galaxiom]

See the link in @Isaac's post for why I don't use integer for anything.

I do very little in Access now. In SQL Server, Integer is the equivalent of Long in Access/VBA.

 

[JonXL]

I do very little in Access now. In SQL Server, Integer is the equivalent of Long in Access/VBA.

This is in the Excel forum...

 

[moke123]

This is in the Excel forum...

Blame the OP @Uncle Gizmo . There's no reference to excel anywhere in the thread.

 

[The_Doc_Man]

Uh... guys, look at the top, just above the thread title and just below the menu bar....?

 

[Uncle Gizmo]

Blame the OP @Uncle Gizmo . There's no reference to excel anywhere in the thread

Paul Kelly is an Excel Microsoft MVP and the video example is in Excel. ..

 

[moke123]

oops.

 

[Minty]

I use arrays extensively in Excel VBA - an order of magnitude faster than referring to cell contents if you are maneuvering a lot of data around, or doing string comparisons, finding changes in data, etc.
I've not had much need for dictionaries or collections, as most of my work is done in Access/SQL backend based, and there are other techniques I'm more familiar with.

 

[Isaac]

There is a difference here between declaring a variable as INTEGER or LONG and declaring a field as INTEGER or LONG. I would typically agree with letting most counting variables be LONGs. However, if you want specific behavior of numbers then data typing of your variables becomes important. If there is a reason to consider the range of a number, you should "use the right tool for the job" - and variable sizes are one way to assure that

I assume by field, you mean a Column in a table?
If yes, then of course, column datatypes are extremely, extremely important and not to be taken lightly.
I thought we were only talking about VBA variables.

However, if you want specific behavior of numbers then data typing of your variables becomes important

But that's just the point, I've yet to hear anything in this thread that demonstrates why a person would, for example, benefit from from the extra effort of deciding between Integer and Long, in VBA (meaning a variable), and choosing Integer over Long. Maybe I just missed it?

If there really isn't any specific behavior (other than a lower-range one like Integer is more likely to cause you to forget its range and result in a runtime error), then really the only specific behavior we can point to seems to point right back to the wisdom of only using one - Long.

If we were talking about right-sizing a database table Column, I'll support that all day long. i.e. Varchar(50) vs. varchar(8000)
But no serious person can make a case for that in VBA and memory and that kind of thing...such as "your Longs are taking up too much memory", etc.

 

[isladogs]

This doesn't just apply to FIELDS in a table. You are making blanket statements about variables that can easily be shot down.

I'm perfectly serious when I say that where I know a variable can never exceed the limits of an integer or even a byte, I will declare it as such in VBA.
For example, I may want to set variables to represent the R,G,B components in a colour value. As the range in each case is 0 to 255, each would be a byte variable
Similarly, a page number in a report can only be an integer value so if I want to reference that, I would use an integer variable.
Also, if using a variable to reference the width of a form in twips as that is subject to an integer limit.
I'm sure I could come up with many more examples

BTW For consistency, do you also refer to a record as a ROW in Access? If not, why not?

 

[Isaac]

Very few compilers ARE that cool. In my lifetime I have worked with at least three FORTRAN compilers (IBM and DEC), ALGOL (DEC), BASIC (DEC and Microsoft), P/L-1G (DEC), PASCAL (BORLAND), VBA (MS)... - and not a single one cared about whether my expression was numerically kosher at compile time. They waited until run-time to lower the boom on me if I transgressed

As far as old systems are concerned, I will take your word for it.

However, the implication that this is rare is simply incorrect. In Visual Studio, the (arguably) most used development platform in the world, the compiler definitely is that cool, hence my complaint, it would be wonderful if VBA could get a makeover.

Try declaring a variable as Short in a .Net project in VS and then assigning it 33000, then Run Code Analysis (or any parsing-type action), it definitely tells you.

 

[Isaac]

This doesn't just apply to FIELDS in a table. You are making blanket statements about variables that can easily be shot down

Who are you addressing? I'm not the one that implied we were talking about columns in a table, Doc was.

I'm perfectly serious when I say that where I know a variable can never exceed the limits of an integer or even a byte, I will declare it as such in VBA.
For example, I may want to set variables to represent the R,G,B components in a colour value. As the range in each case is 0 to 255, each would be a byte variable
Similarly, a page number in a report can only be an integer value so if I want to reference that, I would use an integer variable.
Also, if using a variable to reference the width of a form in twips as that is subject to an integer limit.
I'm sure I could come up with many more examples

With all due respect, you could come up with more examples but none of your examples have demonstrated anything about the benefit of doing that. You are simply asserting that's what you do.

BTW For consistency, do you also refer to a record as a ROW in Access? If not, why not?

Not necessarily, because I know in the wider lingo of relational databases, Record and Row are synonymous enough.
In contrast, "Field" is something 99% used by Access people with limited exposure to the major commercial RDBMS.

It's a matter of getting used to the most proper lingo that fits the situation best for clarity and correctness.
I don't use "field" to refer to a Column because Field is too generic a word that can mean too many things.
IMO, properly understanding the concept of Column (rather than Field which is most often used to refer to a one-dimensional device) is one of the most fundamental aspects of relational database learning. In fact teaching people to act on Columns rather than one-dimensional [anything] is one of the first things they usually hammer when an Access person starts learning SQL. It is wildly resisted by many, but important nonetheless.

Just one man's opinion.
Or, go to a SQL dev shop and start talk about adding Fields to a table, find out if what I'm saying is true.

Anyway, I won't argue further because it doesn't seem productive any more. Cheers

 

[isladogs]

@Isaaac
I was addressing you

With all due respect, you could come up with more examples but none of your examples have demonstrated anything about the benefit of doing that. You are simply asserting that's what you do.

I didn't think I needed to spell it out beyond what I had written earlier
Using a Long instead of Integer or Integer instead of Byte in the examples given, would trigger an error when the possible value range was exceeded. Better to prevent the error in the first place

It's a matter of getting used to the most proper lingo that fits the situation best for clarity and correctness.

Yes I agree. When discussing SQL Server, use the correct terminology for SQL Server (etc)

But when referencing Access, use the correct terminology for Access
Field not column, Record not row

 

[Isaac]

I didn't think I needed to spell it out beyond what I had written earlier
Using a Long instead of Integer or Integer instead of Byte in the examples given, would trigger an error when the possible value range was exceeded. Better to prevent the error in the first place

I'm going to give you the benefit of the doubt that the reason you wrote just that, is because you (understandably, I do the same thing sometimes) speed-read a rather long series of posts and perhaps my meaning hadn't come out clearly to you.

Because what you're saying doesn't make sense to me in consideration of what I posted. And then it doubly makes no sense in consideration of the next thing I'd posted.

- Yes, you will get a runtime error if you declare as Integer and assign an out of range value.
- No, this risk is not equal to Integer and Long, as one of them has the larger range.

Maybe you can explain to me how declaring something as an Integer helps you avoid an out-of-range value versus a Long? It makes no sense. The Long is the larger value. That's why I tend to just use Long in the first place. If I had suggested to always use Integer or Byte, then what you said makes sense to reply. It's you who are risking the error, respectfully, by choosing anything less (Byte, Integer) than the larger (Long) value variable especially in a scenario where there is virtually no perceivable benefit in doing so, at least none that anybody has come up with in a hundred hours of posting so far.

Using a Long instead of an Integer triggers an error when the value range is exceeded? Really?
I think it's just the opposite. That's why many people choose Long all the time. It covers Byte and Integer.

Perhaps you thought I was saying something along the lines of the opposite, in which case your response is sensible.

The whole idea is to avoid the error. Differentiating between Byte, Integer and Long--which presumes 100% certainty where there usually isn't that, with a couple rare exceptions you have quoted--increases your risk of making a mistake rather than just using Long.

You have a trip to take. You think you know the mileage distance, but there's always the chance that you change your mind later on or something unexpected happens. You have two cars to choose from. One with a half a gas/petrol tank full, one with a complete tank full.

Why stand there each time choosing the half tank or the full tank? Take the full tank and be on your way. Less dev time that way, IMO.

 

[isladogs]

Well I'd say that seems you've skim read what I wrote

One example
Define X. Y, Z as Long
Set each of them to a value less than 0 or more than 255.
Now add code like Me.ControlName.BackColor =RGB(X, Y, Z) and you'll get a run-time error.

I notice you didn't comment on the last three lines on my previous reply.

 

[Galaxiom]

I don't use "field" to refer to a Column because Field is too generic a word that can mean too many things.

What are generally known as Columns in other databases are actually called "Fields" in the Access object model. That meaning is entirely specific and doesn't "mean too many things".

Some people incorrectly use the word "Field" to refer to a Control in Access but that doesn't make it "mean too many things".

If you are talking about Access then it makes sense to use Access terminology. Pulling someone up for referring to a "Field" when they actually mean a field is your mistake not theirs.

 

[Galaxiom]

But that's just the point, I've yet to hear anything in this thread that demonstrates why a person would, for example, benefit from from the extra effort of deciding between Integer and Long, in VBA (meaning a variable), and choosing Integer over Long.

The extra effort? Oh such an arduous decision. Are you serious?

I can think of one disadvantage using a Long as a counter in a loop when it is perfectly obvious that there will never be more than a few cycles. An unexpected problem can potentially throw an out of range error much more quickly with the smaller counter variable while the Long will run two billion loops before failing.

 

[Isaac]

It depends on the case, and perhaps I've been too broad in my sweeping statements - My apologies.

You both raise several points that have made me re-think some of the things I said.

@isladogs the example you made of RGB is a good one. You also make a valid point about report pages, or something along those lines.
The fact that I myself have not had occasion to use a Variable for RGB color code properties, as well as Reports being the one Access object I've used much less of than some people made me blind to those particular use cases, so that was my mistake, and it was also wrong of me to resist the point you were making, choosing instead to focus on the portions of your statement I found confusing.

I still found some of your response confusing, as it sounded like you were suggesting that the overflow-type dangers of using Long were equal to the overflow-type dangers of using Integer, which puzzled me that you would suggest that and led me to believe that you maybe hadn't read the sequence or detail of my posts. My point was that in a world of less certainty than we would sometimes care to admit, it can be helpful to default to a larger range rather than limit ones self to a smaller range if there is no particular benefit in doing the limiting. Or at least, that's what I should have said, but I went too far and admit it.

As far as Column vs. Field. So there are some naming things that I have found Microsoft to be annoyingly back-and-forth on. A great example is Worksheet vs. Workbook. In my opinion, the best approach is to call the whole file a Workbook (always), and to call those little tabs at the bottom Worksheets (always). There are many reasons why it is helpful to stick to that single name approach which I trust will be obvious to everyone so I won't belabor that point. And yet, Microsoft has muddled the picture, and IMO helped confuse newcomers, by doing things like displaying "Microsoft Excel Worksheet" in File Explorer, when labelling the Type of an Excel file.

To my mind, they created a similar disservice to people's understanding and terminology by calling Access table columns "Fields".
Nonetheless, I was wrong to extend this opinion to make it sound like Microsoft has not chosen to call them Fields, or that it was wholeheartedly wrong to call them Fields, in Access context. Thanks to those who took the time to point this out.

I guess the sincere truth is that I've really enjoyed how my career has progressed to involve various database-related work, and one of the most enjoyable parts of that was when SQL Server came along in my journey. When that happened, I took great pains to identify which aspects of my "Access upbringing" were things that were best modified in some way in order to be most successful in the larger, wider, world of databases. There were many things of course, not all of them Access's fault, some of them just my own misunderstanding. Still, and you can agree or disagree with me on this, but it's been my observation over time and after having had the opportunity to interact with many database-type professionals across a healthy span of different firms, that there were many things about Access development that actually had created some poor habits, concepts, ways of thinking, and even naming, that--unless I changed, those things would continue to somewhat separate me from being better integrated in the larger database world. I'm sure it wasn't just "Access fault", I'm sure it was how I interacted with and understood Access, but I do observe that whether right or wrong, it does seem to be common that working extensively with Access at the beginning definitely puts most people on a path that requires many corrections in order to be successful at the other things I've mentioned. You can blame Access more or the person's understanding/application more, either way, it's a common denominator.

To my mind one of these habits that I was better off leaving behind was calling columns Fields. Not just because I found out I couldn't integrate into any non-Access db job while using that term for Columns, but (in my opinion) I also think there is a philosophy behind it that makes sense (which is hard to explain, as I've proved by trying to in vain several times and won't belabor now). Most practically because, I could intelligently communicate with my whole world using 'Column', whereas if I tried to use both and said 'Field' to any of my SQL Dev colleagues, I would get laughed all the way to the Watercooler. It made more sense (to me) to land on just one - a more universally accepted one.

However, I'll admit that doing so is a personal preference, and you are right that Microsoft calls them Fields (for Access), so whether I think that is silly or not, it is certainly not wrong for a person who is working with Access and wishes to use Access-approved terminology to continue to do so.

In my enthusiasm for what I have found works best for me, I wrongly failed to acknowledge that, from a strictly "Access lingo" perspective, "Fields" is perfectly fine and correct.

@Galaxiom Yes, I do think it matters when one writes a high volume of code that there are some decisions that aren't worth making, it all adds up. I posted earlier in this thread with an example that mentioned "Decimal(19,2)", you can read that for more explanation. You can make it sound funny (because you're right, a few moments to consider Byte or Long doesn't sound like much), but I stand by my opinion that on some subjects, it also makes sense to "default" yourself to something in order to 1) avoid the time to consider over and over daily/hourly/etc, and 2) in some cases a certain default might be less risky. I think I can safely say we have all had times where we felt certain the future would only involve _____ [insert some assumption you made, which drove your decision - any decision, not just Byte vs. Long], and then found out later that assumption was wrong. Is the best solution for that to default to Long? No, not necessarily, that's just one example of one person (me and the people on UA who recommended that to me, Banana I think but it was too many years ago to remember).....but I'll bet if we could be a fly on the wall, you probably also have your 'default' choices that could be criticized as not the 'perfect' choice, but also it does no harm, and allows you to move on quickly from the decision point.

Perhaps I can understand you better by remembering you may be talking mostly about considered, robust, deployed applications, whereas you can understand me better by knowing that a lot of my projects have been ad-hoc, short-term scripts that literally had me writing the same declarations over and over, and stopping to think of Byte, Int and Long just didn't make sense for me in almost all cases.

All in all, I was too sweeping in a couple things and would like to apologize for it. I allowed my personal opinions, as well as my personal experience and ambitions, to have too much influence in the way I said things, to the extent my generalizations became way too broad and I accept all of your respective points about Fields. Thanks @isladogs and @Galaxiom for pointing this out.

I can admit when I've been wrong.

 

[isladogs]

Congratulations. You've managed to get Daniel Pineault to write an article based on your columns vs fields comments.

Microsoft Access – Table Fields or Table Columns, Which Is It?

Discussing the usage of the terms Table Fields vs. Table Columns in the contex of an Access database. Which is correct? Which one should be used?

www.devhut.net

 

[Galaxiom]

A great example is Worksheet vs. Workbook. In my opinion, the best approach is to call the whole file a Workbook (always), and to call those little tabs at the bottom Worksheets (always). Microsoft has muddled the picture, and IMO helped confuse newcomers, by doing things like displaying "Microsoft Excel Worksheet" in File Explorer, when labelling the Type of an Excel file.

Yes it definitely should be Workbook. Part of the problem is Excel is broadly known as a "spreadsheet application" with a workbook typically referred to as a "spreadsheet".

It doesn't help that the object model uses both names, Worksheets and Sheets for the same collection.

Yet another inconsistency is that what the Access object model calls fields are called columns just like other databases for DML commands in Access because the JET and ACE database engines use conventional terminology.

ADODB recordsets have "fields" even when returned from SQL Server.

HTML forms have fields but, like Access, they are called Controls in .NET, though many people look at you strangely if you talk about Controls in any context.

Some developers have colloquially referred to Access subformcontrols as a form "container". I remember pulling up one highly experienced developer here (Bob someone IIRC) about misusing the term because the Containers collection is part of the Access object model. He was surprised because he had never heard of it.

It is bit like speaking English in different countries. Best use the right word for the context. "Fanny" has a very different meaning in the US from UK or Australia.

 

[NauticalGent]

Fanny" has a very different meaning in the US from UK or Australia.

I've got a very burlesque "sea-story" from when my ship visited Portsmouth, England. Definitely one for the Water Cooler...