MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Retrieve/send data to access

Posted by Ron on January 28, 2002 11:11 AM

I'd like to be able to send and retrieve data from Access tables to use in my spreadsheets. I'm familiar with VBA, but unsure of how to code in order to do this. I've placed a button on a form that the user will press to retrieve data and one for the user to save data. Any suggestions on the code behind these buttons would be appreciated. Thanks in advance!!

Posted by XL Mon on January 28, 2002 11:24 AM

Are you sure you want to actively send and receive data? Have you considered linking to your excel table from Access. That's what I do with much luck, but I don't know your particular application requirements.
To do what you describe, I believe you'll need to make an ODBC connection to your Access table and then write VBA code using DAO, ADO and SQL. That's a more complicated solution than I can give a tutorial on in this space. I recommend getting a book and a big cup of coffee if you want to dive into this;-)

Posted by Ron on January 28, 2002 12:24 PM

ODBC Connection?

Thx for the advice. I think I will need an ODBC connection since my Excel data isn't in table format.

Posted by Fernando on January 28, 2002 3:49 PM

-> That's easily done by using the "get external data / create new query to data bank" option in the data menu. Hope this helps