Exchange Access and Excel Data

There are many ways to share data between databases and spreadsheets. The most powerful way is to use ActiveX Data Objects and Visual Basic for Applications to program the exchange. Data transferred this way can be exchanged based on a schedule, it can be validated before the transfer is completed and it can ‘massaged’ by either the sending or receiving application.

I Get It! Development can create software to automate these exchanges, but what if your needs are more simple? You simply want to look at a database table or the results of a query as a spreadsheet? Microsoft has been building more and more bridges between applications for quite some time.

If you are viewing a table or query results in Access, you can click on the Tools menu and use the Office Links section. To view the data in Excel, use ’Analyze It with MS Excel’ (apparently someone in marketing thought “Export To Excel’ was too confusing…)

Are You Ready For Office 365?

Office 365 is

• Email and calendaring powered by Microsoft Exchange
• Office Web Apps - the office productivity apps you already know, tailored for the web
• Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing website
• Instant Messaging and Online Meetings powered by Microsoft Lync
• All with a financially-backed 99.9% uptime guarantee

Sign up for a free trial:

Name
Company
Email Address

Your information will not be shared with any other party. You must provide a valid email in order to recieve your login information and starting information kit.

Watch this video  to see how Office 365 can help your business.

Of course, a good old copy and paste into Excel still works, too!

Be aware that data types frequently change in this operation so you might need to change them back. See our articles on forcing data type changes using Text-To-Columns and Paste Special on the web.

To put Excel data into Access, you can also use copy and paste. If you want to add data from an Excel spreadsheet to an existing table, open the table in datasheet view and use the Paste Append option that now appears under the Edit menu. 

If you are creating a new table, you can paste directly into the tables tab and Access will create the new table using the data in the clipboard.

Access also has a versatile Import menu. You can go to File > Get External Data and use it to import data from Excel spreadsheets or named ranges. You can also use this facility to import data from other databases, Outlook folders and text files.