By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

ODBC - Insert on linked table [tableName] failed issue

P: 46
Hi guys,

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.

Control
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

Private_Sub SaveCommand
On_Error ....

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.AddNew
rs("StudentId") = Me.text1.value
rs.Update
rs.close

End If

set rs= Nothing
set db =Nothing

Private Sub_AfterUpdate()

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
Jan 15 '07 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
I would have expected:

If rs.EOF and rs.BOF then
' no record found for WHERE criteria
rs.AddNew
rs("StudentId") = Me.text1.value
' All unique fields from the WHERE should be filled here so add also
rs("Course") = Me.Combo1.value
rs("Subject") = ....
'etc.
rs.Update
rs.close

End If

Nic;o)
Jan 16 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.