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

[rkc]

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.

Spending some time reading up on relational database design in general
and normalization in particular might also be of some value.

To answer your question, yes it is. How would that help?

[Larry Linson]

"Tim Hunter" <th*****@rochester.rr.comwrote

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?

"Yes," as rkc said, "it is."

"No," I say, "it is almost certainly not what you SHOULD be doing."

From your description, it sounds as if you are "committing spreadsheet" --
wanting to use Access as nothing but a bigger version of Excel, that can
handle more rows and columns. I agree that you need to carefully review what
business functions you need to accomplish, and how you need to structure and
use the data -- which, in a database, will not be tables with so many
columns you bump into Access' limitations.

Where I disagree with rkc (and I think he was being somewhat facetious) is
that I think you might well benefit if you contract with an experienced
Access developer to work with you and from whom you might learn. But, even
before that, check out the recommended self-study books at
http://www.mvps.org/access and other sites.

Some of my favorites are:

"Microsoft Access Step-by-Step" from Microsoft Press for the raw Access
beginner

"Microsoft Access 2003 Inside-Out" by John Viescas, from Microsoft Press, or
"Special Edition Using Microsoft Access <version>" by Roger Jennings, from
Que,
both of which start from the beginning but go farther into development than
the Step-by-Step books

"Microsoft Access <versionDeveloper's Handbook" by Litwin, Getz, et al,
from SYBEX for the intermediate to advanced developer

but there are many books available, and most of them are good -- but not if
they just sit on the shelf at the bookstore.

Larry Linson
Microsoft Access MVP

[Tim Hunter]

Gentleman,
I asked a simple question I didn't expect to be attacked. I have 40 plus
years in development and I have been developing in Access for about 10
years. This doesn't make me an expert by any means and I may not be at
your level, but I do know my way around in Access. Before I refuse a
paying client because something isn't politically correct, I exaust all
possibilities and that is what i am doing at this moment. There are many
ways to skin this cat and using Access and arrays is one idea I thought
might work.

Thank you

Tim Hunter

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

[Gord]

On Jan 25, 4:35 pm, Tim Hunter <[e-mail]:

Gentleman,
I asked a simple question I didn't expect to be attacked. I have 40 plus
years in development and I have been developing in Access for about 10
years. This doesn't make me an expert by any means and I may not be at
your level, but I do know my way around in Access. Before I refuse a
paying client because something isn't politically correct, I exaust all
possibilities and that is what i am doing at this moment. There are many
ways to skin this cat and using Access and arrays is one idea I thought
might work.

Thank you

Tim Hunter

*** Sent via Developersdexhttp://www.developersdex.com***

Does this help?
Sub foo()
Dim ColumnNames As Variant
Dim ColumnValues As Variant
Dim i As Integer
Dim rst As DAO.Recordset

ColumnNames = Array("Column1", "Column2", "Column3")
ColumnValues = Array("foo", "bar", "baz")

Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
rst.AddNew
For i = 0 To 2
rst(ColumnNames(i)).Value = ColumnValues(i)
Next
rst.Update
rst.Close
Set rst = Nothing
End Sub

[Tim Marshall]

Tim Hunter wrote:

>Before I refuse a
paying client because something isn't politically correct, I exaust all
possibilities and that is what i am doing at this moment.

Hi Tim,

I didn't think you weren't attacked.

But the above concerns me... are you saying that if a client requests
something that is not a good idea that you will try to do it anyway? I
would think that one of the purposes of those of us who provide our
expertise in information technology to lay folk would be to advise
against poor choices and do our best to guide them correctly....
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Hunter]

Ahhhh, I've been waiting for someone to ask this question and a good
question it is. Certainly I consider myself an Information Systems
Professional (Semi-Retired) and under that umbrella I want to develop
good industrial strength applications that follow good design concepts
using good coding conventions. Having said that, I also am not a person
to say that something can't be done unless I have a good understanding
of why it either can't or shouldn't. I try to keep an open mind when I
do my research and I always come away with much more knowledge than I
started with. What I end up with for the application that I am working
on now is my decision and the customer will more than likely follow my
recommendations, but I had better know what I am talking about when i
talk to him or I won't be there for long...

Respectfully

Tim Hunter

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

[Tim Hunter]

Thank you very much, this is exactly what I couldn't figure out on my
own. I tested this and it works just fine. Why I love this Web Site is
because being semi-retired I can't walk over to the next cube and ask a
question. You guys are my next cube...

Thank you ver much

Tim Hunter

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

[Lyle Fairfield]

rkc wrote:

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.

Spending some time reading up on relational database design in general
and normalization in particular might also be of some value.

To answer your question, yes it is. How would that help?

What's happening here, rkc? Is this thread some kind of troll or test?
There's gotta be some explanation. My guess is that it's some kind of
bet between you and the original poster where he makes some posts that
are stupider than can be imagined and a few bucks are going to change
hands on the basis of the general tone of the answers.

[David W. Fenton]

Tim Hunter <th*****@rochester.rr.comwrote in
news:45*********************@news.qwest.net:

Thank you very much, this is exactly what I couldn't figure out on
my own. I tested this and it works just fine. Why I love this Web
Site is because being semi-retired I can't walk over to the next
cube and ask a question. You guys are my next cube...

Um, you aren't using a website. You're using a Usenet group.
AccessMonster is just a website that is a gateway to Usenet, a very
poor one, at that.

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

[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*****@rochester.rr.comwrote in
news:45*********************@news.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/