Is it possible to store table field names in array then use to update table

[Tim Hunter]

Hi I am using WinXP and Access 2003
Is it possible to store the field names of a table in an array and then
loop through the array and update the table using the field names stored
in the array? I can't figure out the coding to accomplish this.
I have an Excel application that is a monster and it has become too much
to maintain and test. I didn't write it but i support it. I am trying to
convert this application to Access and it is not as easy as it sounds.
Lots of field names and lots of tables. I have actually exceeded the
maximum number of field names in a table causing me to breakdown the
data and create more tables...

TIS

Tim Hunter

*** Sent via Developersdex http://www.developersdex.com ***

[David W. Fenton]

rkc <rk*@rochester. yabba.dabba.do. rr.bombwrote in
news:45******** *************** @roadrunner.com :

Tim Hunter wrote:

>Hi I am using WinXP and Access 2003
Is it possible to store the field names of a table in an array
and then loop through the array and update the table using the
field names stored in the array? I can't figure out the coding to
accomplish this. I have an Excel application that is a monster
and it has become too much to maintain and test. I didn't write
it but i support it. I am trying to convert this application to
Access and it is not as easy as it sounds. Lots of field names
and lots of tables. I have actually exceeded the maximum number
of field names in a table causing me to breakdown the data and
create more tables...

Come on now, creating an Access application is as simple as
selecting one of the many pre-made templates and entering or
tranfering your data. Anyone can point and click their way to
success without ever having to consult with a ridiculously highly
paid database developer. Some of them already have enough money as
it is any way.

I think you should have put <sarcasm></sarcasmtags around that, as
I assume you were being sarcastic.

I think Access makes it quite easy to create tables and queries and
forms and reports and macros. But I don't think it makes it easy to
do any of those things properly or well. That requires knowledge and
experience, and that's why I read your quoted paragraph as being
sarcastic.

My experience is that most novices don't have the background in data
schema design and that leads them to most of their problems. As
Larry said, this looks like a case where a spreadsheet-type design
is being used, and that's one of the most common causes of major
problems in designing a database application, i.e., a non-normalized
design.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Tim Hunter <th*****@roches ter.rr.comwrote in
news:45******** *************@n ews.qwest.net:

Is it possible to store the field names of a table in an array and
then loop through the array and update the table using the field
names stored in the array? I can't figure out the coding to
accomplish this. I have an Excel application that is a monster and
it has become too much to maintain and test. I didn't write it but
i support it. I am trying to convert this application to Access
and it is not as easy as it sounds. Lots of field names and lots
of tables. I have actually exceeded the maximum number of field
names in a table causing me to breakdown the data and create more
tables...

Two things:

1. you clearly have schema problems. If you'll post a description of
what you have I'm sure we can all help with making a better design,
which will likely involve breaking down that single spreadsheet into
a number of related tables. There's even a wizard to analyze your
data and do it for you, though I can't say I've ever found the
results helpful in getting to a final design (it usually misses
something important, and undoing that is harder than just doing it
manually in the first place).

2. walking through a bunch of records one-by-one and making changes
to them one-by-one is a procedural approach to data editing, and is
very inefficient. While doing so is necessary in a very few cases
where you need to make changes based on the context within a set of
records (i.e., the values you need in one record depend on the
values that are in other records in the same data set), in most
situations you should use SQL UPDATE queries to make the changes
instead. These will be far, far faster than changing the same
records sequentially. If you want to get into databases, you need to
learn SQL, so I would suggest that instead of just using the
sequential code that someone posted for you, try accomplishing the
task with an UPDATE query. The Access query designer is one of the
best tools for learning how to do this, as it makes it pretty darned
easy to create such queries.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/