Messaging Databse (deep breath)

[jr007]

Hmmm.
See chapter 14 page 594 in the excerpt at this link

As before, I suggest you determine exactly what you need and what is feasible.

Good luck.

Thanks very much jdraw.

I'll take a good look at that link - appreciate the thought.

Out of interest, what is your opinion on what, at the moment, seems to be a viable solution that plog has been kind enough to discuss?

 

[plog]

Out of interest, what is your opinion on what, at the moment, seems to be a viable solution that plog has been kind enough to discuss?

To be fair, what I've given you isn't a solution. It's just the table structure you need for this system. Implementation of what you want is going to so much harder than just laying out the tables.

I'm with everyone else--you shouldn't be using Access for this.

 

[jdraw]

My base question for plog's approach is -- How do you send messages?
I understand his table structure and it could be that some combination of the table structure and the class module could evolve to a "proof of concept". Again, depends on requirements and your programming skills (and resources)[feasibilty].

 

[jr007]

Thanks for all the information guys.

Plog, I'll re-visit the table layout and include 'MessageThread'. That sounds like it will be easier to manage programmatically too.

I understand this is far away from what a real solution should be, so I'm very grateful for the help.

 

[jr007]

So, whenever a completely new message is created it creates a record in both Messages and Threads. When subsequent messages are made in the thread those just add records to Messages and use the existing ThreadID. Then you just have to sort messages by DateCreated to get them all in order.

plog, would you mind clarifying this please?

When a new record is inserted, the program will add the new line to 'Messages', but if 'ThreadID' is a foreign key, won't this line need to be added beforehand?

If so, will I then need to use @@Identity to get the ThreadID from 'Threads' to add into the 'ThreadID' field in 'Messages'?

Lastly, for subsequent messages, how will they link to the correct ThreadID?

Sorry if I'm missing something - just want to be sure rather than copy and paste without knowing what I'm doing!

I understand if this is you right now plog: :banghead:

 

[plog]

When a new record is inserted, the program will add the new line to 'Messages', but if 'ThreadID' is a foreign key, won't this line need to be added beforehand?

Messages are children of MessageThreads, so you will always need a record in MessageThreads before you put a record in Messages. Only the very first message of a thread will create a MessageThread record--all the others are responses and their MessageThread has already been created. For responses you already know the
MessageThread, so when you add a new record to Messages the MessageThread value is already known.

 

[jdraw]

plog,

How does a message get sent from one person/division to another?
Have we dropped Access from possible/feasible candidates?

 

[plog]

How does a message get sent from one person/division to another?

No idea, I'm helping him with the table. In the table he has To/From fields.

Have we dropped Access from possible/feasible candidates?

Define 'we'. The original poster sounds like he's dead set for it.

 

[jr007]

plog

Thanks for your help. What I initially had, and what you mentioned above in relation to the 'Thread' table is working perfectly.

Mainly through program code, messages are sent/replied to quite nicely.

Thanks for helping me along my quasi-suspicious restricted path.

 

[jdraw]

jr007,

Could you tell us or show us some code related to how you send and receive messages?
It might be a good candidate for code repository since it has been difficult to find a messaging technique in Access.