append queries records with multivalue fields

[Dawie Theron]

I need to append a number of tables.

I make use of an APPEND QUERY and it works fine.

BUT, for one part of my application, I need to work with tables which
contain multi value fields (Attachments).

I could not find a way around that. Please help

Dawie Theron

 

[PieterLinden via AccessMonster.com]

I need to append a number of tables.

I make use of an APPEND QUERY and it works fine.

BUT, for one part of my application, I need to work with tables which
contain multi value fields (Attachments).

I could not find a way around that. Please help

Dawie Theron

Why would you want to use multi-valued fields? Why not just create a child
table and do it the right way? I remember a looong time ago when the
FileMaker folks finally realized that MVAs were a bad idea... lots of fun to
sort out. I definitely wouldn't recommend using a feature like that.

 

[John W. Vinson]

Why would you want to use multi-valued fields? Why not just create a child
table and do it the right way? I remember a looong time ago when the
FileMaker folks finally realized that MVAs were a bad idea... lots of fun to
sort out. I definitely wouldn't recommend using a feature like that.

You should have heard the meeting between MVP's and the Access development
team at the MVP summit a few years ago. Blistering... we almost got thrown
out, and I think some feelings were hurt on both sides, unfortunately.

I absolutely agree. Bad idea, bad misfeature, unnecessary and hard to work
around... but added because there was a requirement to interface with
SharePoint.

 

[Dawie Theron]

Thank you John and Pieter,

Yes, I certainly agree with you sentiments. Thing is, I am fairly new at
ACCESS (4 months) and the feature of being able to simply attach documents
directly to a field sounded like a good idea at the time.

I must say, it served me well for a while too. While I only used simple
queries...lol

Then I became a bit more confident with ACCESS and started to build more
complex queries. That is where the fun started. I only became awareof such a
thing as multi-vulue fields when the error messages popped up... After
some reading up and much frustration, I am beginning to understand why.

At first I thought it was merely a "old school purist" thing, but I now
understand that it is fundamentally not a good idea.

So, How do I search more than one table simultaneously if I cannot append?
Or alternatively, how do I save documents in a database table? This
application is for a document control system, but the same would apply if one
wants to build a simple photo album?

The child table idea does not seem to solve anything, because I will then
simply move the problem to another table if I attach documents there, or not?

Looks like stumbled upon a touchy subject here...lol

Your response are really much appreciated.

Please continue with this thread, because I am open for suggestions and
definately willing to abondon the attachment idea, but need something to
replace it with.

Cheers
Dawie Theron

 

[PieterLinden via AccessMonster.com]

Dawie,
Maybe it's time for more details. Otherwise, I'm not sure if I'm answering
your question. Some table has a MVA field where you can add several
documents. This is essentially a one-to-many relationship sort of packed
inside your table. But Access is already relational, so why not just break
that one-to-may relationship right now and put the individual multiple values
into another table? Why would that cause a problem with your data?

What are you searching for? You can create a query joining the two tables
(primary key from parent record to foreign key to child table) and then open
that or base a read-only form on that... or use a subform...

Why do you need to save documents in your database table? Are you protecting
them from modification?

The child table idea does not seem to solve anything, because I will then
simply move the problem to another table if I attach documents there, or not?

Okay... please explain what you are trying to do with the data. I can't tell
why the "child table idea does not seem to solve anything". How do you
intend to use the attachments in the child table?

Pieter