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

[Atreju]

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

 

[Martin Walke]

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]

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.

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

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

 

[Martin Walke]

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

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.

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

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

 

[Michael B. Johnson]

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

Yes, it is. And you may base query upon query, though at some point it
complicates maintenance.

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 don't shy away from strong queries in Access because if it's truly a
useful query, then it is re-usable in a report or form. It's also easy
to troubleshoot and change without changing the code. It's easier to
browse the data and demonstrate whether it is correct or not.

<snip again/>
__________________
Michael B. Johnson

 

[Steve Gerrard]

| >
(various snips)

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

| >> 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).
| >> Q: My major concern is: Does this really matter? Adding fields
later
| >> in the table?|| >> 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 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.
|

30 fields is certainly not too many for a table, especially if they are
all used on most records. If it makes sense, it is probably right.

Ditch the field-by-index thing right away. There are plenty of ways to
get tripped up in VB without painting a target on your forehead.

The order of fields in the table should not matter one way or the other.
All your SQL and code should be written such that the field order in the
tables makes no difference.

You should get in the habit of using a Select query that specifies the
fields you want by name, in the field order you want them. While you are
at it, also get in the habit of including an Order By clause to specify
the sort order of the records.

Stored queries in Access are more or less like views in SQL Server.
Using them as the basis of your final queries can have benefits. If they
are run from Access, and then saved again, Access will (supposedly) save
the processed SQL and the query will run faster next time. You can
administer them separately from tables if needed. And if you ever do
split a table, you can rework the stored query, and all your other code
will still run the same.

Suppose you have a stored query called qryMainView, which currently
contains "Select Field5, Field3, Field1 From TableA", and your program
contains a SQL string that reads "Select Field3, Field5 From qryMainView
Order By Field3".

You could now split TableA, and rename the fields while you were at it.
Without touching your code, you could rewrite qryMainView as "Select
FieldE As Field5, FieldC As Field3, FieldA As Field1 From TableA1 Inner
Join TableA2 On TableA1.Key = TableA2.Key". Your program would then
continue to work as is.

Which is why it is good to write code with explicit SQL and field names.

 

[Atreju]

On Tue, 18 Jan 2005 17:01:33 -0000, "Martin Walke"

SNIP

Ok, what about a Form with lots of data-bound textboxes. Will binding
them to a Query update the record just as would binding to a table?

BIG SNIP


---Atreju---

 

[Martin Walke]

Yes - providing the query is an update query. Personally I don't use
data-bound controls, I populate with code. Gives me full control.

Martin