Design issue (gurus wanted)

Hi,

I've got a access XP database.
In this DB there are 2 tables:
- CustomerContact (keeps track of all the contacts we've had with a customer)
- Actions (keeps track of all the actions needed to satisfy the customer)

As you can imagine, 1 customer can have multiple entries in CustomerContact, and 1 entry in CustomerContact can result in more than 1 action.

To have a proper form in Access I'm writing a activeX component that displays all the CustomerContact and Actions for a specific customer.

To create this activeX and to fill it with data is no problem.
The problem occurs when trying to add or modify data.
I was thinking of creating a 'dirty' property for each CustomerContact, that can be set to true or false.
Now I want to have a proper connection with the database that allows me to update the record or to create a new record whenever it's needed.

Does anyone have some suggestions on how to sort this problem.

Thanx.

Table of customers
Table of "contacts with customer" via customer ID
Table of Actions via "contact with customer" id

Proper connection?
You mean bound or unbound?

You could have this on one form or several forms.
The topp section would deal with the customer (use a search option to list those already on record and select on (which then holds the id) or create one (which returns an ID). Option to use a pop up form for data entry. Use a couple of labels for the selected Customer.
Second section could be a list box with all the contacts of that customer so far (empty initially). Button to add a new contact, which goes to a pop up form for data entry. When the form closes the list box is updated and displays the contacts. Use a select option again or just the highlighted row to refresh the third section. Another list box which holds all the actions for the selected contact. Pop up form again.


Or you could use a tabbed form, three tabs with the same options as above, but no pop ups as you could put each of the controls on the tabs.

Or three forms Linking to each other (one opens the next etc...)

Just some options there are loads out there, and you wouldn't need to go into creating an Active X control unless you were going to use this inside another form of som sort? Making life more difficult?

I've been spending some time in making things work in Access like I'd like it to see, but that didn't work out.
Furthermore I had a activeX that already solved a number of issues, so I just modified that one.

The data is stored in the backend, the forms, queries and reports are in a frontend db.
I can connect directly to the tables or use a query to return the entires. Both of them have some (dis)advantages.
I have implemented a 'dirty' flag, so when a user modifies data in a field, the set is flagged. If a user creates a new item, the new item is created in the activeX and flagged 'dirty'. When the user enters another set the activeX checks if the exited set is dirty, if so the DB is updated.

What kind of connection would you prefer to request the data and to update the records.?

ADO

Its the most flexible although you may get slightly better performance with DAO as that was made for Access.

Anyway, ADO, 'cause if you need to upgrade the back end to something better than Access, you only need to change the connections string, and perhaps some queries, rather than rewriting the lot.

More secure is queries (so say quite a few people on here) but they are running on SQL Server mostly, although it may be a good idea to start using that for future job options.

I'd say do what you feel is best. After re-reading your post it looks like you are using Bound forms and using a dirty flag to say changes (with user concent?) So whichever method suits your purposes.

I personally prefer unbound forms and opening recordsets via code to fill forms or update tables. They are slower, but provide me with further options.

Queries for form data saving are a good idea, but I found that you'd need to specify all the parameters and the opening of a recordset is easier and looks better in the code (Ok so it s more lines but well its your choice).

Thanks for the reply.

I used to work with Oracle, in VB I created the forms (so the DB is purely a datacontainer) In there I used also ADO, and it served very well.
For this job I'm upgrading an existing DB, and I don't want to interfere too much in the existing structures.
If I could do it all over I'd know the way to go, but ....

I think I'll go for ADO, it's fimiliair to me, and it's possible to upgrade the DB at a certain point.

Thanks for the help.