ODBC - Insert on linked table [tableName] failed issue

[technocraze]

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

[XLGibbs]

Are you trying to update the SQL server table via Access?

The recordset in Access itself is not updateable, you must send the update to the SQL server.

Assuming you had write permissions on the SQL Server you would build your connection to the database and execute the appropriate SQL to add the record.

I do not believe a linked table would ever allow updates directly in the Access front end in this method.

I will log onto my work PC and grab the code I use to do this (although i do it from a VB6 program, the code will be similar...so you might be able to apply it. )

Will post back shortly with it.

[XLGibbs]

Well, my code is a little more complex than you need, but regardless, what you want to do is create an ADODB connection to the database.

dim cn as Connection, connstr as string, strSQL
Set cn = CreateObject("ADODB.Connection")
connstr = "DSN=databasename;UID=username;PWD=password"
'the DSN would be the appropriate ODBC connection on your PC
cn.Open connstr

strSQL = "Insert Into [Table] ({Columns}) Select {Values}"


' the values inserted must be in the order of the columns assigned
' so to insert for example name and phone number might look like
' "Insert Into [TableName] (Name,PhoneNumber) Select 'Smith,Joe','111-555-9999' "

'make sure to encapsulate string data in single carots '
cn.Execute strSQL
cn.Close




HTH.

[technocraze]

Hi XLGibbs,

First of all, thanks for yr prompt reply

Yes, updating the SQL server table via acess (Acess front end, sql server backend).

I have tried the following but encountered an error. I am using Windows NT authentication(tustedConnection)

connStr = "Server = ServerName;DSN = DataSource Name; Database = DatabaseName; TrustedConnection=yes"

Code:
strSQL = "insert into [databasename.tablename] (Fieldname) values (ControlName) Select (Fieldnames) from [databaseName.TableName] where matching criteria (correspoding values from combo1,combo2,Listbox) but to no avail. I do knw that object name is referring to tableName and my table name is correct.

Error: when executing SQL query
[Microsoft ODBC]{Sql Server]Object name not found "Table name"

[XLGibbs]

If it is SQL 2000, you would need databasename.dbo.tablename

SQL 2005 does not require the dbo (or database owner) prefix.

[technocraze]

Seriously have no idea what could be the potential problem that resulted to this error. I have verified already the information are all correct. Really at the loss as what exactly is required to resolve this issue.