Problem copying Table

[Middlemarch]

My DoCmd.CopyObject is failing with error


3709
The search key was not found in any record.

I've proved this to the table itself as an older version if it copies ok
I did at one time get the message One or more of the newly created objects contain a data type that isn't compatible with earlier versions of Microsoft Access.
This may be it as the target db is an mdb, but there's no accdb specific datatypes (that I can find).
And it does copy if I remove all the fields except the id autonumber.
Anyone have any debugging tips where I might see what the problem is ?
Thanks.

I have done a Compact & Repair. No change.

[isladogs]

That error usually indicates corruption in one or more records identify the corrupt record(s) and copy the table without those

[Micron]

removed - somehow ended up with a more or less duplicate post.

[Micron]

Try a compact/repair?
Also, make sure you don't have an index on a memo (long text) field.

[isladogs]

Access won't allow you to create an index on a long text field, at least not in current versions.
If it were ever possible, it would indeed be a bad idea.

[Micron]

Access won't allow you to create an index on a long text field, at least not in current versions.

Are you sure?

[isladogs]

Interesting.
I was sure when I answered!
I had checked it earlier using the Indexes item on the Table Design ribbon & it wouldn't let me do so. In fact any long text fields aren't listed there & it won't let you write them in.

However, you are correct that it will let you index long text fields from the field property list! Very odd!
I will follow up with a member of the Access team!

[Micron]

Well if it turns out to be an anomaly, can you convince them to name it the Micron Anomaly?
BTW, I was able to save that change to the table design.

[isladogs]

Its definitely an anomaly.
I've had an initial response from the Access team and will let you know the outcome when I can tell you more.

According to this help article, https://support.microsoft.com/en-us/...s%20to%20index, you can index long text fields though not OLE, calculated or attachment fields.
Nevertheless, as we both agree, its not generally a good idea to do so.

[isladogs]

I've now had a more detailed reply from Microsoft.

The Access team has acknowledged the inconsistency in behavior regarding indexes in long text fields and will make a decision on the best way of addressing that inconsistency.

Whether they do so by blocking indexing from the property pane, or by allowing indexing in the index dialog will require further investigation.
In the meantime, the previous advice given (in this thread) is worth reiterating – do not create an index on a long text field