[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MS Access link table update query

Posted on 2016-09-20
12
Medium Priority
?
93 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 22
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 22
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 22
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 22

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 600 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 1400 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 22
ID: 41809214
you're welcome ~ happy to help
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

656 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