Does anyone know how to import and excel spreadsheet using code so that you can hit a button on a form and it does it for you. Also, I want the imported data to be added to an already existing table. How do i do this? Thanks.
DoCmd.TransferSpreadsheet acImport etc...
But, I have preferred to go the "Link" route. An upload (ie, the above) involves setting up a table in Access and Access is too sensitive to datatype mismatches from Excel. I suppose one can code to have the spreadsheet conform to the table at every upload. The plus is you preserve data integrity with the table struc you set up, whereas with the Link you cannot.
Make sure that the data being imported is of the same format as access is expecting - in many situations where data is dragged in things like dates for some stupid reason have been entered to the spreadsheet in a text format - makes life a real pain plus field names can cause the import to drop because some one has made the sheet have a record set headed (IE) startdat and in access you have corrected and placed startdate
I know its teaching people to suck eggs but in the last 2 years ive had a mountain of problems pulling data across when the initial user decides that they'll make the field name in excell easier to understand ( bang)