Solved

MS Access link table update query

Posted on 2016-09-20
12
43 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now