Using Excel Data to Populate an Access Table

[chrismann85]

Hi, I have an Excel Workbook that extracts tables from an Access Database using a command button. These tables are then modified (new fields and records added) in Excel.

Is there a way to feed this back into access with a command button from excel? So that when I click the button, the data from excel will be transfered back into the Access database?

 

[stopher]

There are several ways to go about this. One way is to create a link to your excel spreadsheet in Access. By doing this your excel data then becomes visible as if it were a table in Access. Then you can create an update query of make table query to copy your data from on "table" to another. You can then just get your button to run the query.
hth
Chris

 

[chrismann85]

Thanks

I have created the linked tables in Access. How do I go about writing a macro in Excel to update the tables in Access? So far I have been getting by recording them.

 

[XLEAccessGuru]

If you are familiar with Access, you could write the table population process in Access with a corresponding Access macro that will run that process. Then you could kick off the Access macro from Excel VBA behind your button.

I am better at manipulating the Excel object from Access rather than the other way around, so I'm sorry, but I don't have code to offer right away. But if you can write the population process in Access with a corresponding macro, all you'd need to do is search this forum for Excel code that will kick off an Access macro.

I'll look around if I can find some time. If not, try a search in this forum on "Access macro" or something similar.