Append Query Key Violations - Hairy One...

[cclambie]

Hi All,

I have a major hair puller...

I have a system whereby I have a Front End (FE) and back end (BE) for storing data etc.

I am appending data from the BE to the FE into a local table to work on it (checking it "OUT" so no one else can work on it, then back "IN" when finished...)

Problem is I am getting a key violation when I Append the Data back in.

I have tried:
- Removing all FKs
- PK is autonumber and should autogenerate when appending back in?
- removed all relationships and lookups in both BE and FE tables

I have an issue as I can't see how this can be happening to 90% of the records. I just Appended them from the BE to the FE 2 secs ago, why cant they go back to the BE?

This is the SQL I am running (in VB) to append it back

strSQL = "INSERT INTO TblFieldValueBE ( QuoteID, QuoteTypeID, SectionID, SubSection, FieldID, FieldValue, MemoFieldValue, NumberFieldValue, CalcFieldValue, [Date], UpdatedBy ) SELECT TblFieldValue.QuoteID, TblFieldValue.QuoteTypeID, TblFieldValue.SectionID, TblFieldValue.SubSection, TblFieldValue.FieldID, TblFieldValue.FieldValue, TblFieldValue.MemoFieldValue, TblFieldValue.NumberFieldValue, TblFieldValue.CalcFieldValue, TblFieldValue.Date, TblFieldValue.UpdatedBy FROM TblFieldValue;"

TblFieldValueBE.ValueID is PK in BE (Local Linked Table)
TblFieldValue.ValueID is PK in FE

Neither are Appended in the Check IN or OUT function?

I have flattened all the fields in both tables to be just Text Boxes, with Number formats (ie. QuoteID)
Did have a relationship, but deleted it.

Have looked at Update and Append Query posts to, that is not appropriate for what I want to do, I want to DELETE * Where QuoteID = X so the data that is appended is the latest Data in the BE that has been worked on, and to save 000s of fields being duplicated everytime a QuoteID is called from BE to FE to BE...

Would appreciate your help as it has me stumped. Have checked all Key Violation and Append posts on this site and googled it, and spoken to a few "experts" around here........

Thanks so much!

 

[Malcy]

Just a thought, but is one of the fields an autonumber? If trying to append back then you could have a problem.
That said I am not sure I like your concept but you presumably have your reasons.

 

[cclambie]

Hi Malcy,
The only Autonumber is the ValueID, which is not being appended, so shouldn't be an issue....one would presume.

I am going to try ADO.Addnew .Update this morning to see if similar issues crop up.

The reason for the concept is speed of data retrieval.
The Data is used in 10-15 calculations that grab 100s of records of data to calculate based on a TblCalcRule, so getting it from a local table I found halved the time to run the Calcs.
I would stay with a split BE/FE altogether if I could make my calcs run faster, but they take 2.5mins at the moment in that format, with FE data it only takes 1min.
Suggestion on better performance on this?

 

[cclambie]

Hi All,

Solution:
I think there was some underlying relationships left over from the table, even though I had deleted all FKs, and Relationships.

I copied the table, structure only, renamed the old one, and replaced it with new one.
Worked a charm.

????

Thanks for your help Malcy.
Suggestions on a better way to deal with this efficiency would be great.

Cheers

 

[Malcy]

Hi
Glad you got that bit sorted.
I worry a bit about the time on calculations. Is your network fully optimised and your server running on a decent processor?
The times seem to be extreme so I wonder if you should perhaps be looking at SQL server instead of Access for your BE perhaps. Alternatively looking at your calculations logic to see if it can be neated up. You could also perhaps move more to SQL stored procedures so they get processed on the server rather than across the network.
It sounds pretty horrendous.
I know I have one process on a client's system that is slow but that is at least down in part to their network and server access times.
Good luck

 

[cclambie]

Malcy,

Thanks for your comments, if you look back in, how long do you think it would take to re-write the procedures in SQL Server processes?
For someone with No SQL Server experience?

C