I have been working on a dB that helps us track and replace assets. Our current process has been to create xls spreadsheets, send a spreadsheet to a specific client co-ordinator for a group and this person would validate all the data on the spreadsheet, making changes where needed and also adding new asset that we may not have been aware of. Because of the number of spreadsheets we have to maintain we decided to put this into a dB (Attached). So what we want to do now is to export the data we want the user to verify to a excel spreadsheet (I include the AssetID Key to relate existing records back to one another), they make their changes and send it back to us. Up to this point I have figured it out (With help, can not take credit)
The structure of the dB (looking back does not seem like it was the best method to have followed) essentially is one main table (tblAsset)with multipule tables related to it as lookup tables.
Based on this structure, when a user sends the spreadsheet back for importing, the VB would have to:
1. Go through each field from the spreadsheet that is related to a lookup table in the dB, if it finds a value that is not yet in the lookup table, it must add this value.
2. Then for each related AssetID in the spreadsheet, update the related records in the dB where changes has been made.
3. Then finally for each new record in the spreadsheet (New being any record that does not have a related AssetID) add/append a new record in the dB.
I have found this thread by WayneRyan which seems most similar to what I want to be able to do but I just can not figure out the code.
http://www.access-programmers.co.uk/forums/showthread.php?t=76375&highlight=Moisture+Data
I have included a link http://www.azulight.com/mdb/ for a copy of the dB, to check the export process in action, open form frmAssetMain, click on Export, select Desktop Deployment and All and then export. I have also included a spreadsheet with mock data to import.
Any help with the VB code will be appreciated..
The structure of the dB (looking back does not seem like it was the best method to have followed) essentially is one main table (tblAsset)with multipule tables related to it as lookup tables.
Based on this structure, when a user sends the spreadsheet back for importing, the VB would have to:
1. Go through each field from the spreadsheet that is related to a lookup table in the dB, if it finds a value that is not yet in the lookup table, it must add this value.
2. Then for each related AssetID in the spreadsheet, update the related records in the dB where changes has been made.
3. Then finally for each new record in the spreadsheet (New being any record that does not have a related AssetID) add/append a new record in the dB.
I have found this thread by WayneRyan which seems most similar to what I want to be able to do but I just can not figure out the code.
http://www.access-programmers.co.uk/forums/showthread.php?t=76375&highlight=Moisture+Data
I have included a link http://www.azulight.com/mdb/ for a copy of the dB, to check the export process in action, open form frmAssetMain, click on Export, select Desktop Deployment and All and then export. I have also included a spreadsheet with mock data to import.
Any help with the VB code will be appreciated..
