Best practice for synchronizing related table?

[sparky961]

I have a database that gets automatically updated by an external application (AccessBooks RT). Currently, the application is set to replace existing records. This means that any fields I add to the tables are lost during an update.

It has been suggested to me to add tables that contain the additional fields, and link them back to the original tables. Since these tables won't be automatically updated, the data will persist untouched.

The problem, however, is how to ensure that for every record in the original table, I have a matching record in the linked table.

For example, I need a field to indicate whether a label has been printed for a particular line item (salesorderlinedetail) of a sales order (salesorder). I want to make another table, let's call it "_salesorderlinedetail" that's linked back to the salesorder table in the same way as salesorderdetail. For every record in salesorderlinedetail, there must be a corresponding record in _salesorderlinedetail.

A few points to note:
- Database is split into two parts - FE and BE
- Multiple users (3)
- Automatic update can happen at any time

Some pointers in the right direction would be appreciated greatly!

Thanks,
-Sparky

 

[Guus2005]

...
The problem, however, is how to ensure that for every record in the original table, I have a matching record in the linked table.

...

The answer is don't. Only store records for the additional changes you make.

Use Left (or right) outer join queries to show empty fields for the records that don't have a match.

For more information please post a sample database.

HTH

 

[sparky961]

Guus,

At this point, the database is massive and it contains sensitive information. If I need to in the future, I might be able to condense things into a more portable generic format that I can post.

Can you tell me how Access will display checkboxes using the method you suggest? For example, when a new salesorderlinedetail record is added, there will be no linked field for IsLabelPrinted (in a separate table). Using a bound checkbox on a form, can I expect that the linked field will be created after being checked/unchecked on the form? Or, will I need to add some supporting code to ensure that this takes place?

Thanks,
-Sparky

 

[Guus2005]

if you are using a bound form, i suggest that you fake the result of the query for the checkbox, in case it is null:

select table2.IsLabelPrinted from table1 left outer join table2 on table1.id = table2.id

When there is no corrsponding ID in table2, due to the left join, the result will be null. When it is NULL, replace the NULL by false:

select Nz(table2.IsLabelPrinted,False) from table1 left outer join table2 on table1.id = table2.id

The checkbox is grayed out when it displays a NULL value.

HTH

 

[sparky961]

Maybe I'm missing something, but how do you check the checkbox in order to store the result, if it's greyed out?

Thanks..
-Sparky

 

[Guus2005]

Maybe I'm missing something, but how do you check the checkbox in order to store the result, if it's greyed out?

Thanks..
-Sparky

That doesn't mean that its disabled or locked. It means that it displays a value it can't display. It's not TRUE or FALSE its NULL.

Try it and you'll see. Its quite useless.
When clicked once, you set the value to TRUE or FALSE.

Ofcourse this is only when the property Tri-state is set to false. which it usually is.

HTH

 

[sparky961]

Ok, I decided that if this works it'll surely be the easiest implementation. I'd love to get a solution working that doesn't require additional VBA code. That said, it doesn't seem to be working the way I'm using it.

Here's the query I'm basing the main form on:

SELECT salesorder.PONumber, salesorder.RefNumber, salesorder.DueDate, salesorderlinedetail.CustomField6, salesorderlinedetail.ItemRef_FullName, salesorderlinedetail.Desc, salesorderlinedetail.Quantity, salesorder.CustomerRef_ListID, salesorderlinedetail.IsLabelPrinted, salesorderlinedetail.CustomField7, salesorder.TxnDate, customer.FullName, salesorder.IsToBePrinted
FROM (customer INNER JOIN salesorder ON customer.ListID=salesorder.CustomerRef_ListID) INNER JOIN salesorderlinedetail ON salesorder.TxnID=salesorderlinedetail.IDKEY
WHERE (((salesorderlinedetail.Quantity)>"0") AND ((salesorder.IsToBePrinted)=-1) AND ((salesorder.IsFullyInvoiced)=0) AND ((salesorder.IsManuallyClosed)=0))
ORDER BY salesorder.DueDate, customer.fullname, salesorder.PONumber, salesorderlinedetail.CustomField6;

I start to lose track of things when they get convoluted like this. I tried inserting the snippet that you gave in various ways, but kept getting syntax errors. Would you be so kind as to show me how it would fit into this?

I sincerely appreciate the assistance!

Thanks,
-Sparky

 

[sparky961]

Still working on it, and here's a version that's a bit more organized for those like myself that need some spacing and formatting to "get it"...

SELECT
salesorder.PONumber,
salesorder.RefNumber,
salesorder.DueDate,
salesorderlinedetail.CustomField6,
salesorderlinedetail.ItemRef_FullName,
salesorderlinedetail.Desc,
salesorderlinedetail.Quantity,
salesorder.CustomerRef_ListID,
salesorderlinedetail.IsLabelPrinted,
salesorderlinedetail.CustomField7,
salesorder.TxnDate,
customer.FullName,
salesorder.IsToBePrinted,
Nz([_SalesOrderLineDetail].IsLabelPrinted, False)

FROM
((customer INNER JOIN salesorder ON customer.ListID = salesorder.CustomerRef_ListID) INNER JOIN salesorderlinedetail ON salesorder.TxnID = salesorderlinedetail.IDKEY) INNER JOIN [_SalesOrderLineDetail] on [_SalesOrderLineDetail].TxnLineID = salesorderlinedetail.TxnLineID


WHERE
 salesorderlinedetail.Quantity>"0" AND
 salesorder.IsToBePrinted=-1 AND
 salesorder.IsFullyInvoiced=0 AND
 salesorder.IsManuallyClosed=0

ORDER BY salesorder.DueDate, customer.FullName, salesorder.PONumber, salesorderlinedetail.CustomField6;

This gives me a type mismatch error when I try to run it. What am I doing wrong here? Is this the right way to do the join as described?

Thanks..
-Sparky

 

[Guus2005]

This is a totally different question. You should post it in a new thread.

Usually a type mismatch error means the the type before and after an equal sign (=) are not the same. Search your JOIN and the WHERE clause.

HTH

 

[sparky961]

Well, I don't have it working yet, but I do appreciate your guidance to this point.

I'll keep plugging away, and researching...

Thanks,
-Sparky

 

[sparky961]

I'd really love to put this thread to rest... but I've thought of another catch to this. Even if I'm able to get the query to work properly (which I haven't, as of yet), what's going to happen to the records in my new table(s) when the updater goes and deletes the related record? Seems to me that I'd need to have "enforce referential integrity" turned on in order to avoid having orphaned records, and when the updater deletes the related record, the record in my new table will also be deleted.

Am I seeing this the right way?

Thanks..
-Sparky

 

[Guus2005]

Table relationships are very usefull when you don't want to do it all yourself.
Not me. I like to do it all myself. So i know what happens and when/how.
Orphaned records i have to delete myself.

But Access can do it for you. Like you described. Yes i believe that you get it.

HTH