Database not working after adding attachement field

[Arjan]

Hello,


I new to this forum and signed in for the obvious reason... I have a problem.

I created a database a while ago which has been working just fine, until I wanted to make one addition: an attachement field to one of its tables.

If I do so, some queries don't work anymore and as a result of that, some (sub)forms do not work. The only error I get is: The search key was not found in any record.

When I add an other kind of field (like a text field), the problem does not occur
When I remove the attachement field again, everything works fine again...

Any suggestions?

Thank you,

Arjan

[June7]

I cannot replicate the issue. If you want to provide db for analysis, follow instructions at bottom of my post. Identify the table involved.

[Arjan]

Mmm, not sure I can get it down to 2Mb. My front-end is already over 1,5Mb.

These are the relationships in the Back-end.


It is a tooling database. A tool is related to an assembly. The same tool can be related to more then one assembly. One assembly may require more then one tool. Therefore I have made and Toolapplication table that stores these relationships.

Since many tools will be needed at multiple locations/assemblies at the same time, we have a tools master record (TblTools) and tool serial records (TblToolSerials). The serials are ther real tools, the master records only contain tool(design) information which is identical for all serials.

I want to add an attachement field to the TblTools, so I can store tooldesign data in the database.

[Arjan]

I can't post the db over here because I can not get the BE smaller than 6Mb, even without any records in it

[June7]

Still 6mb without records and after running Compact & Repair? That is really a small db in design and should not be so large without any records.

Alternatives for providing files:

1. backend with only the tables involved in issue

2. upload to fileshare site such as Box.com and post link to file

[Arjan]

Zipped it down and placed it here: http://www.mijnbestand.nl/Bestand-FTZYX87KW8IA.zip

The following queries do not work anymore after adding the attachement field

QryManageToolAssemblyRelations
This one is the most important one and is used to run the subform in FrmManageAssemblyToolRelations. When you remove the attachement field from TblTools you will see it starts working again.

QryShowToolAssemblyRelations_02
This one is to generate a small list (by clicking on the List Pictures button) from that subform that shows pictures from the toolsserials. I found out that when I remove the attachement field ToolSerials!ToolSerial-Thumbnail (which contains the tool serial pictures) from this Qry it works again. I don't know why that solves it, since that is a different table, not directly related to the TblTools.

I would be really great if you can find the cullprit this June7. It is driving me nuts...

[June7]

I opened both queries and the form, all working. I did have to open in Access 2010 because my Access 2007 would not recognize the file.

[Arjan]

Damn! You are right. I just tried an other PC and now it works! Now I only have to figure out how I can make it work on my own PC as well

[Arjan]

After updating the all the links in the FE to the tables in the BE, the database runs just fine again on my PC.

Thanks for your help June7!