Forms & Reports:
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016
Now that you have created a number of Access objects, you will likely need to share your data and reports with others.
Printing is still a great way to share, but there are several other methods.
Since a database object exists only as part of the database, you must convert the object to a different format or else export it to another database.
The same methods will work for tables, query datasheets, forms, and reports, but the formats available are a bit different for each type of object.
Project 4: Forms & Reports
Formats for Exporting Access Objects
The formats that you can choose from vary, depending on the type of object you are trying to export. The most commonly used export formats are probably Text (as comma- or tab-delimited), Excel, and dBase.
The exported file may not contain everything that you had in the Access object. For example, OLE objects like photos and embedded spreadsheets are not exported except in Snapshot Reports and possibly some database formats. The formatting you have applied may not be exported. Calculated fields do not export as calculations but as values in most formats.
To verify that your object was exported correctly, you must have software that can open the file! There are many opportunities for problems here!!
Ask about the software: If you are sending the exported file to someone else, ask what software and what version of that software they will be opening the file with. It is worth some trouble to figure out the best format if you are trying to share with someone who does not have Access installed or who does not have the same version you do.
What you export may look a lot different from the original. The Snapshot Report is a special format just for reports. The exported file looks exactly like the report that you designed!
Viewer Software Required: The user must open the file with the Snapshot Viewer. The viewer shows the exported report exactly as you see it in Access, even if the viewing computer does not have Access installed. The Snapshot format is the only export format that can include OLE objects, such as photos.
You create a snapshot by Exporting the report in Snapshot Format. The exported file will have the extension .snp. So if the report in Access is named My Report, then the snapshot will be named My Report.snp.
The Snapshot Viewer is a free download from Microsoft.
The first time you create a snapshot, Access will install the viewer for you. You may be prompted for your installation CD.
Your user can print directly from the Snapshot Viewer and even choose to print just certain pages. You can also send the file by email from Snapshot Viewer's File menu, using your default email client. From the Snapshot Viewer, you do NOT have to have a MAPI client like Outlook.
Sharing Database Objects with Others
There are several ways to share your tables, queries, forms, and reports with others who do not have access - print, email as an attachment, export to a file, Analyze It with Excel, Publish It with Word, export/import into a different database, display as a web page or data access page, or ordinary copy-and-paste.
Method 1: Print
Reports are the preferred way to print data since you have more control on exactly what is printed and how. You printed objects earlier in Access Basics: Print Objects. The lesson Printable Form had more details about printing form records. The lesson Printing Reports discussed the details about printing reports.
Method 2: Email as Attachment
You can quickly send an Access object as an attachment with the Send To command. You must choose an export format for your data from 3 different Excel formats and 3 text formats (HTML, plain text, Rich Text Format). For reports, the Snapshot Format is also available.
Which Email Program: To use the Send To command from inside Access, you must have an email program that supports Messaging Application Programming Interface (MAPI), such as Outlook, Microsoft Exchange, or Microsoft Mail. These are often used by corporate networks for internal and external email. If you use another email client, like Outlook Express, or a web-based email account like HotMail or Yahoo, you cannot email directly from Access.
File Size: Many email accounts cannot accept a large attachment, often anything larger than 2 MB. If your attached file is large, consider whether you need to send the whole object. Perhaps you could restrict the number of records by filtering first. Otherwise, you may need to use a different delivery method.
Alternative for large files: Use the Export method below to create the file and then upload the file to a web server. (If you do not have access to your own web space or it is not large enough for the large file, there are a number of free file storage web sites.) Then you can email the link to the file instead of sending the whole file.
Method 3: Export as file
To save your data or a report as a separate file, you can use the| command. Your choices for a format vary, depending on the type of object you are exporting.
For a report, a message box appears and says that it is Printing!
Excel opens with a file already saved to your My Documents folder with the same name as the object that you selected. You can save again using Excel's own Save As dialog and choose a different format, if you wish.
Compare the original report at the leftbelow with the Excel version at the
right and then with a revised version below.
Publish It With Word:
The file opens in Word. It was already saved to your My Documents folder with the same name as the object that you selected. This file is in RTF format (Rich Text Format) instead of a Word format. You may save again and choose a different format from Word's own Save As dialog.
Compare the original at the left with the Word version at the right
below. Only the watermark image and some divider lines are missing.
Reports often export to Word better than to Excel, but datasheets work
better in Excel.
Method 4: Export to new (smaller) Access database
If your recipient has Access installed, you can create a special, smaller database and put into it all of the objects that are needed to show what you want them to see. The best procedure is to create a new blank database and import the objects that you need. You can import all the objects at once but you can export only one object at a time.
You can even send the database as an email attachment, if the file is small enough.
Method 5: Static web page
You can export your objects as web pages (HTML). Tables, queries, and forms are exported as datasheets. Each report page will be a separate web page with text navigation links between the pages. Any OLE objects, such as photos or embedded spreadsheets, will not be exported. You can then upload your web pages to a shared network folder or to a web server and let your users know the path to put in their browser's address bar.
The data will be fixed as of the time that you created the HTML page.
Method 6: Data Access Page
You can save database objects as data access pages, without any OLE objects such as photos or spreadsheets. For tables and forms, you can choose to allow the user to add and edit records. The data shown uses the current values from the database. Data Access Pages have some special controls that you may need to use to handle totals. See the Help article 'Calculate a total or other aggregate values' and open the directions under 'Calculate a total or other aggregate values on a data access page'. It is not easy to edit an data access page in Design View!
Method 7: Copy and Paste:
Start with: , resource files, worldtravel.mdb, reports from previous lessons
Now that you have several reports saved, you can practice the various ways besides printing that you can share them the people who cannot view the database themselves. These same methods work for other database objects.
You will not try out every variation of every method! The key is to remember to think about what software your user has. Pick a method that produces a document that they can open! You should also consider what the data will look like in the new format. Access tries to preserve the look of reports, but some formats make that easier than others.
Email As Attachment
You need three things in order to use the Sent To command to email an Access object directly from Access:
You can skip this section if you do not have such an email account set up on the computer that you are using.
Message was not sent
If you do not have an email account set up in an appropriate email program, you will not be able to send the message, but you will be able to save it. That's not much help unless you can create an email account later to send it with!
You will export a report using several different file formats to see what the differences are.
Do not confuse an exported HTML document with a data access page. Both are web pages in HTML format, but a data access page reaches into the database to use the current data every time it is opened. Exporting an object in HTML format produces a page that is frozen in time. It will not change if the data in the database changes. That may be just what you want!
Save As Data Access Page
A data access page is a web page that links directly to the database for its data. Users can be allowed to edit the data themselves. Of course, for a report, there would be no editing. But the report does reflect the most recent data changes and would change the next time it was viewed if the data had changed in the meantime! That could be quite useful.
This looks nice, until you look carefully! The Cumulative Total column is not accumulating. It is showing just the Net Price for the particular trip. The Net Price Total shows an error, #Name? Scroll to the bottom of the page and you will see that the Grand Total and Grand Total Net are both blank.
Recall that the Cumulative Total column is supposed to show the Net Price values, with Running Sum set to Yes, making the values accumulate. The Net Price for each trip is calculated with the expression =[Price]-([Price]*0.01*[Discount %]). Apparently a data access page has a problem with running sums and sums of calculated fields. There is a way around the total issue using a bound span control, which special to data access pages but not the running sum. If you are interested, see the Help article 'Calculate a total or other aggregate values' and open the directions under 'Calculate a total or other aggregate values on a data access page'.
Shortcut to Data Access Page: The Database Window's Pages section will not show a shortcut to your new data access page until after you close the page.
Inspect the Exported Files
~~ 1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~