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

RecordSet update insert error & sql insert syntax error

P: 46
Hi guys,

I have encountered this error when updating the values to the MS Acess table.
Error : Update on linked table failed. ODBC sql server error Timeout expired. MS Acess is my front end and sql server is my backend server.

This error occured whenever i step through and when it reaches rs.update it jux hangs down there and thereafter it shwn the aforementioned error. I am using the RecordSet properties to add the values. Can I use insert, update or delete in OpenRecordSet properties or just select stat only.

What i wants to achieve is to insert the value (userinput(textboxes), 1st combo box (defined-list), 2nd combo box (corresponding values from 1st combo box), listbox (corresponding values from 2nd combox). But i always get this error

Error : Update / Linked table [TableName' failed
[ODBC error][Sql server]timeout expired. I have been trying to resolve this error but to no avail. If would be very muxh appreciated if someone could provide a feasible solution to reolve the error.

I have set up necessay config for ODBC connection (System or File DSN so as to connect to sql server and config the external source under MS Acess for the linkage of table.

using recordSet. Add

Save command

Dim rs as RecordSet
Dim db as Database

Set rs = RecordSetClone
Set db = CurrentDB
set rs = currentDB.Open.RecordSet("Table Name")
rs,AddNew
rs!StudentId = Text
.
.
.
rs.update

AfterUpdate event

Dim rs as Object

Set rs = RecordSet.Clone
rs.FindFirst[SerialCode]= Str(Nz(Me![
If not rs.eof then Me.Bookmark = rs.Bookmark
Me.TextBox.value = ListBox1.Coulmn(0)

If I am using an sql query with the insert stat, hw can this supposedly to be used. syntax. I have tried using

uisng sql insert query

Dim sql as String

sql = "Insert into (table fields) values (fields values from the control) where
Error: sql insert syntax error.
DoCmd.RUNSQL sql (error: insert in linked table failed)
Jan 14 '07 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Please look in this thread and consider adding those items necessary to give an answer.
(POSTING GUIDELINES: Please read carefully before posting to a forum)
I tried to read through this and stopped after the fifth misspelled word made trying to understand a reasonably complex problem even harder.
Please proof-read this yourself before posting if you expect other members to help you.

MODERATOR
Jan 14 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Please read NeoPa's post. Because I don't know which are errors in the code I am just posting the code as it should be

Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2. Dim rs As Recordset
  3.  
  4.    Set db = CurrentDB
  5.    Set rs = db.OpenRecordset("Table Name")
  6.    rs.AddNew
  7.  
And this is just the first few lines.

Mary
Jan 15 '07 #3

Post your reply

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