Taras
asked on
MS Access link table update query
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.
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.
Is this code running on a Form in Access??
ET
ET
> "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
You 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.
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
You 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.
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
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
ASKER
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.
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.
> "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.
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.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
Glad to help and thanks for the points.
ET
you're welcome ~ happy to help
Instead of using *, it is a good idea to qualify it
Select tblExpense.* from tblExpense