BE Append Query Error

[JC3]

I am wanting to run a query in my FE which takes data from a Linked DB and copies this to my BE for general user access. The query itself contains three tables with the final table being a Left Outer Join.

If I run this query to append to a table in my FE it runs fine. If I run it to append to the exact same table copied to the BE I get the error "Record is Deleted". If I then open the table there is nothing showing in it. If I then run a delete query on the table it find records to delete.

After some investigation I have discovered this happens whenever there is null data on the Left Outer Join table.

Does anyone know why this works fine appending to the FE but not the BE and more importantly how to resolve or work round this problem.

Many Thanks

JC3

 

[FoFa]

I would guess you table definition is different between the FE and BE.
Depends on if you want the null records, or not. If not, then exclude those that have no data on your left join. If you want them, you may have to change your table to account for the null field/s.

 

[JC3]

I can't see any difference between the Table Definitions. The table was a direct copy from the FE to the BE with no changes so should be identical in every respect except it is on the BE.

I have added another layer to work round this. i.e I am appending the data to a temporary file in the FE then running another Append Query on the temp file to the BE. A little bit clunky but at least it works until I can resolve the main issue.

Any ideas or suggestions appreciated.

JC

 

[FoFa]

OK, Going by what you are saying, sounds like there is a mismatch between the queries, and what you are expecting to see. For example, open the table and nothing shows, delete query finds records. So either you have some filter on your table, or your delete query is running against another table or you have a bunch of NULL only records and are ignoring the row count. Your kludge just points this out even more. Sounds like there are errors (at least in the way you think they should work) in your original queries. Also coping a table from one DB to another does NOT make them identical, but for your usage most likely that would not be the issue as columns and datatypes are copied intact. It's the "behind the scenes stuff" that does not always copy or copy correctly depending on the receiving DB.