473,692 Members | 2,197 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
(StudentProgres s). 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]

tblStudentProgr ess
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 TestAddingRecor dInChildTable()
Dim rcsCourseProgre ss As ADODB.Recordset
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strSQL As String

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

strSQL = strSelect & strFrom & strWhere

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

If rcsCourseProgre ss.RecordCount = 0 Then
'no record exists for Maya in History.
rcsCourseProgre ss.AddNew
rcsCourseProgre ss.Fields("Name ") = "Maya"
rcsCourseProgre ss.Fields("Subj ect") = "History"
rcsCourseProgre ss.Fields("Grad e") = 64
rcsCourseProgre ss.Update
End If

End Sub

Nov 12 '05 #1
5 4330
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 tblStudentProgr ess.
2. Append or modify that record in tblStudentProgr ess.

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordset , CourseID as String, StudentID as String
CourseID = "History"
StudentID = "Maya"
Rst.Open "SELECT* FROM tblStudentProgr ess WHERE CourseFK = '" & CourseID
& "' AND StudentFK = '" & StudentID & "'", CurrentProject. Connection
IF Rst.RecordCount = 0 then
RunSQL "INSERT INTO tblStudentProgr ess VALUES ('History', 'Maya', 64)"
Else
RunSQL "UPDATE tblStudentProgr ess 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
(StudentProgres s). 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]

tblStudentProgr ess
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 TestAddingRecor dInChildTable()
Dim rcsCourseProgre ss As ADODB.Recordset
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strSQL As String

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

strSQL = strSelect & strFrom & strWhere

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

If rcsCourseProgre ss.RecordCount = 0 Then
'no record exists for Maya in History.
rcsCourseProgre ss.AddNew
rcsCourseProgre ss.Fields("Name ") = "Maya"
rcsCourseProgre ss.Fields("Subj ect") = "History"
rcsCourseProgre ss.Fields("Grad e") = 64
rcsCourseProgre ss.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_NumberInCoun tryOfOrigin [PK2]
Name
ID [autonumber]

tblCourseProgre ss
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 tblStudentProgr ess". 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 tblCoursePropgr ess would be
this:

tlbCouseProgres s
University[PK1]
CourseName [PK2]
Trimester [PK3]
StudentCountryO fOrigin [PK4]
StudentID_Numbe rInCountryOfOri gin [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 tblStudentProgr ess. 2. Append or modify that record in tblStudentProgr ess.

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordset , CourseID as String, StudentID as String
CourseID = "History"
StudentID = "Maya"
Rst.Open "SELECT* FROM tblStudentProgr ess WHERE CourseFK = '" & CourseID
& "' AND StudentFK = '" & StudentID & "'", CurrentProject. Connection
IF Rst.RecordCount = 0 then
RunSQL "INSERT INTO tblStudentProgr ess VALUES ('History', 'Maya', 64)"
Else
RunSQL "UPDATE tblStudentProgr ess 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
(StudentProgres s). 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]

tblStudentProgr ess
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 TestAddingRecor dInChildTable()
Dim rcsCourseProgre ss As ADODB.Recordset
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strSQL As String

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

strSQL = strSelect & strFrom & strWhere

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

If rcsCourseProgre ss.RecordCount = 0 Then
'no record exists for Maya in History.
rcsCourseProgre ss.AddNew
rcsCourseProgre ss.Fields("Name ") = "Maya"
rcsCourseProgre ss.Fields("Subj ect") = "History"
rcsCourseProgre ss.Fields("Grad e") = 64
rcsCourseProgre ss.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
tlbCouseProgres s. If you ever need CountryOfOrigin from
tlbCouseProgres s, you will use a join. But with the design you have the
problem is still easily solved:

Dim StrSQL as String
StrSQL = "SELECT tblCourseProgre ss.Grade FROM (tblCourseProgr ess INNER
JOIN tblStudent ON tblStudent.ID = tblCourseProgre ss.StudentID) INNER
JOIN tblCourse ON tblCourseProgre ss .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 tlbCouseProgres s SELECT * FROM (SELECT
tblCourses.ID AS CourseID, tblStudents.ID AS StudentID, 64 AS Grade FROM
tblCourses, tblStudents WHERE tblCourses .Name = 'History' AND
tblStudents.Nam e = 'Maya')"
Set Qry = CurrentDB.Creat eQueryDef(vbNul lString, 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_NumberInCoun tryOfOrigin [PK2]
Name
ID [autonumber]

tblCourseProgre ss
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 tblStudentProgr ess". 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 tblCoursePropgr ess would be
this:

tlbCouseProgres s
University[PK1]
CourseName [PK2]
Trimester [PK3]
StudentCountryO fOrigin [PK4]
StudentID_Numbe rInCountryOfOri gin [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

tblStudentProgr ess.
2. Append or modify that record in tblStudentProgr ess.

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordset , CourseID as String, StudentID as String
CourseID = "History"
StudentID = "Maya"
Rst.Open "SELECT* FROM tblStudentProgr ess WHERE CourseFK = '" & CourseID
& "' AND StudentFK = '" & StudentID & "'", CurrentProject. Connection
IF Rst.RecordCount = 0 then
RunSQL "INSERT INTO tblStudentProgr ess VALUES ('History', 'Maya', 64)"
Else
RunSQL "UPDATE tblStudentProgr ess 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
(StudentProgres s). 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]

tblStudentProgr ess
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 TestAddingRecor dInChildTable()
Dim rcsCourseProgre ss As ADODB.Recordset
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strSQL As String

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

strSQL = strSelect & strFrom & strWhere

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

If rcsCourseProgre ss.RecordCount = 0 Then
'no record exists for Maya in History.
rcsCourseProgre ss.AddNew
rcsCourseProgre ss.Fields("Name ") = "Maya"
rcsCourseProgre ss.Fields("Subj ect") = "History"
rcsCourseProgre ss.Fields("Grad e") = 64
rcsCourseProgre ss.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
tlbCouseProgres s. If you ever need CountryOfOrigin from
tlbCouseProgres s, you will use a join. But with the design you have the
problem is still easily solved:

Dim StrSQL as String
StrSQL = "SELECT tblCourseProgre ss.Grade FROM (tblCourseProgr ess INNER
JOIN tblStudent ON tblStudent.ID = tblCourseProgre ss.StudentID) INNER
JOIN tblCourse ON tblCourseProgre ss .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 tlbCouseProgres s SELECT * FROM (SELECT
tblCourses.ID AS CourseID, tblStudents.ID AS StudentID, 64 AS Grade FROM
tblCourses, tblStudents WHERE tblCourses .Name = 'History' AND
tblStudents.Nam e = 'Maya')"
Set Qry = CurrentDB.Creat eQueryDef(vbNul lString, 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_NumberInCoun tryOfOrigin [PK2]
Name
ID [autonumber]

tblCourseProgre ss
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 tblStudentProgr ess". 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 tblCoursePropgr ess would be this:

tlbCouseProgres s
University[PK1]
CourseName [PK2]
Trimester [PK3]
StudentCountryO fOrigin [PK4]
StudentID_Numbe rInCountryOfOri gin [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

tblStudentProgr ess.
2. Append or modify that record in tblStudentProgr ess.

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordset , CourseID as String, StudentID as String
CourseID = "History"
StudentID = "Maya"
Rst.Open "SELECT* FROM tblStudentProgr ess WHERE CourseFK = '" & CourseID & "' AND StudentFK = '" & StudentID & "'", CurrentProject. Connection
IF Rst.RecordCount = 0 then
RunSQL "INSERT INTO tblStudentProgr ess VALUES ('History', 'Maya', 64)"
Else
RunSQL "UPDATE tblStudentProgr ess 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 > (StudentProgres s). 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]
>
> tblStudentProgr ess
> 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 TestAddingRecor dInChildTable()
> Dim rcsCourseProgre ss As ADODB.Recordset
> Dim strSelect As String
> Dim strFrom As String
> Dim strWhere As String
> Dim strSQL As String
>
> strSelect = "SELECT tblCourse.Subje ct, tblStudent.Name ,
> tblStudentProgr ess.Grade "
> strFrom = " FROM tblStudent INNER JOIN (tblCourse INNER JOIN
> tblStudentProgr ess ON tblCourse.Subje ct =
tblStudentProgr ess.CourseFK) ON
> tblStudent.Name = tblStudentProgr ess.StudentFK"
> strWhere = " WHERE tblStudent.Name = 'Maya' AND
> tblCourse.Subje ct='History'"
>
> strSQL = strSelect & strFrom & strWhere
>
> Set rcsCourseProgre ss = RunSQL(strSQL) 'RunSQL is my own

procedure > which works OK.
>
> If rcsCourseProgre ss.RecordCount = 0 Then
> 'no record exists for Maya in History.
> rcsCourseProgre ss.AddNew
> rcsCourseProgre ss.Fields("Name ") = "Maya"
> rcsCourseProgre ss.Fields("Subj ect") = "History"
> rcsCourseProgre ss.Fields("Grad e") = 64
> rcsCourseProgre ss.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
tlbCouseProgres s. If you ever need CountryOfOrigin from
tlbCouseProgres s, you will use a join. But with the design you have the
problem is still easily solved:

Dim StrSQL as String
StrSQL = "SELECT tblCourseProgre ss.Grade FROM (tblCourseProgr ess INNER
JOIN tblStudent ON tblStudent.ID = tblCourseProgre ss.StudentID) INNER
JOIN tblCourse ON tblCourseProgre ss .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 tlbCouseProgres s SELECT * FROM (SELECT
tblCourses.ID AS CourseID, tblStudents.ID AS StudentID, 64 AS Grade FROM
tblCourses, tblStudents WHERE tblCourses .Name = 'History' AND
tblStudents.Nam e = 'Maya')"
Set Qry = CurrentDB.Creat eQueryDef(vbNul lString, 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_NumberInCoun tryOfOrigin [PK2]
Name
ID [autonumber]

tblCourseProgre ss
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 tblStudentProgr ess". 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 tblCoursePropgr ess would be this:

tlbCouseProgres s
University[PK1]
CourseName [PK2]
Trimester [PK3]
StudentCountryO fOrigin [PK4]
StudentID_Numbe rInCountryOfOri gin [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

tblStudentProgr ess.
2. Append or modify that record in tblStudentProgr ess.

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordset , CourseID as String, StudentID as String
CourseID = "History"
StudentID = "Maya"
Rst.Open "SELECT* FROM tblStudentProgr ess WHERE CourseFK = '" & CourseID & "' AND StudentFK = '" & StudentID & "'", CurrentProject. Connection
IF Rst.RecordCount = 0 then
RunSQL "INSERT INTO tblStudentProgr ess VALUES ('History', 'Maya', 64)"
Else
RunSQL "UPDATE tblStudentProgr ess 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 > (StudentProgres s). 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]
>
> tblStudentProgr ess
> 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 TestAddingRecor dInChildTable()
> Dim rcsCourseProgre ss As ADODB.Recordset
> Dim strSelect As String
> Dim strFrom As String
> Dim strWhere As String
> Dim strSQL As String
>
> strSelect = "SELECT tblCourse.Subje ct, tblStudent.Name ,
> tblStudentProgr ess.Grade "
> strFrom = " FROM tblStudent INNER JOIN (tblCourse INNER JOIN
> tblStudentProgr ess ON tblCourse.Subje ct =
tblStudentProgr ess.CourseFK) ON
> tblStudent.Name = tblStudentProgr ess.StudentFK"
> strWhere = " WHERE tblStudent.Name = 'Maya' AND
> tblCourse.Subje ct='History'"
>
> strSQL = strSelect & strFrom & strWhere
>
> Set rcsCourseProgre ss = RunSQL(strSQL) 'RunSQL is my own

procedure > which works OK.
>
> If rcsCourseProgre ss.RecordCount = 0 Then
> 'no record exists for Maya in History.
> rcsCourseProgre ss.AddNew
> rcsCourseProgre ss.Fields("Name ") = "Maya"
> rcsCourseProgre ss.Fields("Subj ect") = "History"
> rcsCourseProgre ss.Fields("Grad e") = 64
> rcsCourseProgre ss.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
4698
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 the referential integrity box. What gives? Continuing on with that line of thinking, I understand what do the relationships do for you in a database, but what do they do physically to the tables? Thanks,
4
2118
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 form - even though NO Child field data are entered? There's no problem if at least one of the Child's subform fields is entered. But if instead a command button is clicked in the Child's subform to open yet another form and create records in...
6
3854
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 "Generations"), and it allows the user to add, edit and delete the various records of the table. "Generations" table has the following fields: "IDPerson", NamePerson", "AgePerson" and "IDParent". A record contains the information about a person (his name, his...
2
3462
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 code that will successfully copy a record and append the information to a new record in the same table (parent table) within a form. However, there are related child tables with primary keys (set to Autonumber) stored in sub-forms. That information...
7
4536
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 records. Should I design a new form for this or can I somehow make this work in the same form. Thanks in advance, john
1
2118
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 parent) and a query (the child). The two sources are linked by an identical unique number. The table populating the parent uses the number as primary key. The child table has no primary key so it can used one-to-many. Users add data through the form...
1
1569
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 to child table and also i set the automatic identity generator for it. why it will not add the new record to child table? string connection = "Data Source=.\\SQLEXPRESS;AttachDbFilename=\"C:\\Program Files\\Microsoft SQL...
0
2039
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 house case number, appt date and time) for the applicants yearly history and the childs yearly history and then print a report with the applicants info and this in house case number. The forms are linked with ID_app (from the applicant table).
2
2321
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 picked up a few books and have gone from there... I'm not sure how to relate my question using the accepted shorthand, so I'm afraid this will be a narrative description. I am using Access 2003. The database is for property management. I have...
0
8608
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8540
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9086
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8963
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
6459
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4557
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2974
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2241
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1959
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.