What's going on????????

  • Thread starter Thread starter Bill Stanton
  • Start date Start date
B

Bill Stanton

Below is a simple routine that reads a table (Families) one
record at a time, extracts the family id from the key field and
proceeds to then generate 29 entries in a donations table for
that family. There are about 240 families, thus the resulting
donations table should have 29 x 240 = 6960 records when the
routine finishes. I watched the progress in the "Immediate
Window" via the Debug.Print and all appeared as expected.
However, the resulting donations table only has 29 records that
bares the ID of the first family, which happens to be one (1).
The results gives the appearance that the "For" loop only
executed once. That certainly isn't the case, as I've also observed
the Debug.Print placed "inside" the innermost loop.

What am I missing here?

Thanks,
Bill

Option Compare Database
Option Explicit
Dim con As ADODB.Connection
Dim rsFamilies As ADODB.Recordset
Private Sub MakeDonations()

Dim tmpSQL As String
Dim I As Integer
Dim DVal As Long
Dim FamID As Integer

Set con = Application.CurrentProject.Connection
tmpSQL = "SELECT [FamilyID] FROM [Families]"
Set rsFamilies = New ADODB.Recordset
rsFamilies.Open tmpSQL, con, adOpenKeyset, adLockOptimistic

While (Not (rsFamilies.EOF))
FamID = rsFamilies!FamilyID
DVal = 37815
For I = 1 To 29

tmpSQL = "INSERT INTO [Donations](FamilyID,DateVal)"
tmpSQL = tmpSQL & " VALUES(" & FamID & ", " & DVal & ");"

CurrentDb.Execute tmpSQL
DVal = DVal - 7
Next

Debug.Print tmpSQL

rsFamilies.MoveNext
Wend

rsFamilies.Close
Set rsFamilies = Nothing
End Sub
 
Bill said:
Below is a simple routine that reads a table (Families) one
record at a time, extracts the family id from the key field and
proceeds to then generate 29 entries in a donations table for
that family. There are about 240 families, thus the resulting
donations table should have 29 x 240 = 6960 records when the
routine finishes. I watched the progress in the "Immediate
Window" via the Debug.Print and all appeared as expected.
However, the resulting donations table only has 29 records that
bares the ID of the first family, which happens to be one (1).
The results gives the appearance that the "For" loop only
executed once. That certainly isn't the case, as I've also observed
the Debug.Print placed "inside" the innermost loop.

What am I missing here?
Click to expand...

What's the primary key of the Donations table?

It's almost always a good idea to use dbFailOnError so you
can tell if anything goes wrong.
 
I've left the donations table without a primary key at
the moment. Uniqueness stems from a combination
of the FamilyID and DVal taken as an ordered pair,
so I thought in that case it was better to leave the table
without a primary key.

I'll add the dbFailOnError and run the routine again.

Bill
 
Well, I added a primary key field using AutoNumber and
deleted the one-to-many relationship between Families and
donations. Then, I ran the routine a couple of times to observe
the changing values of autonumber. The number ran up exactly
the number of records that should have been written with
each iteration of the routine, but still only records
corresponding to a single family appear in the table.
I give up!!!!!! Any wisdom out there??????
Bill
 
Current.Execute is DAO, so you do need a reference to:

DAO 3.6 Object Library to be set.

You can also use the value of 128 in place of dbFailOnError,

So,

CurretnDB.Excute tempSQL, 128

Should also work.

I am late in this thread. A few things to check:

Are any of the indexes used in the target table set to NOT allow duplicates?
(if yes, then any duplicate entry will be ignored). Often, this is great way
to prevent duplications during a import, or table merge. However, in your
case it is likely you have a simple index set to not allow duplicates.

If you DAO references are messed up, then try:

docmd.RunSql tmpSQL

The above will give you any warnings if you can't get the CurrentDB.Execute
to work.
 
Bill said:
Well, I added a primary key field using AutoNumber and
deleted the one-to-many relationship between Families and
donations. Then, I ran the routine a couple of times to observe
the changing values of autonumber. The number ran up exactly
the number of records that should have been written with
each iteration of the routine, but still only records
corresponding to a single family appear in the table.
Click to expand...

Albert has already mentioned the reason (unique index) why I
think the records are not being added.
 
"Bill Stanton" <[email protected]
wrote in message Albert,
You were correct in your suspicions, the DVal "no duplicates"
property was set to YES.

As an aside, where can I find some education regarding the
principle difference between DAO and ADO; where to use
one versus the other, etc. I'm new enough to VBA that I
don't have a clue as why one should be used over the other
or why there are two choices in the first place.

Thanks,
Bill
Click to expand...

You are most welcome. As for asking about DAO and ADO?

Oh, boy! That is a REAL big can of worms to open. Almost like asking between
the Democrats and the Republican party!

I will give you a quick rundown. You code in DAO could have been written as:

Dim rsFamilies As DAO.Recordset

tmpSQL = "SELECT [FamilyID] FROM [Families]"
Set rsFamilies = CurrentDb.OpenRecordset(tmpSQL)

You can see how DAO tends to take a "bit" less code to set-up.

DAO is quite old now. It is the old tried and true technology that came with
the JET engine. (the JET engine is the database engine included with
ms-access).

So, in effect, the JET database engine is really integral very tightly with
DAO.

DAO (Data Access Object) is thus usually preferred means when working with a
jet database.

Of course, you can also use ms-access as a front end to something like
Oracle, or sql server. Thus, the JET engine supports ODBC connections to
most database servers. However, that means you still use DAO, but have to
still
go through the JET engine to grab your data.

Often, JET gets in the way of that goal to grab data. I mean, JET is a whole
database engine. JEt can interprets sql. JET can create and use indexes. JET
has to enforce referential integrity. However, when you start using
something like sql-server or oracle, then "JET" just becomes extra baggage.
I mean, you don't really need JET anymore since indexing, and the sql stuff
occurs on the server side now.

So, the folks at MS thought it would be a good idea to offer a new
technology that allows you to NOT need jet. This new "lighter" technology
would enable you to connect to a wide variety of systems, and would also be
smaller then JET. The new assumption was that now everyone was going to use
a server
based system.

The machine at the other end would do all the processing.

That new technology is ADO (ActiveX Data Object).

The real advantage of ADO is that you can pick this one technology, and then
"choose" the back end system you use.

Now, it turns out that ADO can also connect to JET. So, ADO is one layer up.

If you learn and use ADO, then that should be ALL you need to learn. If you
plan to use sql server, or any new system, then again ADO is *supposed* to
be the ticket here.

However, ADO is somewhat less feature rich then is DAO. Further, for
managing
security stuff (workgroups), then again DAO is better (it is the native JET
data object).

The general consensus is thus when using JET, you might as well stick with
DAO.

There is also a general feeling that MS is trying to "lead" everyone away
from DAO. Of course, many of us like DAO.

Of course, we now have the new .net, and another data object was thrown out
at
us.

So, now, we are being told that ADO is not the next new thing either!

The main advantage of ADO is that for Web server stuff, and using sql
server, it is a better choice.

Many here state that if you use JET, then you *should* use DAO. However, it
is not so bad to use ADO, and if you ever plan to NOT use JET, the you will
not have to "learn" anything new.
 
Albert,
Thanks for your ADO/DAO comments. I'll study them and
give the whole concept/notion a chance to sink in.
Thanks again,
Bill
Albert D. Kallal said:
"Bill Stanton" <[email protected]
wrote in message Albert,
You were correct in your suspicions, the DVal "no duplicates"
property was set to YES.

As an aside, where can I find some education regarding the
principle difference between DAO and ADO; where to use
one versus the other, etc. I'm new enough to VBA that I
don't have a clue as why one should be used over the other
or why there are two choices in the first place.

Thanks,
Bill
Click to expand...

You are most welcome. As for asking about DAO and ADO?

Oh, boy! That is a REAL big can of worms to open. Almost like asking between
the Democrats and the Republican party!

I will give you a quick rundown. You code in DAO could have been written as:

Dim rsFamilies As DAO.Recordset

tmpSQL = "SELECT [FamilyID] FROM [Families]"
Set rsFamilies = CurrentDb.OpenRecordset(tmpSQL)

You can see how DAO tends to take a "bit" less code to set-up.

DAO is quite old now. It is the old tried and true technology that came with
the JET engine. (the JET engine is the database engine included with
ms-access).

So, in effect, the JET database engine is really integral very tightly with
DAO.

DAO (Data Access Object) is thus usually preferred means when working with a
jet database.

Of course, you can also use ms-access as a front end to something like
Oracle, or sql server. Thus, the JET engine supports ODBC connections to
most database servers. However, that means you still use DAO, but have to
still
go through the JET engine to grab your data.

Often, JET gets in the way of that goal to grab data. I mean, JET is a whole
database engine. JEt can interprets sql. JET can create and use indexes. JET
has to enforce referential integrity. However, when you start using
something like sql-server or oracle, then "JET" just becomes extra baggage.
I mean, you don't really need JET anymore since indexing, and the sql stuff
occurs on the server side now.

So, the folks at MS thought it would be a good idea to offer a new
technology that allows you to NOT need jet. This new "lighter" technology
would enable you to connect to a wide variety of systems, and would also be
smaller then JET. The new assumption was that now everyone was going to use
a server
based system.

The machine at the other end would do all the processing.

That new technology is ADO (ActiveX Data Object).

The real advantage of ADO is that you can pick this one technology, and then
"choose" the back end system you use.

Now, it turns out that ADO can also connect to JET. So, ADO is one layer up.

If you learn and use ADO, then that should be ALL you need to learn. If you
plan to use sql server, or any new system, then again ADO is *supposed* to
be the ticket here.

However, ADO is somewhat less feature rich then is DAO. Further, for
managing
security stuff (workgroups), then again DAO is better (it is the native JET
data object).

The general consensus is thus when using JET, you might as well stick with
DAO.

There is also a general feeling that MS is trying to "lead" everyone away
from DAO. Of course, many of us like DAO.

Of course, we now have the new .net, and another data object was thrown out
at
us.

So, now, we are being told that ADO is not the next new thing either!

The main advantage of ADO is that for Web server stuff, and using sql
server, it is a better choice.

Many here state that if you use JET, then you *should* use DAO. However, it
is not so bad to use ADO, and if you ever plan to NOT use JET, the you will
not have to "learn" anything new.


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn
Click to expand...
 
Back
Top