Solved

MS Access link table update query

Posted on 2016-09-20
12
61 Views
Last Modified: 2016-09-21
In Ms Access 2013 using entry form I am trying to enter new record in table that is linked table from SQL Server  and got the error:  Run - Time error 3146 ODBC -call Failed
Error popped up at recordset update line.

Set db = CurrentDb
Set rstExp = db.OpenRecordset("Select * from tblExpense", dbOpenDynaset, dbSeeChanges)
Rstexp.AddNew
……..
…….
……….
rstExp.Update <<<<<<error line.
0
Comment
Question by:Taras
  • 5
  • 4
  • 3
12 Comments
 
LVL 19
ID: 41807727
check to make sure that any required field is filled.  Also check Default Value and make sure that foreign keys are not getting filled with a 0 (zero), which probably won't match anything. Ensure that whatever is the Primary Key is indeed unique.

Instead of using *, it is a good idea to qualify it
Select tblExpense.* from tblExpense
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41807822
Is this code running on a Form in Access??

ET
0
 
LVL 19
ID: 41807826
> "In Ms Access 2013 using entry form I am trying to enter new record in table"
why not use the form itself to make a new record? Make sure the RecordSource property of the form is the table you want to add records to and then controls (ie: textbox controls) can be created to add, show, and edit information from the Record Source by setting the Control Source equal to a field in the Record Source of the form.

new records are added by clicking the new record button of the navigation controls in the lower left
Access Navigation - New Record ButtonYou can make bigger buttons on the form if desired to allow users to create a new record -- but first test the built-in way to ensure new records can be created. If not, open the table directly and try clicking the new record button there. If you still can't create a new record and edit values, then the issue is with permissions for the table connection or your privileges.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41807851
Ok, try this ... I had this problem a few years ago with a linked MySQL table.  This is why I wanted to confirm whether or not the code was running on a Form Object.

Make sure the [ID] or unique id field in the linked SQL Server is included in the record source of your Form.  I even went as far as including it on the Form then setting the Visible property to NO.

This eliminated my problem with the same error.

ET
0
 

Author Comment

by:Taras
ID: 41807918
Form that I use for record entry is not bound form.
I use form fields just to collect some users entry and add new record to talble tblExpense or to udated tblExpense.
 TblExpense Has primary key field that is auto number and is increased every time when new record is entered automatically. I do not include that field in RstExp.AddNew.
0
 
LVL 19
ID: 41807932
> "Form that I use for record entry is not bound form."

why is that? you can limit a form to just one record if desired. Let Access do the work!

> " I do not include that field in RstExp.AddNew."

while you may not choose to SHOW a field in a control, it is good to add it and set the Visible property to No (False) -- and in that case, color it differently like black background and white font so it is obvious to developers in design view.
0
 

Author Comment

by:Taras
ID: 41807944
Crystal something is not clear here, you or I am missing something.

When I am adding new record in Linked table through an unbound access form primary Key is not know when I open recordset (does not exist at that moment) it will be allocated when record is entered in table in this case when recoredset "update" action is performed.
 
In case when I edit(update existing) record in that case yes you can show primary key field on form but not in case when I am adding new record in linked table.

 I think you are talking about editing a record on Access  form and I am talking about adding new record.
0
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 150 total points
ID: 41807974
I am talking  about either -- it will be a lot less effort to bind the form.  You can set Data Entry to be yes so it will only allow new records and not pull the whole table down if you like -- but use what Access has built in.

did you try opening the table directly and creating a record? could you do it?
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 350 total points
ID: 41808127
Taras ... you said ... <<<<When I am adding new record in Linked table through an unbound access form primary Key is not know >>>

That is the problem and is why you are getting the error.  You are adding a record to a linked table on a different database management system (SQL Server) through an Access Form Object.  Without that unique [ID] field on your Form ... Access is having problems seeing/identifying the unique records in the SQL Server Table.  I had this problem with a MySQL Table and discovered this issue a while back.

Test your code against using a Form that is bound to a SQL Server table and include the unique [ID] identifier in the record source of your Form and on the Form.  Let me know what happens.
0
 

Author Closing Comment

by:Taras
ID: 41808783
Thank you etsherman and crystal it was primary key in access table that was not set as autonumber, at the time when we moved table on new server those primary key filed were not set up as indent = yes on SQL Server. When I changed it access linked table change it automatically to auto increment. You gave me some input where to look for solutions. Thank you both.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41809170
Yes, without an autonumber or unique ID field on that linked table Access will have some problems when adding new records on the front end.

Glad to help and thanks for the points.

ET
0
 
LVL 19
ID: 41809214
you're welcome ~ happy to help
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question