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 5 4308
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
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
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
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
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
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |