Programming for all fields in a table

[Feico]

Dear friends

I am an experienced programmer, but I happen to have MS Access, which
uses a language unknown to me.

I want to perform an operation on all record on a table, like this

For recordnumber=1 to NumberOfElements(Tabel)
PerformCalculation(recordnumber.fieldname)
How is it done?
--
Feico

[pietlinden]

A query.

I'm sure you could do it by opening a recordset and doing it that way,
but you could also probably do something like push your car to the
store instead of driving it. Possible, maybe, but I sure wouldn't
recommend it.

[DFS]

Feico wrote:

Dear friends

I am an experienced programmer, but I happen to have MS Access, which
uses a language unknown to me.

I want to perform an operation on all record on a table, like this

For recordnumber=1 to NumberOfElements(Tabel)
PerformCalculation(recordnumber.fieldname)
How is it done?

Depends. A combination of queries and recordset processing might work best

dim db as database, rs as recordset, i as integer
set db = currentdb()
set rs = db.openrecordset("SELECT TOP 1 * FROM TABLE;")
for i = 0 to rs.fields.count - 1
db.execute("UPDATE TABLE SET [" & rs(i) & "] = ..... WHERE....;")
next i
rs.close
db.close
msgbox "Finished"

or less efficiently...

dim db as database, rs as recordset, i as integer
set db = currentdb()
set rs = db.openrecordset("SELECT * FROM TABLE;")
do until rs.eof
for i = 0 to rs.fields.count - 1
rs.Edit
rs(i) = PerformCalculation
rs.update
next i
rs.movenext
loop
rs.close
db.close
msgbox "Finished"

[Feico]

On Sun, 16 Oct 2005 20:05:45 -0400, "DFS" <nospam@dfs_.com> wrote in
comp.databases.ms-access:

dim db as database, rs as recordset, i as integer

Unfortunately, db as database is rejected.
--
Feico

[DFS]

Feico wrote:

On Sun, 16 Oct 2005 20:05:45 -0400, "DFS" <nospam@dfs_.com> wrote in
comp.databases.ms-access:

dim db as database, rs as recordset, i as integer

Unfortunately, db as database is rejected.

Go to Tools | References, and uncheck ActiveX Data Objects 2.x library, and
make sure DAO 3.x is checked.

That should do it.

[Allen Browne]

In the VBA code window, choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library
This library is automatically selected in all versions of Access except 2000
and 2002, so presumably you have one of those 2.

Unfortunatley, the ADO library also has a Recordset object, so you need:
dim db as database, rs as DAO.recordset, i as integer

More on references:
http://allenbrowne.com/ser-38.html

BTW, DFS interpreted your question that you wanted to apply the change to
all fields in all records of your table. If you just wanted to change a
particular field in all records, you could just use an Update query, such
as:
dbEngine(0)(0).Execute "UPDATE MyTable SET MyField = (1.1 * MyField);",
dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Feico" <fe***@gmsrem0vethis.net> wrote in message
news:ul********************************@4ax.com...

On Sun, 16 Oct 2005 20:05:45 -0400, "DFS" <nospam@dfs_.com> wrote in
comp.databases.ms-access:

dim db as database, rs as recordset, i as integer

Unfortunately, db as database is rejected.
--
Feico

[Feico]

On Mon, 17 Oct 2005 16:53:20 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote in comp.databases.ms-access:

In the VBA code window, choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library
This library is automatically selected in all versions of Access except 2000
and 2002, so presumably you have one of those 2.

Unfortunatley, the ADO library also has a Recordset object, so you need:
dim db as database, rs as DAO.recordset, i as integer
That helps, thank you (you must be an extremely experience programmer
to remember such things)
BTW, DFS interpreted your question that you wanted to apply the change to
all fields in all records of your table. If you just wanted to change a
particular field in all records, you could just use an Update query, such
as:
dbEngine(0)(0).Execute "UPDATE MyTable SET MyField = (1.1 * MyField);",
dbFailOnError

In fact, I do not want to change the table at all. However, I do (of
course) want to read the contents of the table. For this I need a
syntax like:

recordset [index] . fieldname

What's this syntax in VB?

Sorry for appearing to be a dunce. I am actually an experienced
programmer (Algol, Fortran, Assembly, Cobol, C++, APL) but I used
Basic only on the Apple II, and I hated the language. But it appears
that Access has nothing else to offer.

--
Feico

[Tim Marshall]

Feico wrote:

On Sun, 16 Oct 2005 20:05:45 -0400, "DFS" <nospam@dfs_.com> wrote in
comp.databases.ms-access:

dim db as database, rs as recordset, i as integer

Unfortunately, db as database is rejected.

DFS is using DAO methods in his example. It sounds, then as if you are
using Access 2000? There are two things you need to do (I haven't check
DFS's code, I'm assuming what he's given you is sound).

First thing.

Access 2000 does not have the DAO library referenced by default, so do
the following:

1) Open a form or standard module.

2) Click on the menu item Tools->References. A "References" window will
appear.

3) Find Microsoft DAO 3.6 Object library and make sure it is ticked.

4) Click the OK button.

Your application now can reference the DAO library and use DAO methods.

Second Thing.

You will now have two libraries referenced, both of which have some
common terms. You must now make sure your variables that are dimmed
reference the correct library.

So change:

dim db as database, rs as recordset, i as integer

to

dim db as DAO.database, rs as DAO.recordset, i as integer

And DFS's code should run properly.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Allen Browne]

Do you want to:
a) change the value of all fields, in all records (every cell in the entire
table) or
b) change the value of one field, in every record (just one column of the
table)?

If a), execute the update query. Looping through the recordset will be less
efficient to write and execute.

If you want to loop thorugh all records anyway, the syntax would be like
this (assuming all fields are numeric, and you want to change them to 9999):

Function AdjustMyTable()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field

Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable")

Do While Not rs.EOF
rs.Edit
For Each fld In rs.Fields
fld = 9999
Next
rs.Update
rs.MoveNext
Loop
rs.Close

set fld = Nothing
Set rs = Nothing
Set db = Nothing
End Function

For a quick intro to the objects in the DAO library, see:
http://allenbrowne.com/ser-04.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Feico" <fe***@gmsrem0vethis.net> wrote in message
news:6f********************************@4ax.com...

On Mon, 17 Oct 2005 16:53:20 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote in comp.databases.ms-access:

In the VBA code window, choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library
This library is automatically selected in all versions of Access except
2000
and 2002, so presumably you have one of those 2.

Unfortunatley, the ADO library also has a Recordset object, so you need:
dim db as database, rs as DAO.recordset, i as integer

That helps, thank you (you must be an extremely experience programmer
to remember such things)

BTW, DFS interpreted your question that you wanted to apply the change to
all fields in all records of your table. If you just wanted to change a
particular field in all records, you could just use an Update query, such
as:
dbEngine(0)(0).Execute "UPDATE MyTable SET MyField = (1.1 *
MyField);",
dbFailOnError

In fact, I do not want to change the table at all. However, I do (of
course) want to read the contents of the table. For this I need a
syntax like:

recordset [index] . fieldname

What's this syntax in VB?

Sorry for appearing to be a dunce. I am actually an experienced
programmer (Algol, Fortran, Assembly, Cobol, C++, APL) but I used
Basic only on the Apple II, and I hated the language. But it appears
that Access has nothing else to offer.

--
Feico

[Feico]

On Mon, 17 Oct 2005 09:13:48 -0230, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote in
comp.databases.ms-access:

DFS is using DAO methods in his example. It sounds, then as if you are
using Access 2000? There are two things you need to do (I haven't check
DFS's code, I'm assuming what he's given you is sound).
Yes, I'm using Acess 2000
Access 2000 does not have the DAO library referenced by default, so do
the following:

You'd expect a system to be usable by default. Well, C needs all kinds
of enigmatic headers before a program can be compiled.
--
Feico

[Tim Marshall]

Feico wrote:

Access 2000 does not have the DAO library referenced by default, so do
the following:

You'd expect a system to be usable by default. Well, C needs all kinds
of enigmatic headers before a program can be compiled.

I agree, but when A2000 was introduced, a new technology, ADO was
introduced and I believe the intent was that it would supercede DAO. It
didn't. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[DFS]

Tim Marshall wrote:

Feico wrote:

Access 2000 does not have the DAO library referenced by default, so
do the following:

You'd expect a system to be usable by default. Well, C needs all
kinds of enigmatic headers before a program can be compiled.

I agree, but when A2000 was introduced, a new technology, ADO was
introduced and I believe the intent was that it would supercede DAO.
It didn't. 8)

When you say ADO didn't supercede DAO, by what measure didn't it?

[Tim Marshall]

DFS wrote:

When you say ADO didn't supercede DAO, by what measure didn't it?

It didn't completely replace it - Microsoft now states, at least it's
Access tech support does, that DAO is optimized for Jet.

Indeed, the MS acknowledgement that this is so is the A2003 (I'm not
sure about 2002, I went from A97 to 2003) reference to the DAO library
as default.

As I've mentioned here a couple of times in other threads, I was really
disappointed that ADO did not expand to be able to be used in reports.
Had it been able to provide recordsets for report recordsources like it
is able to do, for example, in form combo/list boxes and recordsources,
I would have dumped DAO myself.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Albert D. Kallal]

>Algol, Fortran, Assembly, Cobol, C++, APL

Interesting, all very old languages, and their approach to data managing is
very different.

In fact, the code approach in today's systems is also very different. There
is TWO major changes in the code
approach from those above mentioned systems:

#1) - event driven programming

In those above old systems, you tended to write a "main" code routine, and
then that "main" routine would branch out (call subroutines etc) to other
parts of the application. Today, we don't have this traditional concept
anymore, and there is no "main" routine. Event driven programming means that
we have a WHOLE BUNCH of MUCH smaller routines, and those routines get
called when a event occurs (in most cases, this event is a button click, but
there are "many" events that occur. These events are a result of USER
actions). So, in a old system, you might prompt the user for some text, and
then do something. In the new approach, you might have 10 text boxes on a
screen, and our code ONLY runs in what is called the "after update" event of
each text box. So, in the old approach, you run code to prompt the user. In
the new approach, code runs in RESPONSE TO a event. The reason for this
change is the graphical user interface. We can't run code form a large
"main" routine, since with a mouse, we don't really know what the user wants
to do next! So, now most code is no longer called from some main routine, or
main menu code module. Most code now runs in RESPONSE to a event. This is,
and was a major mind set change from the older programming environments you
mention. So, you wind up with many smaller pieces of code. And, code runs in
response to user actions, not the other way around where your code asks the
user for input.

#2) - working with objects

Modem systems are so much more complex, then as developers you are forced to
work with objects. A good example in other responses in this thread is the
recordset object. This reocrdset "object" is a thing that lets you work with
table data. And, you can see by the responses, we even got a choice of which
object we use (ADO, or DAO). These objects are what unlocks the power of
modern systems. And, we can even create our own. You can read about that
here:

http://www.members.shaw.ca/AlbertKal.../WhyClass.html

So, at the end of the day, you will find that working with data means that
old concepts like sequential reading of a file, and even arrays are rarely
used. While a array was likely the most important data type in FORTRAN,
today, we RARELY need arrays in code since we got collection objects.

For the most part, you can, and should use sol to update the table
information. This means you do NOT have to write looping code.

dim strSql as string

strSql = "update tblCustomers set City = 'New York' where city = 'N.Y.' "

currentdb.Execute strSql

You can see in the above, that I did no have to "loop" though the data
tables as we got a "sql" engine to do the work for us.

So, in the above example, we are replacing all occurrences of a city field
that is 'N.Y.' to the correct text of New York.

Not only is the above less code, but it also allows the "engine" to do the
work, and if that engine happens to be oracle, or sql server, then all the
better (thus, the concept here is that the data processing part of code
should be sql when you can use it. And, this also is for reasons of "client"
to server. SQL means we have a split between our code, and the "system" that
can update data.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Feico]

On Mon, 17 Oct 2005 16:51:05 GMT, "Albert D. Kallal" <ka****@msn.com>
wrote in comp.databases.ms-access:

Algol, Fortran, Assembly, Cobol, C++, APL

Interesting, all very old languages, and their approach to data managing is
very different.

Except C++, which is the only language of these which I still
frequently use.

Basic is a lot older.
--
Feico

[Tim Marshall]

Feico wrote:

Interesting, all very old languages, and their approach to data managing is
very different.

Except C++, which is the only language of these which I still
frequently use.

Basic is a lot older.

That's true, but how different from BASIC is VBA - Visual Basic (for
applications)? With the very little I remember from playing with
GW-BAsic and the old Gorilla and his exploding bananas, it's vastly
different, isn't it?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:dj**********@coranto.ucs.mun.ca:

Feico wrote:

Interesting, all very old languages, and their approach to data
managing is very different.

Except C++, which is the only language of these which I still
frequently use.

Basic is a lot older.

That's true, but how different from BASIC is VBA - Visual Basic
(for applications)? With the very little I remember from playing
with GW-BAsic and the old Gorilla and his exploding bananas, it's
vastly different, isn't it?

So far as I'm concerned, VB and BASIC are not the same languages at
all. I took a class in programming BASIC in college and nothing
specific from the class helped me at all when I started working with
Access. Yes, general concepts like data vs. program, logical
structures like IF and so forth were useful to me, but otherwise,
there's absolutely nothing signficant in common.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc