Access Import data to field related to another table. RRS feed

  • Question

  • I am converting the software used by a small business from Excel to Access.  This involves considerable restructuring of the data.  An Excel workbook held details of all the items in which the business deals.  In Access I have a table that contains all the names of Manufacturers, and another table into which item description, weight, condition, etc are detailed with one field showing the Manufacturer using the lookup wizard to relate it to the PK (autonumber) of the Manufacturer table.

    To deal with the initial import of data I have created in Excel a worksheet that contains all the data I need in the same format as the Access table, but when I carry out the import it rejects the Manufacturer field as being the wrong type.  I.e. it is text trying to go into to a number field.  Research on various forums reveals that the only way around this is to import it into to a temporary Access table with the Manufacturer field set as text, and then to transfer the data by copying from the temporary table to the target table.  This works fine when I manually select the whole of the temporary table (which has the Manufacturer as text), copy it all and then paste it into the target table (which has the lookup wizard relationship to the Manufacturers table in the Manufacturer field).

    I had hoped that in VBA I could use SQL INSERT to replicate my manual copy / paste transfer of the data, but it rejects the Manufacturer field.  I cannot find any other way to replicate in Access VBA the copying of the whole temporary table and pasting it into the blank target table.  I would be grateful for any guidance on how to replicate the copy / paste in VBA, or any ideas on how to persuade the INSERT command to use the lookup wizard relationship.

    Friday, August 23, 2019 11:41 AM

All replies

  • I'd import the Excel table "as is". The imported table will contain the manufacturer as a text field.

    Open the table in design view and add a number (long integer) field ManufacturerID. Save the table.

    On the Create tab of the ribbon, click Query Design.

    Add the imported table and the Manufacturers table, then close the Show Table dialog.

    Link the tables on the manufacturer name fields (the text fields).

    In the Query Type group of the Design tab of the ribbon, click to highlight Update.

    Add the ManufacturerID field of the imported table to the query grid.

    In the 'Update to' row of this column, enter [Manufacturers].[Name of Primary Key field] using the actual names.

    Click the Run button and confirm that you want to update records.

    Now inspect the imported table to check that the ManufacturerID field has been populated correctly. There may be blanks, for example because of misspelled names. Correct these if necessary.

    When everything is OK, you can delete the text field from the imported table and keep the number field.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, August 23, 2019 12:37 PM
  • Hans

    Thanks as always for looking at this.  It looks like an elegant solution, but I am trying to do this in VBA.  Is it possible to do all those steps in VBA?  Otherwise I might as well stick to my simple cut and paste, which works when I do it manually.  I want to automate the whole transition process from Excel to Access, as there are several other worksheet to tables transitions.  I have managed the whole process down to a single button click and a couple of input boxes, the only frustrating exception being the one described above. 

    Andy C

    Friday, August 23, 2019 1:08 PM
  • Unless you have to do this hundreds or thousands of times, I think writing, testing and debugging the code would take longer than performing the steps manually...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, August 23, 2019 1:50 PM
  • Hans

    Thanks for your comment.  I shall follow your advice;  it's usually pretty good.

    Andy C

    Friday, August 23, 2019 2:58 PM
  • For the benefit of anyone who has a similar query, further research has revealed that there is a way to achieve what I need, replicating in VBA the copy / paste manual operation.  However, it is a very complex and (with a large amount of data) lengthy procedure, and the suggestion that it may be simpler just to do it manually is very valid.

    I found at https://www.pcreview.co.uk/threads/copy-all-contents-of-a-recordset-to-clipboard.2772619/ a set of procedures by Terry Kredt that appear to use the Windows memory management at kernel32.dll to load up the clipboard.  Testing it shows that it works with only minor changes to adapt it to individual needs.

    For the Pasting part of the exercise, there is a similar set of procedures at https://social.msdn.microsoft.com/Forums/office/en-US/5589f76f-bd77-47d4-bd57-85d8ff55e53e/docmdruncommand-accmdpasteappend-into-tbltemp?forum=accessdev.  However, I used a much shorter (in code) procedure that was part of the question at the same URL, for the simple reason that I understood how it worked.  However, it again takes a long time to run.

    I hope this may be of some help if you have the same problem as I did.

    Andy C
    Thursday, August 29, 2019 5:12 PM
  • but I am trying to do this in VBA.  Is it possible to do all those steps in VBA?

    Hi Andy,

    I do quite a lot of imports with VBA, so yes it is possible. Each input file with a different layout however needs its own code.

    The recipe is simple: Open the file (.txt, .csv, even .html) for input, and read it line by line. Depending on the typicaiities of the line (header, body, …) you can skip the line or process it in one way or the other.

    Each line that will be processed is Split in an array using the appropriate cell-divider (mostly ";" or "," or TAB). The simplest next step is to loop through the array and through the fields of a new record and assign the array value to the field, or construct an INSERT command to execute it.

    In more complex cases you can do data conversion of the cell, write to field of a related record, perform all kind of consistency checks, etc., etc.

    In this way I transfer downloaded banking data to Access tables, process registration forms from emails, grab all kind of information from internet, a.o. actual exchange information, and many other functions.

    The basis is the simple line-by-line/cell-by-cell approach, where you have control over each individual cell.

    And of course: the more data you want to transfer, and the more complex, the longer it takes.


    Thursday, August 29, 2019 9:48 PM
  • You have 2 choices

    Either "format" the Excel's data to present as to what Access would expect ...for example if you import an Excel sheet and one in the columns the data are numeric and later on it becomes text...Access get confused..unless explicity stated


    Use VBA to open Excel as oject and iterate all cells and populate accordingly

    Friday, August 30, 2019 9:18 AM
  • You might like to take a look at DecomposerDemo.zip in my public databases folder at:


    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates how non-normalized data from Excel or similar can be imported into a temporary 'master' table in Access and then decomposed into a set of predefined correctly normalized related tables by executing a series of INSERT INTO statements in a specific order.  A brief description of each step is given as you work through the demo.  The basic rule of thumb is that rows are inserted into each referenced table in a relationship type before inserting rows into a referenced table.

    Ken Sheridan, Stafford, England

    Friday, August 30, 2019 11:18 AM
  • Many thanks to Imb-hb, TsGiannis andKen Sheridan for their answers above.  Each is helpful in understanding the whole process of getting data from Excel to Access.  However, I emphasise that i have got data into Access without difficulty, in the format that I want.  My problem lay in the fact that I could not get into the destination table one text field that was related (through a primary key) to another table.  It was rejected when using INSERT, which is why I chose to use Copy and Paste.  Now that I have found a method in VBA of using Copy and paste (albeit not very efficiently) I am happy to leave the process alone and move on to the next part of building the whole program.

    With many thanks to all who answered,

    Andy C

    Friday, August 30, 2019 2:37 PM
  • My problem lay in the fact that I could not get into the destination table one text field that was related (through a primary key) to another table. 

    Hi Andy,

    In a cell-by-cell approach you can set a Stop when the Manufacturer cell is being processed. Then you can with the debugger step by step see what happens and where it goes wrong.

    Probably you need to convert the Manufacturer's name (in the Excel cell) convert to the Manufacturer_ID by referemce the Manufacturer_tbl, and use that that value in the Access table as FK.


    Friday, August 30, 2019 4:19 PM
  • Probably you missed something when you Where performing the INSERT... recently I had a similar issue because I had data with single quote.....Replacing with dual resolved nicely
    Friday, September 6, 2019 6:23 PM