Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Easiest Way to Export/Import

Hi PPL

I have a question, I am export some data from 11 different tables, but its only the Primary Keys that I am actually exporting.

Its a little hard to explain but I am just going to give an example

Table1
Column 1 Value ---> 72
Column 2 Value ---> 59
Column 3 Value --> 'John'

Table2
Column 1 Value --->59
Column 2 Value --->77

Now because these numbers are generated by the system, they will probably exist in the target DB. So when I import, if I change Table1.Column2 Value to 85, I want an easy way to change Table2.Column1 Value to 85 also but I am working on a much larger scale.

I was thinking of using a Search and Replace function but then there are certain Columns that might have the same value and I might not want to change it. I was also thinking of using a collection, holding the old Value and the New Value, then check in the collection if a new value exists before I insert my record and then do the replace....

I am pretty sure that there is a better way to do this, I am just looking at a easy way to read and write... All my data once loaded is stored in an array of array, basically an array for each row in a table...

Any advice would be appreciated


0
Deshin13
Asked:
Deshin13
  • 2
1 Solution
 
PBuckCommented:
Do you have any control on how the data in Table1 gets added?  My first thought is to use Table1 as a 'Master' control table.  So when a new field gets added, a reference to Table2's location can be added also.

If you had Table1 designed something like;

FieldValue   table2ID
------------   -----------
72               190023
59               822212
John            

Where table2ID is a reference to Table1's autonumber or random (primary key).
Table2:

AutoNumber  FieldValue
--------------  ------------
190023         72
822212         59

If you have control over this situation, this is my first thought on how to ease this routine.  Good luck!
0
 
Deshin13Author Commented:
I was thinking about doing it something like that but holding it all in memory. Use a collection to hold the links between the old values and the new values....

I am going to try that out and see how it goes...
0
 
PBuckCommented:
Yeah that sounds like a plan.  Depending on the size of your project you may want to use an array of type versus a collection (faster than a collection).

Private Type DB_FIELDS
    FieldValue as String     'Updated/New value
    Table2ID as Long       'Record number from Table2
    Etc ...
End Type
Private XX() as DB_FIELDS

With XX(0)
   .FieldValue = "79"         'If you want to keep the last value handy
   .Table2ID = 1
End With

With XX(1)
   .FieldValue = "59"         'If you want to keep the last value handy
   .Table2ID = 2
End With
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now