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

 

[Pat Hartman]

You can usually do this with queries. I don't think you'll need VBA.

Import the spreadsheet.
Create a totals query that summarized the data needed to create the "parent" table. For example, if your "parent" is a Customer, select the name, address, phone and group by all the fields.
Turn this into a make-table query and run it.
Open the new table in design view and add an autonumber primary key. Give it a nice name like CustomerID.
Now's the time to examine the parent table for duplicates. You may find that you have the same customer three times because of slight typing differences in the name or address. If this is the case, we'll need to create a cross reference so we can tie all the dups together. Add a new column to the "parent" table. Run an update query to populate it with the value from the autonumber column. Add a RefID column to the imported spreadsheet. Go back to the "parent" table and change the RefID of each dup to the value of the autonumber of the record that will be the master. These RefIDs must now be transfered back to the imported spreadsheet. Create an update query that joins the two tables on all the original columns. Update the RefID in the "spreadsheet" with the RefID from the "parent" table.
Now that the detail records all have a valid foreign key value (the RefID), you can select the "child" fields plus the RefID and make the "child" table.
Open the relationships diagram and create a relationship between the two tables. Autonumber PK in the parent table to the RefID foreign key in the child table.
Exit the database and create a backup.
Open the db again and create a delete query that deletes all the parent records where the RefID is differennt from the PK. Then delete the RefID column. Rename the RefID column in the child table so that it is the same name as the PK in the parent table.

In general. Stop frequently. Exit the database and make backups. You always want a fall back position when you are messing around with data like this.

If you create a join of the parent to the child, you should be able to reproduce your spreadsheet except that the data anomolies will be cleaned up.

 

[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!