Unanswered: ODBC - Insert on linked table [tableName] failed issue
I am having trouble resolving this error. Below mentioned is my code and implmentation.
MS Acess is my front end and sql server is my backend.
What i want to achieve is to be able to insert StudentId in the table matching the values chosen in the combo boxes and listboxes. This sound more like searching the table to insert StudentId matching the criterias.
I have set the necessary configuration for ODBC connection (File DSN & System DSN>Add>Sql Server>) and linked the table from MS Acess (Get external source>ODBC Database>DataSource>System>Table[Student]) but somehow or rather it jux failed to work.
Is there a possibilty whereby i can combine RecordSet property (rs.AddNew) with sql stat (select stat) The prog just hang at rs.update when i step till it and resulted to [ODBC -insert on linked table [tableName] failed. Do I need to set any connection property for ODBC to sql server? If yes, hw can this be set.
Really at a loss as to hw this can be resolve!
Any solutions, suggestions, pointers, code snippets, correction of codes is very much appreciated.
StudentId - TextBox(Userinput)
Combo box1 (predefined values) course
Combo box 2 (populated results from Combo box 1) subject - combo change event
ListBox (populated result from Combo box 2) class - combo change event
Dim rs as DAO.RecordSet
Dim db as DAO.Database
Set rs = Me.RecordSetClone
Set db = CurrentDb
Set rs = db.OpenRecordSet("Select StudentId, course, subject, class from Student where studentId = '" & Me.StudentId.value & "' And course = '" & Me.Combo1.value & "' And subject = '" & Me.Combo2.value & "'" )
If not rs.EOF then
rs("StudentId") = Me.text1.value
set rs= Nothing
set db =Nothing
Dim rs as Object
Set rs = Me.RecordSet.Clone
rs.FindFirst "[Serialno] = " Str(Nz(Me![List1]))
If not rs.EOF then Me.Bookmark = rs.Bookmark