Results 1 to 8 of 8
  1. #1
    Col245 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    May 2019
    Posts
    5

    Copying data from one Table to another in vba where the format is different

    I am trying to find an Automated way of copying my data from an input table to a master table in access. The problem I have is that the input table changes daily and does not always have the same number of fields, (all of it’s fields are in the master table) the master table is permanent in its design.



    Any ideas please. I tried using queries but with the input format changing they don’t work

    Thnx

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why would source structure change? Are the field names consistent, just not always included? If the names are the same as in the master, open recordset objects and loop through source fields and save to corresponding field in master. Example:
    Code:
    Dim rsDataSource As ADODB.Recordset
    Dim rsDataDest As ADODB.Recordset
    Dim j As Integer
    Set rsDataSource = New ADODB.Recordset
    Set rsDataDest = New ADODB.Recordset
    
    rsDataSource.Open "SELECT * FROM [source table];", CurrentProject.Connection, adOpenStatic, adLockPessimistic
    rsDataDest.Open "SELECT * FROM [dest table] WHERE 0=1;", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    Do While Not rsDataSource.EOF
          rsDataDest.AddNew
          For j = 0 To rsDataSource.Fields.Count - 1
                rsDataDest.Fields(rsDataSource.Fields(j).Name) = rsDataSource.Fields(j)
          Next j
          rsDataDest.Update
          rsDataSource.MoveNext
    Loop
    If corresponding fields do not have same name, then need a 'map' table that associates field names. Code would do a lookup to the 'map' table to retrieve the corresponding master field name.
    strDestField = DLookup("DestField", "Map", "SourceField='" & rsDataSource.Fields(j).Name & "'")
    rsDataDest.Fields("[" & strDestField & "]") = rsDataSource.Fields(j)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Col245 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    May 2019
    Posts
    5
    The input table may include values against date potatoes, tomatoes, cucumber, strawberries one week but the next only have date potatoes and cucumbers. These values in a new row need to be added to the master. If “business objects” program only sees date, potatoes and cucumber it will not export a field for the others but they will still be in the master table. Hence the fields in the input table has changed from the original input table.The names of fields will not be different from those in the master
    Last edited by Col245; 05-24-2019 at 02:11 PM. Reason: Clarity

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Did you mean "data" instead of "date"?

    If you have column for each product, that is not a normalized data structure.

    My suggestion still applies.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Col245 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    May 2019
    Posts
    5
    No I did mean date ( date of the order ) I understand why you mention normalisation and you are correct.

    However I intend normalising once the data is in the Master table in a predefined format. The Master table (badly named but was purely for this example) is cleared after the process which occurs once daily.

    By having in a predefined format it makes it easier for me to code the process to transfer the data to the appropriate tables in the next phase. Each column(field) is already uniquely identified with no intermixed datatypes and there is a unique identifier also.

    I appreciate my process may be a bit clunky (inefficient) but it makes it easier for me to follow the data path from input to proper table completion. There is no manual operation in this it has to all be automated. I will try to retrospectively make it more efficient once I have it working. This is not something I do on a regular basis so this project will help me to learn the problems and how to avoid/ correct them in the future.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Then it is not clear to me why data needs to be copied into 'master' table at all.

    How is the 'input' table accessed to begin with?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Col245 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    May 2019
    Posts
    5
    A text file output from Business Object program is copied using code from a shared folder and the table created in Access. This is the input table.

    As the text output file is highly likely to be different in its format every time eg (different fields included and excluded but always with Same naming structure, so any data in potato field will always be about potato and may be in one text file one day but not the next so the field will be there one day but not the next)

    As I did mention this may be inefficient from your perspective as I could probably use the code to just copy directly from the input table into the specific normalised tables but I wanted to keep it simple initially while I validate each phase and reduce potential errors whilst I am learning.
    Last edited by Col245; 05-25-2019 at 01:51 AM. Reason: Clarity

  8. #8
    Col245 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    May 2019
    Posts
    5
    Works like a dream Thnx

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 11-27-2018, 06:31 PM
  2. Copying table data to different database
    By m450n86 in forum Programming
    Replies: 1
    Last Post: 04-11-2017, 01:46 AM
  3. Copying tabular data into a table
    By Jennifer Murphy in forum Access
    Replies: 3
    Last Post: 02-02-2014, 05:42 PM
  4. Replies: 5
    Last Post: 01-30-2012, 01:14 PM
  5. Replies: 1
    Last Post: 06-08-2011, 02:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums