Import data from Excel(2007) spreadsheet into Access(2007) form using VBA

[Barry]

For many years I used Excel as a database and having recently graduated to Access, have much information that I now want to transfer from Excel to Access. My problem is that in one case the data from each spreadsheet row needs to be split into 2 Access tables, one a subdatasheet of the other. It seems to me the easiest way to do this is via an input form in Access and I have created one for this purpose. My guess is that I now need some VBA code to populate the fields in the form from the spreadsheet and to save the records relevant tables in the database. Please can someone help me with this code? Thanks

 

[Barry]

Thanks for your response. I was a little confused by the Access jargon but I got the gist of what you were trying to tell me.

In the end I created two tables in Access and made one a subdatasheet of the other. Then I divided my data in Excel into 2 Excel spreadsheets, maintaining the relationship between the data in the 2 spreadsheets by adding index columns to both spreadsheets. (Same index used in Access to link the 2 tables)

Finally I populated the 2 Access tables by uploading the data from the 2 spreadsheets using the standard Access functionality. Job done!