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


LVL 1
Deshin13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.