I'm running the following function to
check whether student data has been updated todayand if it hasn't
create a temporary table,
upload data from excel file (all text fields)
change the StudentYeargroup field to integer type
fill the studentyeargroup field with integers
Everything works fine until the line indicated and I cant work out why
Any ideas????
Code:
Function tempReview(tblname As String, filename As String)
On Error GoTo Tempreview_err
Dim mysql As String
Dim db As Database
Set db = CurrentDb
Dim mystring As String
Dim varX As Variant
varX = DLookup("[update_date]", "tbl_update", _
"[update_date] = #" & Date & "#")
Debug.Print varX
If IsNull(varX) Then
'complete download
'delete the old temporary table,import data and change the yeargroup to a integer field
db.TableDefs.Delete tblname
DoCmd.TransferSpreadsheet acImport, 10, tblname, filename, True, ""
mysql = "ALTER TABLE " & tblname & " ALTER COLUMN [StudentYearGroup] int"
db.Execute mysql, dbFailOnError ' fails on this line. The MySQL line works here if run as a query
'delete the data in tbl_students
mysql = "DELETE * FROM tbl_students "
db.Execute mysql, dbFailOnError
'insert data from tbl_students_tmp into tbl_students altering "year 8" to "8" as a number
mysql = "INSERT INTO tbl_students ( LastName, FirstName, UPN, Mathematics_Group, StudentYearGroup )" _
& " SELECT tbl_students_tmp.LastName, tbl_students_tmp.FirstName, tbl_students_tmp.UPN, tbl_students_tmp.Mathematics_Group, Right([yrgroup],1) AS StudentYearGroup" _
& " FROM tbl_students_tmp;"
'Debug.Print mysql
db.Execute mysql, dbFailOnError
db.Execute "UPDATE tbl_update SET tbl_update.update_date = Date();"
mystring = "updated"
Else
'leave as it is
mystring = "not updated"
End If
Debug.Print mystring
tempreview_Exit:
Exit Function
Tempreview_err:
MsgBox Error$
Resume tempreview_Exit
End Function