473,287 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

RecordSet update insert error & sql insert syntax error

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
2 4772
NeoPa
32,554 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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

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

Similar topics

2
by: Clark Savage Jr | last post by:
I am running a bunch of aggregate insert queries to populate a table. When I run the SQL statement via a saved query, all is well. However, when I run it as an ADO recordset in VBA, the count is...
1
by: Steve Leferve | last post by:
Hey folks I'm working on a 'duplicate' function. I need to duplicate related child records in the same subtable. Two questions: 1. My 'db.execute' function is not working. How can I trouble...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
22
by: Gerry Abbott | last post by:
Hi all, I having some confusing effects with recordsets in a recent project. I created several recordsets, each set with the same number of records, and related with an index value. I create...
3
by: Nathan Bloomfield | last post by:
Hi there, I am having difficulty with a piece of code which would work wonders for my application if only the error trapping worked properly. Basically, it works as follows: - adds records...
25
by: Neo Geshel | last post by:
This works: <form> <asp:TextBox id="name" /> <%= name.ClientID %> </form> But this DOES NOT work: <form>
2
by: explode | last post by:
I made nova oledbdataadapter select update insert and delete command and connection veza. dataset is Studenti1data, I made it by the new data source wizard,and made datagridview and bindingsource...
4
by: breadhead | last post by:
Good morning, all. I am creating an Access 2002 application to run on XP clients and I'm struggling with the task of testing whether a single-field record exists before allowing the user to add it...
3
by: jonathan184 | last post by:
The code seems to be working fine for some records but I am thinking it is finding some other records with special characters and so on. I am looking for a way to insert the xml string with escaping...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.