By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 1,904 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

Creating a record in a child table using ADO

P: n/a
When it comes to adding records in related tables, Access is really smart.
But when I try to do the same using ADO, I am really stupid.

Say I have two parent tables (eg Course, Student) and one child table
(StudentProgress). The course progress records how a student progresses on
a course. I have one course (History) and one student called Maya. I now
want to record her grade (64). If I do this in Access using a form, then
the form will magically display her progress record.

If however I do so in VBA, then I must first distinguish between two cases:
(1) A record in StudentProgress does exist for Maya, in which case I have to
modify the grade to 64.
(2) No record exists for Maya in StudentProgress, so I have to create it.

Unfortunately, when I try to create the record, I am told that I will create
duplicate data in an indexed or primary field. I think that what I am
accidentally trying to do is create a new record in student, and a new
record in course, as opposed to a new record in StudentProgress.

Here are my tables, and the code

tblCourse
Subject [PK]

tblStudent
Name [PK]

tblStudentProgress
CourseFK [PK1]
StudentFK [PK2]
Grade
Here is the code which is sopposed to do the following:
(1) tries to see if a record for course progress already exists for Maya in
History
(2) if not, tries to create it

Private Sub TestAddingRecordInChildTable()
Dim rcsCourseProgress As ADODB.Recordset
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strSQL As String

strSelect = "SELECT tblCourse.Subject, tblStudent.Name,
tblStudentProgress.Grade "
strFrom = " FROM tblStudent INNER JOIN (tblCourse INNER JOIN
tblStudentProgress ON tblCourse.Subject = tblStudentProgress.CourseFK) ON
tblStudent.Name = tblStudentProgress.StudentFK"
strWhere = " WHERE tblStudent.Name = 'Maya' AND
tblCourse.Subject='History'"

strSQL = strSelect & strFrom & strWhere

Set rcsCourseProgress = RunSQL(strSQL) 'RunSQL is my own procedure
which works OK.

If rcsCourseProgress.RecordCount = 0 Then
'no record exists for Maya in History.
rcsCourseProgress.AddNew
rcsCourseProgress.Fields("Name") = "Maya"
rcsCourseProgress.Fields("Subject") = "History"
rcsCourseProgress.Fields("Grade") = 64
rcsCourseProgress.Update
End If

End Sub

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
I get a feeling that you are returning a non-updateable ADO recordset
from your RunSQL procedure. Also, what is the point of the joins? It
seems that the process should be as simple as the following 2 steps:
1. Check if there is a record for "Maya" and "History" in tblStudentProgress.
2. Append or modify that record in tblStudentProgress.

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordset, CourseID as String, StudentID as String
CourseID = "History"
StudentID = "Maya"
Rst.Open "SELECT* FROM tblStudentProgress WHERE CourseFK = '" & CourseID
& "' AND StudentFK = '" & StudentID & "'", CurrentProject.Connection
IF Rst.RecordCount = 0 then
RunSQL "INSERT INTO tblStudentProgress VALUES ('History', 'Maya', 64)"
Else
RunSQL "UPDATE tblStudentProgress SET Grade = 64 WHERE CourseFK =
'History' AND StudentFK = 'Maya'"
endif
Rst.Close
Set Rst = Nothing

This is air code - please debug.
Cheers,
Pavel

Ilan Sebba wrote:

When it comes to adding records in related tables, Access is really smart.
But when I try to do the same using ADO, I am really stupid.

Say I have two parent tables (eg Course, Student) and one child table
(StudentProgress). The course progress records how a student progresses on
a course. I have one course (History) and one student called Maya. I now
want to record her grade (64). If I do this in Access using a form, then
the form will magically display her progress record.

If however I do so in VBA, then I must first distinguish between two cases:
(1) A record in StudentProgress does exist for Maya, in which case I have to
modify the grade to 64.
(2) No record exists for Maya in StudentProgress, so I have to create it.

Unfortunately, when I try to create the record, I am told that I will create
duplicate data in an indexed or primary field. I think that what I am
accidentally trying to do is create a new record in student, and a new
record in course, as opposed to a new record in StudentProgress.

Here are my tables, and the code

tblCourse
Subject [PK]

tblStudent
Name [PK]

tblStudentProgress
CourseFK [PK1]
StudentFK [PK2]
Grade

Here is the code which is sopposed to do the following:
(1) tries to see if a record for course progress already exists for Maya in
History
(2) if not, tries to create it

Private Sub TestAddingRecordInChildTable()
Dim rcsCourseProgress As ADODB.Recordset
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strSQL As String

strSelect = "SELECT tblCourse.Subject, tblStudent.Name,
tblStudentProgress.Grade "
strFrom = " FROM tblStudent INNER JOIN (tblCourse INNER JOIN
tblStudentProgress ON tblCourse.Subject = tblStudentProgress.CourseFK) ON
tblStudent.Name = tblStudentProgress.StudentFK"
strWhere = " WHERE tblStudent.Name = 'Maya' AND
tblCourse.Subject='History'"

strSQL = strSelect & strFrom & strWhere

Set rcsCourseProgress = RunSQL(strSQL) 'RunSQL is my own procedure
which works OK.

If rcsCourseProgress.RecordCount = 0 Then
'no record exists for Maya in History.
rcsCourseProgress.AddNew
rcsCourseProgress.Fields("Name") = "Maya"
rcsCourseProgress.Fields("Subject") = "History"
rcsCourseProgress.Fields("Grade") = 64
rcsCourseProgress.Update
End If

End Sub

Nov 12 '05 #2

P: n/a
Wow, I think you are finally helping me solve a far more serious problem I
am having.

You write "Also, what is the point of the joins." Allow me to take you up
on this.

At first I thought 'Idiot me - why have the joins' but then I realised why I
do have them. The tables I offered in my original posting where a
simplification, perhaps an over simplification. In my true database, the
tables are more complex. I think you were able to suggest that I 'do away'
with the joins because the primary key of Course Progress was a combination
of two 'meaningful' (ie not autonumbers). But what if the compound primary
key is a combination of two foreigh keys, each of which being an autonumber
in their original table? Allow me to illustrate:

tblCourse
University[PK1]
CourseName [PK2]
Trimester [PK3]
Name (eg 'history')
ID [autonumber]

tblStudent
CountryOfOrigin [PK1]
ID_NumberInCountryOfOrigin [PK2]
Name
ID [autonumber]

tblCourseProgress
StudentID
CourseID
Grade

Now, when I am writing the VB code, I have a pretty good idea of what course
I am dealing with, and which student I am dealing with, but as far as these
autonumbers go, the serve one purpose: to facilitate relationships. I have
no idea what these numbers may be.

In your original post you wrote "Check if there is a record for "Maya" and
"History" in tblStudentProgress". This is now much more complicated to do.
If my understanding is correct I can do one of the following two:

1. Run two queries, on on the tblStudent, and one on tblCourse, to extract
the autonumber ID's of 'Maya' and 'History', and then procede to do a search
as you suggested, (only using Maya's ID and History's ID rather than their
names), or

2. Use the joins.

My view is this: don't use autonumber ID's. Use really big composite keys
so that in this example the composite key for tblCoursePropgress would be
this:

tlbCouseProgress
University[PK1]
CourseName [PK2]
Trimester [PK3]
StudentCountryOfOrigin [PK4]
StudentID_NumberInCountryOfOrigin [PK5]
Grade

If I am to find "Maya"'s ID and "History"'s ID, then I have to go through
all 5 primary keys anyway. And if I have to go through all 5 primary keys,
then extracting the ID number becomes an unecessary complication as I can
remove the ID numbers and extract the CourseProgress record directly from
the same 5 primary keys. However, all the word (that is, newsgroups) tell
me that it is much better to use the autonumber thingy. But if that is the
case, then the only way of extracting records efficiently must be through
JOINs?

Now it could be that I am confusing two issues, and that IDs really have
nothing to do with extracting data from tables. I must admit, I am a bit
lost.

I am eager for your reply, or a comment from anyone else regarding this.

Many thanks
Ilan


"Pavel Romashkin" <pa*************@hotmail.com> wrote in message
news:3F***************@hotmail.com...
I get a feeling that you are returning a non-updateable ADO recordset
from your RunSQL procedure. Also, what is the point of the joins? It
seems that the process should be as simple as the following 2 steps:
1. Check if there is a record for "Maya" and "History" in tblStudentProgress. 2. Append or modify that record in tblStudentProgress.

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordset, CourseID as String, StudentID as String
CourseID = "History"
StudentID = "Maya"
Rst.Open "SELECT* FROM tblStudentProgress WHERE CourseFK = '" & CourseID
& "' AND StudentFK = '" & StudentID & "'", CurrentProject.Connection
IF Rst.RecordCount = 0 then
RunSQL "INSERT INTO tblStudentProgress VALUES ('History', 'Maya', 64)"
Else
RunSQL "UPDATE tblStudentProgress SET Grade = 64 WHERE CourseFK =
'History' AND StudentFK = 'Maya'"
endif
Rst.Close
Set Rst = Nothing

This is air code - please debug.
Cheers,
Pavel

Ilan Sebba wrote:

When it comes to adding records in related tables, Access is really smart. But when I try to do the same using ADO, I am really stupid.

Say I have two parent tables (eg Course, Student) and one child table
(StudentProgress). The course progress records how a student progresses on a course. I have one course (History) and one student called Maya. I now want to record her grade (64). If I do this in Access using a form, then the form will magically display her progress record.

If however I do so in VBA, then I must first distinguish between two cases: (1) A record in StudentProgress does exist for Maya, in which case I have to modify the grade to 64.
(2) No record exists for Maya in StudentProgress, so I have to create it.
Unfortunately, when I try to create the record, I am told that I will create duplicate data in an indexed or primary field. I think that what I am
accidentally trying to do is create a new record in student, and a new
record in course, as opposed to a new record in StudentProgress.

Here are my tables, and the code

tblCourse
Subject [PK]

tblStudent
Name [PK]

tblStudentProgress
CourseFK [PK1]
StudentFK [PK2]
Grade

Here is the code which is sopposed to do the following:
(1) tries to see if a record for course progress already exists for Maya in History
(2) if not, tries to create it

Private Sub TestAddingRecordInChildTable()
Dim rcsCourseProgress As ADODB.Recordset
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strSQL As String

strSelect = "SELECT tblCourse.Subject, tblStudent.Name,
tblStudentProgress.Grade "
strFrom = " FROM tblStudent INNER JOIN (tblCourse INNER JOIN
tblStudentProgress ON tblCourse.Subject = tblStudentProgress.CourseFK) ON tblStudent.Name = tblStudentProgress.StudentFK"
strWhere = " WHERE tblStudent.Name = 'Maya' AND
tblCourse.Subject='History'"

strSQL = strSelect & strFrom & strWhere

Set rcsCourseProgress = RunSQL(strSQL) 'RunSQL is my own procedure
which works OK.

If rcsCourseProgress.RecordCount = 0 Then
'no record exists for Maya in History.
rcsCourseProgress.AddNew
rcsCourseProgress.Fields("Name") = "Maya"
rcsCourseProgress.Fields("Subject") = "History"
rcsCourseProgress.Fields("Grade") = 64
rcsCourseProgress.Update
End If

End Sub

Nov 12 '05 #3

P: n/a
I was suspecting that you simplified the case, and yes, I said you need
no joins becasue of the meaningful PKs.
I think that your design is good, and PKs need to be autonumbers. And
yes, in the real case you do need to use joins.
I always in cases like this build the quereis in the query builder,
making sure I get the right records with complex joins. Then I simply
copy the SQL into VBA.
I disagree that you should make long composite keys. Try to keep the DB
logically organized - it will be easier to understand and work with. I
mean, CountryOfOrigin definitely is a property of a Student, not
tlbCouseProgress. If you ever need CountryOfOrigin from
tlbCouseProgress, you will use a join. But with the design you have the
problem is still easily solved:

Dim StrSQL as String
StrSQL = "SELECT tblCourseProgress.Grade FROM (tblCourseProgress INNER
JOIN tblStudent ON tblStudent.ID = tblCourseProgress.StudentID) INNER
JOIN tblCourse ON tblCourseProgress .CourseID = tblCourse.ID WHERE
tblStudent.Name = 'Maya' AND tblCourse.Name = 'History'"
Rst.Open StrSQL, CurrentProject.Connection

(air code -please check in query builder)
and so on. Now you can insert values if Rst.RecordCount is zero, or
update Grade if its not. To insert a record, build a query to return
CourseID, StudentID and Grade:

Dim Qry As DAO.Querydef
StrSQL = "INSERT INTO tlbCouseProgress SELECT * FROM (SELECT
tblCourses.ID AS CourseID, tblStudents.ID AS StudentID, 64 AS Grade FROM
tblCourses, tblStudents WHERE tblCourses .Name = 'History' AND
tblStudents.Name = 'Maya')"
Set Qry = CurrentDB.CreateQueryDef(vbNullString, StrSQL)
Qry.Execute
Set Qry = Nothing

Build the UPDATE SQL similarly. Check it - its all aircode.
Good luck,
Pavel

Ilan Sebba wrote:

Wow, I think you are finally helping me solve a far more serious problem I
am having.

You write "Also, what is the point of the joins." Allow me to take you up
on this.

At first I thought 'Idiot me - why have the joins' but then I realised why I
do have them. The tables I offered in my original posting where a
simplification, perhaps an over simplification. In my true database, the
tables are more complex. I think you were able to suggest that I 'do away'
with the joins because the primary key of Course Progress was a combination
of two 'meaningful' (ie not autonumbers). But what if the compound primary
key is a combination of two foreigh keys, each of which being an autonumber
in their original table? Allow me to illustrate:

tblCourse
University[PK1]
CourseName [PK2]
Trimester [PK3]
Name (eg 'history')
ID [autonumber]

tblStudent
CountryOfOrigin [PK1]
ID_NumberInCountryOfOrigin [PK2]
Name
ID [autonumber]

tblCourseProgress
StudentID
CourseID
Grade

Now, when I am writing the VB code, I have a pretty good idea of what course
I am dealing with, and which student I am dealing with, but as far as these
autonumbers go, the serve one purpose: to facilitate relationships. I have
no idea what these numbers may be.

In your original post you wrote "Check if there is a record for "Maya" and
"History" in tblStudentProgress". This is now much more complicated to do.
If my understanding is correct I can do one of the following two:

1. Run two queries, on on the tblStudent, and one on tblCourse, to extract
the autonumber ID's of 'Maya' and 'History', and then procede to do a search
as you suggested, (only using Maya's ID and History's ID rather than their
names), or

2. Use the joins.

My view is this: don't use autonumber ID's. Use really big composite keys
so that in this example the composite key for tblCoursePropgress would be
this:

tlbCouseProgress
University[PK1]
CourseName [PK2]
Trimester [PK3]
StudentCountryOfOrigin [PK4]
StudentID_NumberInCountryOfOrigin [PK5]
Grade

If I am to find "Maya"'s ID and "History"'s ID, then I have to go through
all 5 primary keys anyway. And if I have to go through all 5 primary keys,
then extracting the ID number becomes an unecessary complication as I can
remove the ID numbers and extract the CourseProgress record directly from
the same 5 primary keys. However, all the word (that is, newsgroups) tell
me that it is much better to use the autonumber thingy. But if that is the
case, then the only way of extracting records efficiently must be through
JOINs?

Now it could be that I am confusing two issues, and that IDs really have
nothing to do with extracting data from tables. I must admit, I am a bit
lost.

I am eager for your reply, or a comment from anyone else regarding this.

Many thanks

Ilan

"Pavel Romashkin" <pa*************@hotmail.com> wrote in message
news:3F***************@hotmail.com...
I get a feeling that you are returning a non-updateable ADO recordset
from your RunSQL procedure. Also, what is the point of the joins? It
seems that the process should be as simple as the following 2 steps:
1. Check if there is a record for "Maya" and "History" in

tblStudentProgress.
2. Append or modify that record in tblStudentProgress.

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordset, CourseID as String, StudentID as String
CourseID = "History"
StudentID = "Maya"
Rst.Open "SELECT* FROM tblStudentProgress WHERE CourseFK = '" & CourseID
& "' AND StudentFK = '" & StudentID & "'", CurrentProject.Connection
IF Rst.RecordCount = 0 then
RunSQL "INSERT INTO tblStudentProgress VALUES ('History', 'Maya', 64)"
Else
RunSQL "UPDATE tblStudentProgress SET Grade = 64 WHERE CourseFK =
'History' AND StudentFK = 'Maya'"
endif
Rst.Close
Set Rst = Nothing

This is air code - please debug.
Cheers,
Pavel

Ilan Sebba wrote:

When it comes to adding records in related tables, Access is really smart. But when I try to do the same using ADO, I am really stupid.

Say I have two parent tables (eg Course, Student) and one child table
(StudentProgress). The course progress records how a student progresses on a course. I have one course (History) and one student called Maya. I now want to record her grade (64). If I do this in Access using a form, then the form will magically display her progress record.

If however I do so in VBA, then I must first distinguish between two cases: (1) A record in StudentProgress does exist for Maya, in which case I have to modify the grade to 64.
(2) No record exists for Maya in StudentProgress, so I have to create it.
Unfortunately, when I try to create the record, I am told that I will create duplicate data in an indexed or primary field. I think that what I am
accidentally trying to do is create a new record in student, and a new
record in course, as opposed to a new record in StudentProgress.

Here are my tables, and the code

tblCourse
Subject [PK]

tblStudent
Name [PK]

tblStudentProgress
CourseFK [PK1]
StudentFK [PK2]
Grade

Here is the code which is sopposed to do the following:
(1) tries to see if a record for course progress already exists for Maya in History
(2) if not, tries to create it

Private Sub TestAddingRecordInChildTable()
Dim rcsCourseProgress As ADODB.Recordset
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strSQL As String

strSelect = "SELECT tblCourse.Subject, tblStudent.Name,
tblStudentProgress.Grade "
strFrom = " FROM tblStudent INNER JOIN (tblCourse INNER JOIN
tblStudentProgress ON tblCourse.Subject = tblStudentProgress.CourseFK) ON tblStudent.Name = tblStudentProgress.StudentFK"
strWhere = " WHERE tblStudent.Name = 'Maya' AND
tblCourse.Subject='History'"

strSQL = strSelect & strFrom & strWhere

Set rcsCourseProgress = RunSQL(strSQL) 'RunSQL is my own procedure
which works OK.

If rcsCourseProgress.RecordCount = 0 Then
'no record exists for Maya in History.
rcsCourseProgress.AddNew
rcsCourseProgress.Fields("Name") = "Maya"
rcsCourseProgress.Fields("Subject") = "History"
rcsCourseProgress.Fields("Grade") = 64
rcsCourseProgress.Update
End If

End Sub

Nov 12 '05 #4

P: n/a
Thank you. Helpful and reassuring.
"Pavel Romashkin" <pa*************@hotmail.com> wrote in message
news:3F***************@hotmail.com...
I was suspecting that you simplified the case, and yes, I said you need
no joins becasue of the meaningful PKs.
I think that your design is good, and PKs need to be autonumbers. And
yes, in the real case you do need to use joins.
I always in cases like this build the quereis in the query builder,
making sure I get the right records with complex joins. Then I simply
copy the SQL into VBA.
I disagree that you should make long composite keys. Try to keep the DB
logically organized - it will be easier to understand and work with. I
mean, CountryOfOrigin definitely is a property of a Student, not
tlbCouseProgress. If you ever need CountryOfOrigin from
tlbCouseProgress, you will use a join. But with the design you have the
problem is still easily solved:

Dim StrSQL as String
StrSQL = "SELECT tblCourseProgress.Grade FROM (tblCourseProgress INNER
JOIN tblStudent ON tblStudent.ID = tblCourseProgress.StudentID) INNER
JOIN tblCourse ON tblCourseProgress .CourseID = tblCourse.ID WHERE
tblStudent.Name = 'Maya' AND tblCourse.Name = 'History'"
Rst.Open StrSQL, CurrentProject.Connection

(air code -please check in query builder)
and so on. Now you can insert values if Rst.RecordCount is zero, or
update Grade if its not. To insert a record, build a query to return
CourseID, StudentID and Grade:

Dim Qry As DAO.Querydef
StrSQL = "INSERT INTO tlbCouseProgress SELECT * FROM (SELECT
tblCourses.ID AS CourseID, tblStudents.ID AS StudentID, 64 AS Grade FROM
tblCourses, tblStudents WHERE tblCourses .Name = 'History' AND
tblStudents.Name = 'Maya')"
Set Qry = CurrentDB.CreateQueryDef(vbNullString, StrSQL)
Qry.Execute
Set Qry = Nothing

Build the UPDATE SQL similarly. Check it - its all aircode.
Good luck,
Pavel

Ilan Sebba wrote:

Wow, I think you are finally helping me solve a far more serious problem I am having.

You write "Also, what is the point of the joins." Allow me to take you up on this.

At first I thought 'Idiot me - why have the joins' but then I realised why I do have them. The tables I offered in my original posting where a
simplification, perhaps an over simplification. In my true database, the tables are more complex. I think you were able to suggest that I 'do away' with the joins because the primary key of Course Progress was a combination of two 'meaningful' (ie not autonumbers). But what if the compound primary key is a combination of two foreigh keys, each of which being an autonumber in their original table? Allow me to illustrate:

tblCourse
University[PK1]
CourseName [PK2]
Trimester [PK3]
Name (eg 'history')
ID [autonumber]

tblStudent
CountryOfOrigin [PK1]
ID_NumberInCountryOfOrigin [PK2]
Name
ID [autonumber]

tblCourseProgress
StudentID
CourseID
Grade

Now, when I am writing the VB code, I have a pretty good idea of what course I am dealing with, and which student I am dealing with, but as far as these autonumbers go, the serve one purpose: to facilitate relationships. I have no idea what these numbers may be.

In your original post you wrote "Check if there is a record for "Maya" and "History" in tblStudentProgress". This is now much more complicated to do. If my understanding is correct I can do one of the following two:

1. Run two queries, on on the tblStudent, and one on tblCourse, to extract the autonumber ID's of 'Maya' and 'History', and then procede to do a search as you suggested, (only using Maya's ID and History's ID rather than their names), or

2. Use the joins.

My view is this: don't use autonumber ID's. Use really big composite ke ys so that in this example the composite key for tblCoursePropgress would be this:

tlbCouseProgress
University[PK1]
CourseName [PK2]
Trimester [PK3]
StudentCountryOfOrigin [PK4]
StudentID_NumberInCountryOfOrigin [PK5]
Grade

If I am to find "Maya"'s ID and "History"'s ID, then I have to go through all 5 primary keys anyway. And if I have to go through all 5 primary keys, then extracting the ID number becomes an unecessary complication as I can remove the ID numbers and extract the CourseProgress record directly from the same 5 primary keys. However, all the word (that is, newsgroups) tell me that it is much better to use the autonumber thingy. But if that is the case, then the only way of extracting records efficiently must be through JOINs?

Now it could be that I am confusing two issues, and that IDs really have
nothing to do with extracting data from tables. I must admit, I am a bit lost.

I am eager for your reply, or a comment from anyone else regarding this.

Many thanks

Ilan

"Pavel Romashkin" <pa*************@hotmail.com> wrote in message
news:3F***************@hotmail.com...
I get a feeling that you are returning a non-updateable ADO recordset
from your RunSQL procedure. Also, what is the point of the joins? It
seems that the process should be as simple as the following 2 steps:
1. Check if there is a record for "Maya" and "History" in

tblStudentProgress.
2. Append or modify that record in tblStudentProgress.

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordset, CourseID as String, StudentID as String
CourseID = "History"
StudentID = "Maya"
Rst.Open "SELECT* FROM tblStudentProgress WHERE CourseFK = '" & CourseID & "' AND StudentFK = '" & StudentID & "'", CurrentProject.Connection
IF Rst.RecordCount = 0 then
RunSQL "INSERT INTO tblStudentProgress VALUES ('History', 'Maya', 64)"
Else
RunSQL "UPDATE tblStudentProgress SET Grade = 64 WHERE CourseFK =
'History' AND StudentFK = 'Maya'"
endif
Rst.Close
Set Rst = Nothing

This is air code - please debug.
Cheers,
Pavel

Ilan Sebba wrote:
>
> When it comes to adding records in related tables, Access is really

smart.
> But when I try to do the same using ADO, I am really stupid.
>
> Say I have two parent tables (eg Course, Student) and one child table > (StudentProgress). The course progress records how a student progresses
on
> a course. I have one course (History) and one student called Maya.
I now
> want to record her grade (64). If I do this in Access using a form,

then
> the form will magically display her progress record.
>
> If however I do so in VBA, then I must first distinguish between two

cases:
> (1) A record in StudentProgress does exist for Maya, in which case I

have to
> modify the grade to 64.
> (2) No record exists for Maya in StudentProgress, so I have to
create it.
>
> Unfortunately, when I try to create the record, I am told that I
will create
> duplicate data in an indexed or primary field. I think that what I
am > accidentally trying to do is create a new record in student, and a new > record in course, as opposed to a new record in StudentProgress.
>
> Here are my tables, and the code
>
> tblCourse
> Subject [PK]
>
> tblStudent
> Name [PK]
>
> tblStudentProgress
> CourseFK [PK1]
> StudentFK [PK2]
> Grade
>
> Here is the code which is sopposed to do the following:
> (1) tries to see if a record for course progress already exists for Maya in
> History
> (2) if not, tries to create it
>
> Private Sub TestAddingRecordInChildTable()
> Dim rcsCourseProgress As ADODB.Recordset
> Dim strSelect As String
> Dim strFrom As String
> Dim strWhere As String
> Dim strSQL As String
>
> strSelect = "SELECT tblCourse.Subject, tblStudent.Name,
> tblStudentProgress.Grade "
> strFrom = " FROM tblStudent INNER JOIN (tblCourse INNER JOIN
> tblStudentProgress ON tblCourse.Subject =
tblStudentProgress.CourseFK) ON
> tblStudent.Name = tblStudentProgress.StudentFK"
> strWhere = " WHERE tblStudent.Name = 'Maya' AND
> tblCourse.Subject='History'"
>
> strSQL = strSelect & strFrom & strWhere
>
> Set rcsCourseProgress = RunSQL(strSQL) 'RunSQL is my own

procedure > which works OK.
>
> If rcsCourseProgress.RecordCount = 0 Then
> 'no record exists for Maya in History.
> rcsCourseProgress.AddNew
> rcsCourseProgress.Fields("Name") = "Maya"
> rcsCourseProgress.Fields("Subject") = "History"
> rcsCourseProgress.Fields("Grade") = 64
> rcsCourseProgress.Update
> End If
>
> End Sub

Nov 12 '05 #5

P: n/a
Thank you. Helpful and reassuring.
"Pavel Romashkin" <pa*************@hotmail.com> wrote in message
news:3F***************@hotmail.com...
I was suspecting that you simplified the case, and yes, I said you need
no joins becasue of the meaningful PKs.
I think that your design is good, and PKs need to be autonumbers. And
yes, in the real case you do need to use joins.
I always in cases like this build the quereis in the query builder,
making sure I get the right records with complex joins. Then I simply
copy the SQL into VBA.
I disagree that you should make long composite keys. Try to keep the DB
logically organized - it will be easier to understand and work with. I
mean, CountryOfOrigin definitely is a property of a Student, not
tlbCouseProgress. If you ever need CountryOfOrigin from
tlbCouseProgress, you will use a join. But with the design you have the
problem is still easily solved:

Dim StrSQL as String
StrSQL = "SELECT tblCourseProgress.Grade FROM (tblCourseProgress INNER
JOIN tblStudent ON tblStudent.ID = tblCourseProgress.StudentID) INNER
JOIN tblCourse ON tblCourseProgress .CourseID = tblCourse.ID WHERE
tblStudent.Name = 'Maya' AND tblCourse.Name = 'History'"
Rst.Open StrSQL, CurrentProject.Connection

(air code -please check in query builder)
and so on. Now you can insert values if Rst.RecordCount is zero, or
update Grade if its not. To insert a record, build a query to return
CourseID, StudentID and Grade:

Dim Qry As DAO.Querydef
StrSQL = "INSERT INTO tlbCouseProgress SELECT * FROM (SELECT
tblCourses.ID AS CourseID, tblStudents.ID AS StudentID, 64 AS Grade FROM
tblCourses, tblStudents WHERE tblCourses .Name = 'History' AND
tblStudents.Name = 'Maya')"
Set Qry = CurrentDB.CreateQueryDef(vbNullString, StrSQL)
Qry.Execute
Set Qry = Nothing

Build the UPDATE SQL similarly. Check it - its all aircode.
Good luck,
Pavel

Ilan Sebba wrote:

Wow, I think you are finally helping me solve a far more serious problem I am having.

You write "Also, what is the point of the joins." Allow me to take you up on this.

At first I thought 'Idiot me - why have the joins' but then I realised why I do have them. The tables I offered in my original posting where a
simplification, perhaps an over simplification. In my true database, the tables are more complex. I think you were able to suggest that I 'do away' with the joins because the primary key of Course Progress was a combination of two 'meaningful' (ie not autonumbers). But what if the compound primary key is a combination of two foreigh keys, each of which being an autonumber in their original table? Allow me to illustrate:

tblCourse
University[PK1]
CourseName [PK2]
Trimester [PK3]
Name (eg 'history')
ID [autonumber]

tblStudent
CountryOfOrigin [PK1]
ID_NumberInCountryOfOrigin [PK2]
Name
ID [autonumber]

tblCourseProgress
StudentID
CourseID
Grade

Now, when I am writing the VB code, I have a pretty good idea of what course I am dealing with, and which student I am dealing with, but as far as these autonumbers go, the serve one purpose: to facilitate relationships. I have no idea what these numbers may be.

In your original post you wrote "Check if there is a record for "Maya" and "History" in tblStudentProgress". This is now much more complicated to do. If my understanding is correct I can do one of the following two:

1. Run two queries, on on the tblStudent, and one on tblCourse, to extract the autonumber ID's of 'Maya' and 'History', and then procede to do a search as you suggested, (only using Maya's ID and History's ID rather than their names), or

2. Use the joins.

My view is this: don't use autonumber ID's. Use really big composite ke ys so that in this example the composite key for tblCoursePropgress would be this:

tlbCouseProgress
University[PK1]
CourseName [PK2]
Trimester [PK3]
StudentCountryOfOrigin [PK4]
StudentID_NumberInCountryOfOrigin [PK5]
Grade

If I am to find "Maya"'s ID and "History"'s ID, then I have to go through all 5 primary keys anyway. And if I have to go through all 5 primary keys, then extracting the ID number becomes an unecessary complication as I can remove the ID numbers and extract the CourseProgress record directly from the same 5 primary keys. However, all the word (that is, newsgroups) tell me that it is much better to use the autonumber thingy. But if that is the case, then the only way of extracting records efficiently must be through JOINs?

Now it could be that I am confusing two issues, and that IDs really have
nothing to do with extracting data from tables. I must admit, I am a bit lost.

I am eager for your reply, or a comment from anyone else regarding this.

Many thanks

Ilan

"Pavel Romashkin" <pa*************@hotmail.com> wrote in message
news:3F***************@hotmail.com...
I get a feeling that you are returning a non-updateable ADO recordset
from your RunSQL procedure. Also, what is the point of the joins? It
seems that the process should be as simple as the following 2 steps:
1. Check if there is a record for "Maya" and "History" in

tblStudentProgress.
2. Append or modify that record in tblStudentProgress.

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordset, CourseID as String, StudentID as String
CourseID = "History"
StudentID = "Maya"
Rst.Open "SELECT* FROM tblStudentProgress WHERE CourseFK = '" & CourseID & "' AND StudentFK = '" & StudentID & "'", CurrentProject.Connection
IF Rst.RecordCount = 0 then
RunSQL "INSERT INTO tblStudentProgress VALUES ('History', 'Maya', 64)"
Else
RunSQL "UPDATE tblStudentProgress SET Grade = 64 WHERE CourseFK =
'History' AND StudentFK = 'Maya'"
endif
Rst.Close
Set Rst = Nothing

This is air code - please debug.
Cheers,
Pavel

Ilan Sebba wrote:
>
> When it comes to adding records in related tables, Access is really

smart.
> But when I try to do the same using ADO, I am really stupid.
>
> Say I have two parent tables (eg Course, Student) and one child table > (StudentProgress). The course progress records how a student progresses
on
> a course. I have one course (History) and one student called Maya.
I now
> want to record her grade (64). If I do this in Access using a form,

then
> the form will magically display her progress record.
>
> If however I do so in VBA, then I must first distinguish between two

cases:
> (1) A record in StudentProgress does exist for Maya, in which case I

have to
> modify the grade to 64.
> (2) No record exists for Maya in StudentProgress, so I have to
create it.
>
> Unfortunately, when I try to create the record, I am told that I
will create
> duplicate data in an indexed or primary field. I think that what I
am > accidentally trying to do is create a new record in student, and a new > record in course, as opposed to a new record in StudentProgress.
>
> Here are my tables, and the code
>
> tblCourse
> Subject [PK]
>
> tblStudent
> Name [PK]
>
> tblStudentProgress
> CourseFK [PK1]
> StudentFK [PK2]
> Grade
>
> Here is the code which is sopposed to do the following:
> (1) tries to see if a record for course progress already exists for Maya in
> History
> (2) if not, tries to create it
>
> Private Sub TestAddingRecordInChildTable()
> Dim rcsCourseProgress As ADODB.Recordset
> Dim strSelect As String
> Dim strFrom As String
> Dim strWhere As String
> Dim strSQL As String
>
> strSelect = "SELECT tblCourse.Subject, tblStudent.Name,
> tblStudentProgress.Grade "
> strFrom = " FROM tblStudent INNER JOIN (tblCourse INNER JOIN
> tblStudentProgress ON tblCourse.Subject =
tblStudentProgress.CourseFK) ON
> tblStudent.Name = tblStudentProgress.StudentFK"
> strWhere = " WHERE tblStudent.Name = 'Maya' AND
> tblCourse.Subject='History'"
>
> strSQL = strSelect & strFrom & strWhere
>
> Set rcsCourseProgress = RunSQL(strSQL) 'RunSQL is my own

procedure > which works OK.
>
> If rcsCourseProgress.RecordCount = 0 Then
> 'no record exists for Maya in History.
> rcsCourseProgress.AddNew
> rcsCourseProgress.Fields("Name") = "Maya"
> rcsCourseProgress.Fields("Subject") = "History"
> rcsCourseProgress.Fields("Grade") = 64
> rcsCourseProgress.Update
> End If
>
> End Sub

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.