Working with one to one related data

[JonathanBr]

Hello

I am newbie and really hope someone can assist.

I setup a "main" database with related tables. But there is one table with lots of information based on a primary field (a "curve"). I did create an autonumber field to drive new instances of curves.

A form was created with a lot of information uniquely related to that autonumber. However there was one field that has caused problems. We have an image for each unique curve, so it seemed a good idea to add an "attachment" type of field to store the jpeg. As it is unique one-to-one relationship, I thought that it would be fine a single field. And initially it is.

However, we want to have the data available to a different (secondary) database, via a linked table or query. This is where problems arise. If possible, my boss wants the data in the secondary database to be read only - which could be done with a query in the second database,, linking straight to the main database.

This has caused problems. As a newbie I did not know the attachment field is a Multi-value field. A table with a multi-value field cannot be referenced in an insert into query in another database. But if the attachment field is removed from the actual main database, that query will work.

But in the data entry form in the main database, I need to allow the jpeg to be referenced and added in. I will of course add a new table to the link - give it a field number and relate it to the autonumber in the existing table. But I'd like to not have a subform, (as the existing form is a split form anyway and works well for users. Furthermore, it seems pointless in creating a subform for 1 field. Afterall it has a one-to-one relationship with records in the main table.).

So I could create a multi-table select query to feed the form. that will be fine. But is that it? When I press the add button, will the same autonumber be populated in the main table and the related table for the images. Or do I need to do something with VBA to make that work?

Or will I need a subform?

Any help be much appreciated.

regards

 

[NauticalGent]

So I could create a multi-table select query to feed the form. that will be fine. But is that it?

The quick answer is yes, in fact this is usually how it is done. However sometimes when you do it this way, the underlying query is not updateable.

Why not simply add the attachment files to the main table?

 

[Galaxiom]

We have an image for each unique curve, so it seemed a good idea to add an "attachment" type of field to store the jpeg.

So long as you realise that embedding attachments in will rapidly increase the database size. Once your Access file reaches 2GB it stops working.

 

[JonathanBr]

Thanks for the replies.

Few points. Re the query - that is the key issue, I need the underlying query to be update-able and particularly that new rows are definitely added in both tables. The issue is that, as it is an multi-value field type, it cannot be referenced in a query in another database. If I didn't have the other database, this would not be an issue. So I am hoping someone can assist, maybe with some rudimentary code?

Re the image file size - yes it is apparent that the "attachment" is memory intensive - but that is the way to link as far as I understand it. Is there another way to have it stored that is not in a multi-value file type (in Access 2010)? In particular - the images are needed to be automatically generated in a report - so that has to be borne in mind.

Any further comments are much appreciated.

regards
J

 

[gemma-the-husky]

Multi Value fields are non-standard access add-ons.

Using them might save a tiny bit of development time, but can come back and cause problems later on, as you have found. If you need the mvf functionality, then the best solution might be to re-design the database to use standard features. THe idea underlying an mvf is actually fairly trivial. It's just a hidden one-to-many table with reduced flexibility, as far as I am aware. I have never used them though.

I think most of us would just store the location of the image file, and not embed it in the database. That avoids database bloat.

Finally, there is always an issue with read-only. Databases are by nature not read only, and it is actually quite difficult to make a database read only.

 

[JonathanBr]

Gemma.

Thank you. Now I have that, I may just change it to a hyperlink field and we reference the location.

The issue I have is whether a report will actually automatically display the relevant file... If you know if that will be ok, brill. But I will look for some oher explkanation here or in other forums...

Frustrating all this. Why can't Microsoft leave good things be

 

[The_Doc_Man]

We had "attachment" issues. Our solution was to just store the file link as a text field that pointed to a file in a specific sub-folder relative to the location of the shared BE file. Store an image as a JPEG or WMF or BMP or PNG, doesn't matter that much. Then have an image control. In the form's OnCurrent or the report's OnFormat, load the .Picture property of the image control with the name of the file.

By keeping the curve's image as a separate file, you can have an external file. Then, database size issues due to a large number of curve images become a thing of the past. When the file is outside the database, it's size doesn't count. If you have a backup regimen on your shared folder, you have ways of keeping them safer.

The problem is the need to have a read-only aspect of the files. If you wanted to make the files read-only but still wanted the ability to create new files, that is a real tickler because that is a direct contradiction of the way Windows file security works. The same would be true if you had the files embedded via OLE methods or attachment methods in any kind of container file, like a secondary database. If you want to create new records then it cannot be made READ-ONLY.

It WOULD however be possible to do this: Have a split database. Put the BE file in your shared area. Put the curve images in files in a sub-folder of the shared area. Make the FE file sensitive to who is using it on your network. Based on login information, have the critical forms NOT display a button that would allow creation of new records unless you were authorized to do so. For unauthorized folks, it becomes impossible to use the form to create a new entry.

You can look up LOTS of threads on the forum to describe how you secure a split FE/BE database to become role-sensitive. It's a bit of work to set it up but if you go through the work correctly, it will work like a champ.