473,224 Members | 1,618 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

Creating a record in a child table using ADO

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
5 4298
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Mike Turco | last post by:
What is the difference between creating relationships in the front-end vs. the back-end database? I was trying to create a relationship in a database front-end and noticed that I could not check...
4
by: | last post by:
This is blowing my mind because I thought this setup was working.... How is a new record generated in the table of a Child subform whenever a new record is generated in the table of the Master...
6
by: polocar | last post by:
Hi, I'm writing a program in Visual C# 2005 Professional Edition. This program connects to a SQL Server 2005 database called "Generations" (in which there is only one table, called...
2
by: Swinky | last post by:
I hope someone can help...I feel like I'm walking in the dark without a flashlight (I'm NOT a programmer but have been called to task to do some work in Access that is above my head). I have...
7
by: john | last post by:
In my form I have a master table and a details table linked 1xM. I can search through the whole parent table but I also like to be able to search through the child table fields to find parent...
1
by: Tony Dunn | last post by:
I have inherited another developer's database (Access 2003) and with it a problem I'm struggling with. The problem is this. I have a parent-child form pair, which is populated by a table (the...
1
by: csharpa | last post by:
I have 2 tables Patient(parent) , Battery(Child). when i gave input to it, in parent table the new record will be added but in child table the new record will not be added although I'm sending the FK...
0
by: emalcolm_FLA | last post by:
Hello and TIA for any help with this non profit Christmas assistance project. I have an applicant (app history) and child (child history) tables (4 total). I need to grab the next available (in...
2
by: adwest | last post by:
Forgive me if this is a foolish question. I "play" in Access and have only created a few rather simple relational databases. My knowledge and experience is limited. I have no formal training, just...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.