Problem (1 Viewer)

[mapat]

Hello,

something really weird is happening with the db. I have an ACCESS 2007 front-end linked to a SQL Server 2008 back-end. All the users have the same front-end on their PCs. I have 2 tables Guardians and Students which are linked with a 1 to many relationship starting from Guardians to Students.

GuardianA
StudentA
StudentB

GuardianB
StudentC

When the users enter different records at the same time then the students are assigned the wrong Guardian. In the above case what would happen is for instance StudentA would be assigned to GuardianB. Has anyone been through something like this before?

Thanks everybody

 

[Access Hero]

Your database is not properly normalized. You need to consider adding a junction or associative table between Guardian & Student, enforcing a M:M relationship.

 

[mapat]

Your database is not properly normalized. You need to consider adding a junction or associative table between Guardian & Student, enforcing a M:M relationship.

Why would I want a M:M relationship between Guardians and Students. The idea is that 1 Guardian can have 1 or more students?

Thanks

 

[Access Hero]

So, you're saying that GuardianID is a single field in Student and you're able to assign a student to a guardian but that (Student) record does not have the correct GuardianID?

 

[mapat]

So, you're saying that GuardianID is a single field in Student and you're able to assign a student to a guardian but that (Student) record does not have the correct GuardianID?

That's exactly what's happening. Also, GuardianID is a foreign key in the Students table.

thanks

 

[Access Hero]

Then, as I understand it, you are putting a GuardianID in to the Students table and it is changing against your/the user's will?

 

[mapat]

Then, as I understand it, you are putting a GuardianID in to the Students table and it is changing against your/the user's will?

When pressing a createRecord button, the Guardians info is entered first with an INSERT stmt, and right after it the Students info gets entered again with an INSERT stmt. This is all done from the VBA code. Isn't this a locking issue?
Thanks

 

[Access Hero]

It doesn't sound like a locking issue at all to me. If it were, the user would be getting an ODBC error stating that the record could not be inserted (unless you've trapped it).

What does

When the users enter different records at the same time

mean?

I take it you're not using bound forms. Have you considered that the VBA logic that inserts the records is wrong? Are you willing to post the VBA code here?

 

[mapat]

It doesn't sound like a locking issue at all to me. If it were, the user would be getting an ODBC error stating that the record could not be inserted (unless you've trapped it).

What does

mean?

I take it you're not using bound forms. Have you considered that the VBA logic that inserts the records is wrong? Are you willing to post the VBA code here?

This is the executing code when pressing that button:

Private Sub cmdNewStudent_Click()
On Error GoTo Finish_NewStudent
Dim strSQL As String, rst As DAO.Recordset, studentID As String, NewGuardianID, dbs As DAO.Database
Dim i As Long, CurrentYear As Integer, IDNum As String
Dim Region As String
CurrentYear = DLookup("CurrentYear", "tCurrent")
Region = DLookup("DatabaseID", "tCustomization")
Set dbs = DBEngine(0)(0)

DoCmd.Echo False
DoCmd.Hourglass True

If IsNull(GuardianID) Then
'if needed set new Guardian ID number
GuardianID = "G" & Region & Right(1000000 + GetNewGuardianID, 6)

'create tGuardian record
strSQL = "INSERT INTO tGuardians ( ID, GuardianFirst, GuardianLast, [Update], AwardYear ) " _
& "VALUES ( '" & GuardianID & "', " & AddQuotes(FirstName) & ", " & AddQuotes(LastName) _
& ", #" & Date & "#, " & StartYear & " );"
ActionQuery strSQL

'create tguardianyearly record
strSQL = "INSERT INTO tGuardianYear ( GuardianID, GuardYear, QualifyIncome ) " _
& "VALUES ('" & GuardianID & "', " & CurrentYear & ", 'None' );"
ActionQuery strSQL

'Write ID to UsedIDs table
strSQL = "INSERT INTO tUsedIDs ( IDNumber) VALUES ( '" & GuardianID & "' );"
ActionQuery strSQL

End If

'Assign new ID number
'-n- Changed cycle through the IDs: now just looks for the max ID and adds one.
strSQL = "SELECT Max(Mid([IDNumber],5)) AS IDNum FROM tUsedIDs WHERE ((Left([IDNumber],1) = '" & Funding & "') AND (Mid([IDNumber],2,3)='" & Region & "'));"
Set rst = dbs.OpenRecordset(strSQL)
i = Nz(rst!IDNum, 0)
i = i + 1
studentID = Funding & Region & Right((1000000 + i), 6)

'Create tRecipient Students record
strSQL = "INSERT INTO [tRecipient Students] ( [Student ID], StudentFirst, StudentLast, Funding, " _
& "Start, Sibling, Guardians_ID, UpdateStudent ) VALUES ( '" & studentID & "', " _
& AddQuotes(StudentFirst) & ", " & AddQuotes(StudentLast) & ", " & Funding & ", " & CLng(StartYear) _
& ", " & Sibling & ", '" & GuardianID & "', #" & Date & "# );"
ActionQuery strSQL

'Get ParentQual if this is an addition to existing family
If GuardianExists = 1 Then
Dim Qualified As String, ParentQual As Long
Set rst = dbs.OpenRecordset("SELECT ParentQual FROM tStudentYear INNER JOIN [tRecipient Students] ON " _
& "tStudentYear.studentID = [tRecipient Students].[Student ID] WHERE Guardians_ID = '" _
& GuardianID & "' AND Year = " & CurrentYear & ";")
If rst.RecordCount > 0 Then
ParentQual = rst!ParentQual
If InStr(1, Left(ParentQual, 3), "7") Then Qualified = "No"
End If
Set rst = Nothing
End If

'Create tstudentyear record
If Len(Qualified) = 0 Then Qualified = "Pending"
If ParentQual = 0 Then ParentQual = 2220
strSQL = "INSERT INTO tstudentyear ( Year, studentID, Qualified, ParentQual ) " _
& "VALUES ( " & CurrentYear & ", '" & studentID & "', '" & Qualified _
& "', " & ParentQual & " );"
ActionQuery strSQL

'Write ID to UsedIDs table
strSQL = "INSERT INTO tUsedIDs ( IDNumber) VALUES ( '" & studentID & "' );"
ActionQuery strSQL

'Modified JEE 6/1/05.
If SysCmd(acSysCmdGetObjectState, acForm, "Qualification Form") <> 0 Then
Forms![Qualification Form].Requery
Else
DoCmd.OpenForm "Qualification Form"
End If

Forms("Qualification Form").[Student ID].SetFocus
DoCmd.FindRecord studentID, acEntire, , , , acCurrent
Forms("Qualification Form").[AppDate] = Date
Forms("Qualification Form").[Grade].SetFocus
Forms("Qualification Form").SetFocus

DoCmd.Close acForm, "New Student Input"

Finish_NewStudent:
DoCmd.Hourglass False
DoCmd.Echo True
End Sub


Thank you

 

[Access Hero]

This line:

GuardianID = "G" & Region & Right(1000000 + GetNewGuardianID, 6)

is bothersome. You should really never calculate a smart ID for a multi-user OLTP system and use that as the primary key. If I understood what you said in your first post correctly, cross contamination occurs between 2 different students. There are many things you should do to resolve this (and should do even if you weren't having a problem):
1. Get rid of the smart codes (all of them, not just the GuardianID). They will only cause grief.
2. Allow SQL Server to assign primary keys and assign no "value" to the keys themselves except as a mechanism to facilitate referential integrity.
3. Consider allowing Access to do what Access does well: manage data on bound forms.

There's not much else I can tell you without a look at the FE & BE.

 

[mapat]

This line:

GuardianID = "G" & Region & Right(1000000 + GetNewGuardianID, 6)

is bothersome. You should really never calculate a smart ID for a multi-user OLTP system and use that as the primary key. If I understood what you said in your first post correctly, cross contamination occurs between 2 different students. There are many things you should do to resolve this (and should do even if you weren't having a problem):
1. Get rid of the smart codes (all of them, not just the GuardianID). They will only cause grief.
2. Allow SQL Server to assign primary keys and assign no "value" to the keys themselves except as a mechanism to facilitate referential integrity.
3. Consider allowing Access to do what Access does well: manage data on bound forms.

There's not much else I can tell you without a look at the FE & BE.

I would like to change the way IDs are created for the system, but the only problem is that inside each ID there are certain keys that indicate for instance the type of fund that is used to "fund" that kid's scholarship. For instance the ID "1CSP000034" indicates first that this is a child's id because it starts with a number and second that specific number belongs to funding type 1. The id "2CSP....." belongs to a kid with funding type 2, and so on. Additionally, IDs that start with "G....." belong to guardians, and the ones starting with "S...." belong to schools. So, this system is about 10 years old, and the IDs play a major role in it, so I don't see changing the way IDs are created a possibility. Believe me, it would be a nightmare to do this.

Thanks

 

[mapat]

I would like to change the way IDs are created for the system, but the only problem is that inside each ID there are certain keys that indicate for instance the type of fund that is used to "fund" that kid's scholarship. For instance the ID "1CSP000034" indicates first that this is a child's id because it starts with a number and second that specific number belongs to funding type 1. The id "2CSP....." belongs to a kid with funding type 2, and so on. Additionally, IDs that start with "G....." belong to guardians, and the ones starting with "S...." belong to schools. So, this system is about 10 years old, and the IDs play a major role in it, so I don't see changing the way IDs are created a possibility. Believe me, it would be a nightmare to do this.

Thanks

Hello,
Can someone give me a hand with this issue?

Thank you very much