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
5  4333 
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
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
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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,
|
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...
|
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...
|
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...
|
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
| | |
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...
|
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...
|
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).
|
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...
|
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...
|
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...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
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...
| |
