Referential Integrity – Import of Data from a purchased package

We are building an Access application which is going to interface with a
purchased accounting system. This purchased system will be the source for
our “Part Info� (part number and part description). The only way to extract
info from this purchased system is to export all part information via a CSV
file which can be imported into our new Access system. This all works
nicely, except when it comes to referential integrity.

We would like to enforce RI between the Parts Table and our Orders table in
the new Access system. (The Part Number in the Orders Table is a foreign key
to connect to the Part Number (primary key) in the Parts Table)

Is there an easy way in which we can replace the entire contents of the
Parts Table daily and still maintain the RI between the Parts Table and the
Orders table?

I would venture a guess that others have run into similar issues as they
have tried to build an interface between an older system and a new Access
system.

Thanks in advance for your ideas.

--
Brad

Importing is always fraught with issues like this.

If your purchase system is generating new part numbers (but not modifying or
deleting existing ones), you could probably solve this by importing the CSV
into a flat-file table where you can maniuplate it before writing to your
real tables. You can then use the Unmatched Query Wizard to identify the new
parts, and programmatically add them to the parts table (recording the new
primary key value into the temporary table so you know what the actual order
was.) Now that all parts are in the real database, you can go ahead and
write the purchases to the appropriate tables, and then delete the data from
the temporary table. It does require some experience in handing recordsets
in VBA.

If the purchase system is editing and deleting existing parts, things get
more complex, because you cannot be sure that part XYZ in the purchasing
system still matches part XYZ in the database. For example, someone may have
deleted XYZ in the purhcase system, and then someone else may have created a
new part named XYZ.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brad" wrote in message
...
We are building an Access application which is going to interface with a
purchased accounting system. This purchased system will be the source for
our “Part Info� (part number and part description). The only way to
extract
info from this purchased system is to export all part information via a
CSV
file which can be imported into our new Access system. This all works
nicely, except when it comes to referential integrity.

We would like to enforce RI between the Parts Table and our Orders table
in
the new Access system. (The Part Number in the Orders Table is a foreign
key
to connect to the Part Number (primary key) in the Parts Table)

Is there an easy way in which we can replace the entire contents of the
Parts Table daily and still maintain the RI between the Parts Table and
the
Orders table?

I would venture a guess that others have run into similar issues as they
have tried to build an interface between an older system and a new Access
system.

Thanks in advance for your ideas.

--
Brad

Allen,

Thanks for the quick reply and for your thoughts.

Yes, in the Purchased Accounting system the users are able to add, change,
and delete part info.

Also, there does not appear to be a way of exporting only the changes from
this old system. Thus it appears that we will need to export all parts
unless we are willing to maintain the same data manually in two places.

--
Brad


"Allen Browne" wrote:

Importing is always fraught with issues like this.

If your purchase system is generating new part numbers (but not modifying or
deleting existing ones), you could probably solve this by importing the CSV
into a flat-file table where you can maniuplate it before writing to your
real tables. You can then use the Unmatched Query Wizard to identify the new
parts, and programmatically add them to the parts table (recording the new
primary key value into the temporary table so you know what the actual order
was.) Now that all parts are in the real database, you can go ahead and
write the purchases to the appropriate tables, and then delete the data from
the temporary table. It does require some experience in handing recordsets
in VBA.

If the purchase system is editing and deleting existing parts, things get
more complex, because you cannot be sure that part XYZ in the purchasing
system still matches part XYZ in the database. For example, someone may have
deleted XYZ in the purhcase system, and then someone else may have created a
new part named XYZ.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brad" wrote in message
...
We are building an Access application which is going to interface with a
purchased accounting system. This purchased system will be the source for
our “Part Info� (part number and part description). The only way to
extract
info from this purchased system is to export all part information via a
CSV
file which can be imported into our new Access system. This all works
nicely, except when it comes to referential integrity.

We would like to enforce RI between the Parts Table and our Orders table
in
the new Access system. (The Part Number in the Orders Table is a foreign
key
to connect to the Part Number (primary key) in the Parts Table)

Is there an easy way in which we can replace the entire contents of the
Parts Table daily and still maintain the RI between the Parts Table and
the
Orders table?

I would venture a guess that others have run into similar issues as they
have tried to build an interface between an older system and a new Access
system.

Thanks in advance for your ideas.

--
Brad

That's right.

Even if you use the same part number as the primary key of your Parts table
in your database (not an autonumber), this has the potential to mess up
existing orders. You need to design a system such that, if a part number is
replaced by a different part with the same number, you are still able to
figure out what the old part number was on old orders that used that number.
Messy.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Brad" wrote in message
...
Allen,

Thanks for the quick reply and for your thoughts.

Yes, in the Purchased Accounting system the users are able to add, change,
and delete part info.

Also, there does not appear to be a way of exporting only the changes from
this old system. Thus it appears that we will need to export all parts
unless we are willing to maintain the same data manually in two places.

--
Brad


"Allen Browne" wrote:

Importing is always fraught with issues like this.

If your purchase system is generating new part numbers (but not modifying
or
deleting existing ones), you could probably solve this by importing the
CSV
into a flat-file table where you can maniuplate it before writing to your
real tables. You can then use the Unmatched Query Wizard to identify the
new
parts, and programmatically add them to the parts table (recording the
new
primary key value into the temporary table so you know what the actual
order
was.) Now that all parts are in the real database, you can go ahead and
write the purchases to the appropriate tables, and then delete the data
from
the temporary table. It does require some experience in handing
recordsets
in VBA.

If the purchase system is editing and deleting existing parts, things get
more complex, because you cannot be sure that part XYZ in the purchasing
system still matches part XYZ in the database. For example, someone may
have
deleted XYZ in the purhcase system, and then someone else may have
created a
new part named XYZ.

"Brad" wrote in message
...
We are building an Access application which is going to interface with
a
purchased accounting system. This purchased system will be the source
for
our “Part Info� (part number and part description). The only way to
extract
info from this purchased system is to export all part information via a
CSV
file which can be imported into our new Access system. This all works
nicely, except when it comes to referential integrity.

We would like to enforce RI between the Parts Table and our Orders
table
in
the new Access system. (The Part Number in the Orders Table is a
foreign
key
to connect to the Part Number (primary key) in the Parts Table)

Is there an easy way in which we can replace the entire contents of the
Parts Table daily and still maintain the RI between the Parts Table and
the
Orders table?

I would venture a guess that others have run into similar issues as
they
have tried to build an interface between an older system and a new
Access
system.

Speaking more from manufacturing/engineering management standpoint, if you
have people messing with part numbers to the point of re-using or re-defining
the same part number for a completely different part, you don't even have
working part numbering system nor part numbers that have a meaning, and,
databases aside, you are sunk before you start.


- - - - - -

Is there any chance that your accounting system might provide access to
it's part table, at least on a read-only basis? If so, you you might be able
to real time link your Access application to that table. That's what we
did with a commercial CRM system before we ditched the commercial one. Less
likely to be accessible with an accounting package, but it's just an idea.

Fred,

Thanks for your thoughts.

I like the idea of accessing the parts table directly in the old purchased
accounting system. Unfortunately, this is not possible. This old system
uses a proprietary embedded DB and the only method that is available to get
at the data is via an export facility that creates a CSV file.

It appears that we are faced with importing this file on a daily basis into
our new Access system in order to avoid maintaining the same data in two
systems. We would like to find a way to do this import without messing up
the RI.


Brad


"Fred" wrote:

Speaking more from manufacturing/engineering management standpoint, if you
have people messing with part numbers to the point of re-using or re-defining
the same part number for a completely different part, you don't even have
working part numbering system nor part numbers that have a meaning, and,
databases aside, you are sunk before you start.


- - - - - -

Is there any chance that your accounting system might provide access to
it's part table, at least on a read-only basis? If so, you you might be able
to real time link your Access application to that table. That's what we
did with a commercial CRM system before we ditched the commercial one. Less
likely to be accessible with an accounting package, but it's just an idea.

My gut feel:

Make and enforce rules for data entry for part numbers in your accounting
package including:

Never delete or modify a part number
Never fundamentally change the description of a part number. Small tweaks
are OK

Also, I assume your accounting package keeps duplicate part numbers from
happenning.


Then export / import the whole parts table daily, as you describe.
Automate it as much as possible.

Hi Brad,

I am thinking because of the problem of changing part numbers you may
need to go about it in a different way. Since you cannot automatically
capture when someone has deleted part XYZ and someone else has added it
afresh, you are going to make your import process smart. This means some
additional fields and some coding. Here is what I might do:

Add to your parts table in Access a field Part_ID field that is an
autonumber field. Make this the primary key. Also add a field named
Effective_Date that is a date field. Use it, along with your part number
field to make up another unique index. When you get a new export/import file
from the legacy system, create a link to it instead of importing it. Then in
code read through all of its records. For each record read, check to see if
the part number already exists in your Access table. You will want to look
only at the latest one as defined by its Effective_Date. Something like
"select * from Parts where Part_Number = 123 order by Effective_Date desc"
will provide you the latest record as the first record of a recordset with
that part number. If you do not find any records, add it to the Parts table
with the current date for the effective date. If you find any records,
compare all of the fields in the import data with all of the fields in the
first record of the recordset to see if anything has changed. If not, there
is nothing to do, go on to the next import record. If there were changes add
a new record with the current date for the Effective_Date. You will never
delete any records. This way, you will always have an exact record of what
someone ordered, when they ordered it.

Now, in your ordering form you will have to pick parts based on the
order date in comparison to the effective date. So, only parts with an
Effective_Date less than or equal to the order date, and that do not have
newer records. You will store the Part_ID in your order items table. So if
you have this in your table:

Part_ID Part_Number Effective_Date
1 123 02/02/2002
2 123 03/03/2003
3 123 04/04/2004

And your order date is 07/07/2003, you would only allow the part with
Part_ID 2 in that order.

Hope that makes sense and is helpful.

Clifford Bass

"Brad" wrote:

Fred,

Thanks for your thoughts.

I like the idea of accessing the parts table directly in the old purchased
accounting system. Unfortunately, this is not possible. This old system
uses a proprietary embedded DB and the only method that is available to get
at the data is via an export facility that creates a CSV file.

It appears that we are faced with importing this file on a daily basis into
our new Access system in order to avoid maintaining the same data in two
systems. We would like to find a way to do this import without messing up
the RI.


Brad

Fred and Clifford,

Thanks for sharing your ideas. I really appreciate it.

Brad


"Clifford Bass" wrote:

Hi Brad,

I am thinking because of the problem of changing part numbers you may
need to go about it in a different way. Since you cannot automatically
capture when someone has deleted part XYZ and someone else has added it
afresh, you are going to make your import process smart. This means some
additional fields and some coding. Here is what I might do:

Add to your parts table in Access a field Part_ID field that is an
autonumber field. Make this the primary key. Also add a field named
Effective_Date that is a date field. Use it, along with your part number
field to make up another unique index. When you get a new export/import file
from the legacy system, create a link to it instead of importing it. Then in
code read through all of its records. For each record read, check to see if
the part number already exists in your Access table. You will want to look
only at the latest one as defined by its Effective_Date. Something like
"select * from Parts where Part_Number = 123 order by Effective_Date desc"
will provide you the latest record as the first record of a recordset with
that part number. If you do not find any records, add it to the Parts table
with the current date for the effective date. If you find any records,
compare all of the fields in the import data with all of the fields in the
first record of the recordset to see if anything has changed. If not, there
is nothing to do, go on to the next import record. If there were changes add
a new record with the current date for the Effective_Date. You will never
delete any records. This way, you will always have an exact record of what
someone ordered, when they ordered it.

Now, in your ordering form you will have to pick parts based on the
order date in comparison to the effective date. So, only parts with an
Effective_Date less than or equal to the order date, and that do not have
newer records. You will store the Part_ID in your order items table. So if
you have this in your table:

Part_ID Part_Number Effective_Date
1 123 02/02/2002
2 123 03/03/2003
3 123 04/04/2004

And your order date is 07/07/2003, you would only allow the part with
Part_ID 2 in that order.

Hope that makes sense and is helpful.

Clifford Bass

"Brad" wrote:

Fred,

Thanks for your thoughts.

I like the idea of accessing the parts table directly in the old purchased
accounting system. Unfortunately, this is not possible. This old system
uses a proprietary embedded DB and the only method that is available to get
at the data is via an export facility that creates a CSV file.

It appears that we are faced with importing this file on a daily basis into
our new Access system in order to avoid maintaining the same data in two
systems. We would like to find a way to do this import without messing up
the RI.


Brad

Hi Brad,

You are welcome!

Clifford Bass

"Brad" wrote:

Fred and Clifford,

Thanks for sharing your ideas. I really appreciate it.

Brad