Another Search Record Not Found Message

[Learn2010]

DB1 is an Access db with a SQL back-end.

I have databases that go out into the field with some of the team. Here is how it works.

Data is exported from DB1 into DB2.

DB2 has more data added to it.

DB2 appends data to DB3, a holding db.

DB3 is linked to DB1.

When DB3 has a record and DB1 is opened, it runs a procedure on that data that updates some tables and appends new records to other tables in DB1. Let's use tbl1 for my example for the records that get appended.

I pull a record out of tbl1 to another table behind a form, let's use tbl2. If the record was created in DB1, I can update it with no problem. If I pull a record into tbl2 that came from DB3, it will not update. I can manually open and close the table and I get the "Search Record Not Found" message.

Does anyone have an idea why these records give this problem?

Thank you.

 

[JHB]

Do you've a space in one of the table fields?

 

[Learn2010]

I manually cleaned up the "long text" field and tried it again and I had the same result. So, I went into DB2 and copied the text from that field. I then opened DB1 and used the process for adding a new record to that person's records. I pasted that data into the field in that record. I got the same result.

It appears the issue is copying one long text field from one database to another database is presenting the problem. Any ideas?

 

[JHB]

Check if the fieldtype for the "long text" is set to "Memo" in MS-Access.
Only for testing - take a text with a length lesser as 240 and paste it into the field.

 

[Learn2010]

Solved. I used a Select query behind the form I used to edit the field. It was connected directly to the actual record. When the change was made it was directly to the record. I can do this here because the record belongs to one person only and only that person would be in the record. No record-locking was needed.

Thanks.

 

[AccessBlaster]

DB1 is an Access db with a SQL back-end.

I have databases that go out into the field with some of the team. Here is how it works.

Data is exported from DB1 into DB2.

DB2 has more data added to it.

DB2 appends data to DB3, a holding db.

DB3 is linked to DB1.

When DB3 has a record and DB1 is opened, it runs a procedure on that data that updates some tables and appends new records to other tables in DB1. Let's use tbl1 for my example for the records that get appended.

I pull a record out of tbl1 to another table behind a form, let's use tbl2. If the record was created in DB1, I can update it with no problem. If I pull a record into tbl2 that came from DB3, it will not update. I can manually open and close the table and I get the "Search Record Not Found" message.

Does anyone have an idea why these records give this problem?

Thank you.

I think a simpler approach would be to use temp tables in DB1. If you right click on a linked table, you have the option to make a (copy) local table.

When you are satisfied with the data in the temp table, you can simply append the changed data to the SQL tables.