Re: Strategy question about expanding existing Access Table with VB Application ADO Code

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Martin Walke (martin.walke_NOSPAM_at_NO_SPAMvega.co.uk)
Date: 01/18/05 

Date: Tue, 18 Jan 2005 17:01:33 -0000

> And I have been meaning to start forcing myself to reference fields by
> name, darnit, I just used to do by index long ago by habit and it is
> hard to break.

Yep - ones of those things that once you make the effort you wonder why you
haven't done it from day one!! ;-) It's true that it's easier to simply pop
the number in, particularly if you're doing a loop to populate the form etc.
Having said all this, I still use field indexes but based on the query field
list *not* the original table field list. But then I also read off the field
name as well and use that to populate labels identifying the fields on the
form.

> - In VB, how would I load from an Access stored Query instead of a
> table? Just choose SELECT FROM queryname instead of tablename?
> That simple?

Amazing isn't it? Yep - it's that simple. When using Access queries you use
them in exactly the same way as you would the table name!

> I usually shy away from storing Queries in Access, I don't know if
> this is a common attitude, but I seldom want to use the same query in
> too many places and if I do, it can be done in the SQL statement just
> as well

I hear what you're saying and it's true - but if you simply create a query
that re-orders the field list then it *is* the same as using a SQL
statement.

Martin

"Atreju" <someone@who.hates.junkmail> wrote in message
news:dtcqu05r99g0vo1hpaaav7ifunvkln5pks@4ax.com...
> Very helpful and insightful, thanks!
> And I have been meaning to start forcing myself to reference fields by
> name, darnit, I just used to do by index long ago by habit and it is
> hard to break. I don't know why I even used to do that! I think
> because it was a time when I knew barely anything and didn't even know
> you could do by name. Or perhaps I used to use csv files and make
> arrays. I don't know for sure. But you're right, it is smart.
>
> - In VB, how would I load from an Access stored Query instead of a
> table? Just choose SELECT FROM queryname instead of tablename?
> That simple?
>
> I usually shy away from storing Queries in Access, I don't know if
> this is a common attitude, but I seldom want to use the same query in
> too many places and if I do, it can be done in the SQL statement just
> as well.
>
> Any further thoughts welcome.
> Thanks again.
>
> On Tue, 18 Jan 2005 14:26:56 -0000, "Martin Walke"
> <martin.walke_NOSPAM@NO_SPAMvega.co.uk> wrote:
>
> >Hi Dan,
> >
> >Having read through your 'thoughts', most of them make sense and you've
> >almost realised to avoid the trap of using field indexes rather than
field
> >names. Field names allow you to add and/or insert new fields into your
> >tables without upsetting any code that may be accessing them.
> >
> >In terms of the number of fields in a row, obviously there is a
performance
> >issue but not one that you will probably notice unless the number of
fields
> >becomes too big. Although you say the fields are difficult to split, I
would
> >have thought it doesn't matter. If you think it will become an issue then
> >split the table (if you want to) and use a link relationship to keep the
two
> >records in each table together.
> >
> >Finally, and this covers your last Q about order, obviously you can
either
> >specify each field in the select statement ;-( [select field1, field4,
> >field2, field3 from tabel1....] or create a query with the fields in the
> >correct order and use this query to access your data. The latter approach
is
> >one that I've used numerous times because there's always a client who
wants
> >to record just that one more piece of data that should have been added at
> >the beginning. Oh.... and in addition, using queries not only allows you
to
> >reference the split table as one but also allows you to use indexes as
field
> >references!!! Talk about going full circle!!
> >
> >HTH
> >Martin
> >
> >"Atreju" <someone@who.hates.junkmail> wrote in message
> >news:uc1qu0tbod2v4m6rrhh1cjp8gongh6sfnb@4ax.com...
> >> Sorry for the very wide cross-post, if anyone requests, I can pull the
> >> post from any given group. I just think this is a Q for all of these
> >> groups! Also, I apologize for the length of this post, but I really
> >> have a few dilemmas on my mind, and I'd greatly appreciate anyone who
> >> has the patience to sift through it all. I hope I organized my
> >> thoughts well.
> >>
> >> I have several questions amongst explanations, so I will put a Q: by a
> >> question. Thanks.
> >>
> >> I have a VB Application that is an ongoing project.
> >> One sticky situation is that I have a production version and a working
> >> version. The working version uses my live data which I actually need
> >> (aghast! a Developer using his own product before it is done! :0).
> >> Therefore I really want to get this refined in the production version
> >> before I change the database structure of the working version. The VB
> >> front-end I can do whatever with but these questions are mostly about
> >> the database.
> >>
> >> I have one main table. As of today it had 21 fields. I am adding a few
> >> (so far it is up to 27 and I anticipate approximately 3 or 4 more. No,
> >> there's really no relevant way to split it up - it is all part of a
> >> single transaction record which just has lots of information).
> >>
> >> My problems are thus:
> >>
> >> First bad situation is in the front-end, I am using data-bound
> >> controls (horrors! I will try to change this in the future). I LIKE to
> >> keep my controls in tandem with my fields - eg: TextBox(6) is
> >> adoRecordset.Fields(6) - but somehow I feel this is a futile effort,
> >> because one can never be certain of the table structure in the future.
> >>
> >> I have added fields to the Table which are more closely related (not
> >> in the database term "related" but more in the characteristic of the
> >> data in that field) to fields that already exist higher up in the
> >> table (lower field index). For example: Name,Address1,City,etc. or
> >> ListDate,ListTime,ListPrice etc.
> >>
> >> Q: My major concern is: Does this really matter? Adding fields later
> >> in the table? I actually re-ordered them once before when I added a
> >> field but doing so again would mean a ton of Search&Replaces in the VB
> >> code.
> >>
> >> On some Forms, I have, in the past, taken a shortcut once in a while
> >> and tested the contents of a field by referring to the
> >> Recordset.Fields(index) where index was the index of a particular
> >> control. I did this because I knew they were matched. But I realize
> >> this is _very_ bad practice, so I may as well get used to NOT doing
> >> that.
> >>
> >> Q: In one form, I have a HFlexGrid. In this form, I wonder if I use
> >> "SELECT * FROM Table" - is there, then, any way to re-order the
> >> columns, or do I have to "SELECT Field1, Field5,Field2,Field9,etc." in
> >> the order I want them? How is the best way to approach this situation?
> >>
> >> I REALLY appreciate greatly any advice anyone can give me. I know it
> >> sounds like I'm practically asking for Database 102 lessons here, but
> >> my knowledge is growing and I just need a few boosts once in a while.
> >>
> >> Please tell me if there is anything I've asked that's not too clear,
> >> or if you need more info.
> >>
> >> I thank you VERY much for any help anyone can give.
> >>
> >> -Dan
> >>
> >>
> >> ---Atreju---
> >
>
>
>
> ---Atreju---

Relevant Pages