If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Referential Integrity – Import of Data from a purchased package
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 |
#3
|
|||
|
|||
Referential Integrity – Import of Data from a purchased packag
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 |
#4
|
|||
|
|||
Referential Integrity – Import of Data from a purchased packag
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. |
#5
|
|||
|
|||
Referential Integrity – Import of Data from a purchased packag
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. |
#6
|
|||
|
|||
Referential Integrity – Import of Data from a purchased packag
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. |
#7
|
|||
|
|||
Referential Integrity – Import of Data from a purchased packag
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. |
#8
|
|||
|
|||
Referential Integrity – Import of Data from a purchased packag
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 |
#9
|
|||
|
|||
Referential Integrity – Import of Data from a purchased packag
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 |
#10
|
|||
|
|||
Referential Integrity – Import of Data from a purchased packag
Hi Brad,
You are welcome! Clifford Bass "Brad" wrote: Fred and Clifford, Thanks for sharing your ideas. I really appreciate it. Brad |
Thread Tools | |
Display Modes | |
|
|