Adding a record or merely filling its nulls?

[Ilan Sebba]

I am not sure if this is the correct newsgroup for this question, but so far
the only thing I understand is table design. So this is my starting point.

What I create a recordset on related tables, what is it exactly that I end
up with? If no recrods are turned up, do I really have no records, or do I
have one record where each field is NULL?

Say I have two parent tables with no relationship between them, and one
child table, with a foreign key to each parent table. I enter one record in
each parent table, but no record in the child table. These are the three
tables:

tblCourse
CourseName [PK]

tblStudent
StudentName [PK]

tblStudentProgress
CourseNameFK [PK1]
StudentNameFK [PK2]
Grade

I enter one course name in the course table ('History') and one name in the
student table ('Martha'). I do not enter any data in the StudentProgress
table. Now I set a recordset equal to the StudentProgress record where
CourseName is 'History', and StudentName is 'Martha'. Because the table is
empty, I get a recordset with no records. That makes sense. But this
recordset is some sort of something. I am not sure what. I notice that it
has a 'Source' property, which is equal to the SQL string which I ran to
create the recordset.

Say I want to give Marth the grade '60' in History. I already have a
recordset. First of all, do I have to add a new record? Perhaps the record
exists but is only full of nulls? I ask this because when I try to Add a
new record to this recordset (CouseNameFK="History", StudentNameFK=
"Martha") I get an error advising me of a primary key violation.
Presumably, I am trying to enter a new course called 'History', as well as a
new student called 'Martha'. Since both of these already exist, I fail.
However, I can add a new record for a new student. That works. My problem
is then this: how could I add a new student called 'David' in a recordset
which has as its 'Source' property an SQL statement which only selects
records where the student name is David.

Bottom line, I don't understand what I am getting when I have a recordset.
This is probably the wrong newsgroup, but I think my starting point is this:
when I create two related tables, what am I doing? Am I creating a
'cartesian' set of records, full of nulls, or am I merely restricting the
type of data which may be entered into different fields. Only once I get a
reply to this, can I start looking in the right direction.

Many thanks for your help.


Ilan

I ask this question because I attempted to add a record in what I thought
was an empty table. I got a message (error -2147217887) stating that my
attempt to add a record would create a duplicate in a primary key. As the
child table was clearly empty, what I must have unwittingly tried to create
a new record in the parent tables. I should add that I am trying to add the
records using ADO, not Access forms.

 

[TC]

"Ilan Sebba" <ilan underscore sebba at btinternet stop com (e-mail address removed)>
wrote in message
(snip)

What I create a recordset on related tables, what is it exactly that I end
up with? If no recrods are turned up, do I really have no records, or do I
have one record where each field is NULL?

dim db as database, rs as recordset
set db = currentdb()
set rs = db.openrecordset ("SELECT * FROM MyQuery WHERE ID=123")

If there are no records with ID=123, the recordset returns *no records*.
This is characterized by RS.BOF and RS.EOF both being true. That is quite
different to a single record with all null fields. It is irrerelevant
whether the recordset is based on table(s) or query(s) (related or
otherwise).

HTH,
TC