Problem adding new records to SQL backend #deleted values (1 Viewer)

[susand]

Question - Problem adding new records to SQL backend #deleted values

I have an Access 2007 db which has been moved off a sharepoint server and the tables have been moved to SQL. I have created an odbc connection which works fine.

When I add new records to the form and click on the tab control or save on the form, I get #deleted showing in the fields on the form. When I close the form down and open up on the correct record they display. I need the values to refresh and display correctly when clicking on the tab control and to bring back the ID number so this can be used on the form. The fields on the form are bound to the table.

I have searched the web on sql rown being successfully committed but will not retrieve and had no luck, the same with the #deleted value showing.

In other db's that I have setup with SQL backend this has not been an issue. Any ideas please I thought this would be an easy conversion and it is turning into a nightmare.

 

[Guus2005]

SQL server is equipped with triggers. Since you're using a MSSQL backend you should check whether a trigger was fired and why. #Deleted happens when the frontend was refreshed and the shown records where deleted. If you didn't do that you perhaps a trigger was fired.

HTH

 

[susand]

Thank you.

The record stays in SQL but it shows with a note in the column that the row was successfully committed to the database, however a problem occured when attempting to retrieve the data back after the commit. If I close the form down and open it up the record appears okay. I have been looking every where for a solution to this. I am not that SQL savy and assume it is some settings on the db

 

[Guus2005]

I believe that the problem is located in your form.
Are you sure that the record you see is the one in the database?
Perhaps you are looking at the wrong information. It's a little hard to tell.

Are you using bound forms?

HTH

 

[susand]

I have been doing a lot of work around on this just to get it going.

In answer to your questions, yes the form is bound to the table and the fields are bound.

What I have had to do is setup a button on the Main Form which opens up this form, instantly does a SQL insert into the table, then refreshes the form and goes to the last record - as it was a new record. Ugly but it is the only way I can get it to work.

I also noted that prior to doing this, after the data was entered and it displayed the #deleted in the fields, if I closed the form down and opened it up again the record was there with the correct values.

I would love to find out what/why is the problem as I am not happy with the above resolution.

 

[Guus2005]

I can't figure out what happens. There must be something else.

There is another way which works like a charm and never presents you false information.

Use unbound forms. Then you don't have to do any inserts before opening a form to show an empty record.

This is how it works:
All the fields on the form are unbound, the form is unbound and starts rather quick. In the OnOpen- or OnLoad event you put the code to populate the fields.
If it is a new record you can skip this part and show the empty fields.
Add a save button to let you confirm the changes you made on the form.

HTH

 

[susand]

Thanks for your help. I was trying to avoid re-coding the db. It was given to me three quarters done and was working fine on sharepoint. Then they moved the tables to SQL and it all turned pear shape and of course wanted it back quickly.

I have noted that even if I enter data directly into the sql table and don't tab through all the fields to the end of the row it happens. If I enter data and tab to the end of the row it doesn't happen. Also have noticed that none of my default values on fields populate on the first save.

I will keep trying options and keep all posted

 

[Guus2005]

It seems like a SQL Server problem to me. Which version do you use?

I will keep trying options and keep all posted

I would like that very much. Thanks.

 

[susand]

Yes I think it is something to do with the sql db setup - version is 2005. I am going to create a new db and see if I get the same results. The SQL db was sent to me already built but I have not had an others do this so finding the setting/difference is going to be fun.

 

[susand]

Okay we have progression of some sort. I created a new SQL backend with one table. Fields setup the same as the original db and table.

I created a new form in the db based on the new db/table.

When I create a new record after the form is open it saves the data to the underlying table and displays the values in the form. When I then go and create another new record and save I get the #Deleted showing.

If after the first save I close the form down and open it up again and create a new record it saves and displays values. None of my other front ends require me to close the form down.

Please all and any ideas

 

[Guus2005]

So the problem remains.

Seems that the error is in your form. Could you post your application or a sample describing the problem?

HTH

 

[susand]

Hi and thanks for the interest. I have cracked it with a work around and while I would still love to know what the problem was I am feeling much better now. Below is the work around and also hopefully a good description of what was happening if anyone every does stumble on an answer.

Work around.
I created a new table in the access front end - same fields and data type as the sql table. Upsized the table to the SQL backend and inserted all the FK's. Linked this table back into Access and changed the source on the form and amazing, no more #Deleted.


Problem.
Tables originally created the Sharepoint then moved to SQL. Form was working well on Sharepoint. After the move to SQL, when the form was first opened, a new record could be created and it re-displayed correctly in the form, if you did not exit the form but then went to create a new record or even naviage to other records and then create a new record, when you saved this record-moved to another record or to one of the tabs on the form, all the fields showed as #Deleted. If you then closed the form, re-opened it the record was there with it's values.

Just as a note, I created a new SQL db with table setup as above and changed the source on my existing form, same problem. On the tables I created in access and upsized to sql not a problem.

Thanks for all your help and maybe one day someone will find this and I will hear some more. If you are interesting in digging further I can look at posting app - I have never done this so would need some help.

 

[Guus2005]

compact and zip your application then attach it to the post using the paperclip. That's it.

 

[Banana]

The Jet/ODBC whitepaper, which is linked in the Beginner's Guide to ODBC discusses to some lengths about causes and resolutinos for #Deleted. See if either the guide or the whitepaper help give you some ideas where it may be coming from.

 

[susand]

Fantastic people. Here is the db and sql db. I have left the original tables in the db along with my new ones. The offending forms we I am going to go through the ODBC doc. As I am just beginning this type of dev I would rather find out now what is happening so I don't run up against this again.